Celonis Product Documentation

Create a new transformation with the title “Create Table: Carbon Engine Emissions Data” to store the output data of the Carbon Engine:

-- Create a table for high-level emission data
-- Query 1
DROP TABLE IF EXISTS "TRANSPORT_EMISSIONS_DATA" cascade;




-- Query 2

CREATE TABLE "TRANSPORT_EMISSIONS_DATA" (

    UNIQUE_STAGE_IDENTIFIER
      VARCHAR(2000) PRIMARY KEY
    ,UNIQUE_STAGE                VARCHAR(2000)
    ,TRANSPORT_MODE_MAIN_LEG     VARCHAR(2000)
    ,SHIPPING_TYPE               VARCHAR(2000)

    ,CO2E                        FLOAT                    -- overall CO2E emissions of shipping stage
    ,CO2E_UNIT                   VARCHAR(2000)            -- unit of overall CO2E emissions of shipping stage
    ,GEOJSON_VISUALIZATION       VARCHAR(65000)           -- (optional feature, which needs to be requested separately) JSON which can be used to visualize route identified by the carbon engine with https://geojson.io/

    ,DISTANCE_KM                 VARCHAR(2000)            -- distance of complete shipping route in km

    ,NOTICES                     VARCHAR(2000)            -- additional notes

    ,ERROR                       VARCHAR(2000)            -- indicator if an error occurred during the calculation

    ,"ERROR_MESSAGE"             VARCHAR(2000)            -- detailed description of the calculation error

    ,_CELONIS_CHANGE_DATE        DATE
)
;

-- Create a table for emission data per transport section
-- Query 1
DROP TABLE IF EXISTS "TRANSPORT_SECTIONS_EMISSIONS_DATA" cascade;
 
-- Query 2
CREATE TABLE "TRANSPORT_SECTIONS_EMISSIONS_DATA" (
    UNIQUE_STAGE_IDENTIFIER            VARCHAR(2000)
    ,LEG_IDENTIFIER                    VARCHAR(2000)
    ,UNIQUE_STAGE                      VARCHAR(2000)
    ,SHIPPING_TYPE_MAIN_LEG            VARCHAR(2000)
    ,TRANSPORT_MODE_MAIN_LEG           VARCHAR(2000)
    ,DEPARTURE_LOCATION_NAME           VARCHAR(2000)        -- departure point of transport section
    ,DEPARTURE_LATITUDE                FLOAT                -- departure point latitude of transport section
    ,DEPARTURE_LONGITUDE               FLOAT                -- departure point longitude of transport section
    ,DEPARTURE_CONFIDENCE_SCORE        FLOAT                -- confidence score of geocoder for departure address
    ,ARRIVAL_LOCATION_NAME             VARCHAR(2000)        -- arrival point of transport section
    ,ARRIVAL_LATITUDE                  FLOAT                -- arrival point latitude of transport section
    ,ARRIVAL_LONGITUDE                 FLOAT                -- arrival point longitude of transport section
    ,ARRIVAL_CONFIDENCE_SCORE          FLOAT                -- confidence score of geocoder for arrival address
    ,TRANSPORT_MODE                    VARCHAR(2000)        -- transport mode of transport section
    ,DISTANCE_KM                       FLOAT                -- distance of transport section
    ,CO2E                              FLOAT                -- CO2E emissions of transport section
    ,CO2E_UNIT                         VARCHAR(2000)        -- unit for CO2E emissions of transport section
    ,ERROR                             VARCHAR(2000)        -- indicator if an error occurred during the calculation
    ,"ERROR_MESSAGE"                   VARCHAR(2000)        -- detailed description of the calculation error
    ,_CELONIS_CHANGE_DATE              DATE
);

-- Create a table for detailed information about the emissiono factors used by the carbon engine
-- Query 1
DROP TABLE IF EXISTS "TRANSPORT_EMISSION_FACTORS" cascade;
 
-- Query 2
CREATE TABLE "TRANSPORT_EMISSION_FACTORS" (
    UNIQUE_STAGE_IDENTIFIER            VARCHAR(2000)
    ,LEG_IDENTIFIER                    VARCHAR(2000)
    ,FACTOR_IDENTIFIER                 VARCHAR(2000)
    ,TRANSPORT_MODE_MAIN_LEG           VARCHAR(2000)
    ,UNIQUE_STAGE                      VARCHAR(2000)
    ,SHIPPING_TYPE_MAIN_LEG            VARCHAR(2000)        
    ,CO2E_CALCULATION_METHOD           VARCHAR(2000)        -- calculation method of the emission factor
    ,CO2E_CALCULATION_ORIGIN           VARCHAR(2000)        -- calculation origin of the emission factor
    ,NAME                              VARCHAR(2000)
    ,ACTIVITY_ID                       VARCHAR(2000)        -- used activity id
    ,CATEGORY                          VARCHAR(2000)        -- emission factor category
    ,LCA_ACTIVITY                      VARCHAR(2000)        -- used lca activity
    ,ID                                VARCHAR(2000)        -- activity_id of the selected emission factor
    ,UUID                              VARCHAR(2000)        -- uniqe identifier of the selected emission factor
    ,SOURCE                            VARCHAR(2000)        -- source database of the emission factor
    ,"YEAR"                            VARCHAR(2000)        -- year of the emission factor
    ,REGION                            VARCHAR(2000)        -- region to which the emission factor is assigned
    ,DATA_QUALITY_FLAGS                VARCHAR(2000)        -- data quality flags regarding the emission factor
    ,CO2E_TOTAL                        VARCHAR(2000)        -- constituent gas: co2e_total
    ,CH4                               VARCHAR(2000)        -- constituent gas: ch4
    ,CO2                               VARCHAR(2000)        -- constituent gas: co2
    ,N2O                               VARCHAR(2000)        -- constituent gas: n20
    ,CO2E_OTHER                        VARCHAR(2000)        -- constituent gas: co2e other
    ,ACCESS_TYPE                       VARCHAR(2000)        -- access type of the source database
    ,ERROR                             VARCHAR(2000)        -- indicator if an error occurred during the calculation
    ,"ERROR_MESSAGE"                   VARCHAR(2000)        -- detailed description of the calculation error
    ,_CELONIS_CHANGE_DATE              DATE
);

Create a new transformation with the title “Create Datamodel Views”:

DROP VIEW IF EXISTS O2C_VTSP;
CREATE VIEW "O2C_VTSP" AS (
            SELECT *
            FROM <YOUR_SAP_ECC_DATASOURCE>."O2C_VTSP"
        );


DROP VIEW IF EXISTS O2C_SHIPMENT_NETWORK;
CREATE VIEW "O2C_SHIPMENT_NETWORK" AS (
            SELECT *
            FROM <YOUR_SAP_ECC_DATASOURCE>."SHIPMENT_NETWORK"
        );


DROP VIEW IF EXISTS O2C_UNIQUE_SHIPPING_STAGES;
CREATE VIEW "O2C_UNIQUE_SHIPPING_STAGES" AS (
            SELECT *
            FROM <YOUR_SAP_ECC_DATASOURCE>."UNIQUE_SHIPPING_STAGES"
        );


DROP VIEW IF EXISTS O2C_UNIQUE_SHIPPING_ROUTES;
CREATE VIEW "O2C_UNIQUE_SHIPPING_ROUTES" AS (
            SELECT
                "UNIQUE_STAGE"
                ,"DEPARTURE_COUNTRY"
                ,"DEPARTURE_CITY"
                ,"ARRIVAL_COUNTRY"
                ,"ARRIVAL_CITY"
            FROM (
                SELECT DISTINCT
                    "UNIQUE_STAGE"
                    ,"DEPARTURE_COUNTRY"
                    ,"DEPARTURE_CITY"
                    ,"ARRIVAL_COUNTRY"
                    ,"ARRIVAL_CITY"
                    ,ROW_NUMBER() OVER (PARTITION BY "UNIQUE_STAGE") AS ROW_NUM
                FROM
                    <YOUR_SAP_ECC_DATASOURCE>."UNIQUE_SHIPPING_STAGES"
            ) AS "UNIQUE_SHIPPING_STAGES"
            WHERE ROW_NUM = 1
        );


DROP VIEW IF EXISTS O2C_TRANSPORT_EMISSIONS_DATA;
CREATE VIEW "O2C_TRANSPORT_EMISSIONS_DATA" AS (
            SELECT *
            FROM "TRANSPORT_EMISSIONS_DATA"
        );


DROP VIEW IF EXISTS O2C_TRANSPORT_SECTIONS_EMISSIONS_DATA;
CREATE VIEW "O2C_TRANSPORT_SECTIONS_EMISSIONS_DATA" AS (
            SELECT *
            FROM "TRANSPORT_SECTIONS_EMISSIONS_DATA"
        );


DROP VIEW IF EXISTS O2C_TRANSPORT_EMISSION_FACTORS;
CREATE VIEW "O2C_TRANSPORT_EMISSION_FACTORS" AS (
            SELECT *
            FROM "TRANSPORT_EMISSION_FACTORS"
        );