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";

-- 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)           -- 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(65000)            -- 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"; 

-- 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 emission factors used by the carbon engine 
-- Query 1 
DROP TABLE IF EXISTS "TRANSPORT_EMISSION_FACTORS"; 

-- 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)        -- unique 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”:

-- In the latest marketplace version of the connector LIKP should be part of the main views 
   -- DROP VIEW IF EXISTS O2C_LIKP; 
    -- CREATE VIEW "O2C_LIKP" AS ( 
    --             SELECT *  
    --             FROM <%=YOUR_SAP_ECC_DATASOURCE%>."O2C_LIKP" 
    --         ); 
    DROP VIEW IF EXISTS O2C_SHIPPING_TYPE_METADATA; 
    CREATE VIEW "O2C_SHIPPING_TYPE_METADATA" AS ( 
           SELECT DISTINCT 
               "VSART" AS "SHIPPING_TYPE", 
               "BEZEI" AS "SHIPPING_TYPE_DESCRIPTION" 
           FROM <%=YOUR_SAP_ECC_DATASOURCE%>."SHIPPING_TYPES" AS "SHIPPING_TYPES" 
    #xA0;      ); 
    DROP VIEW IF EXISTS O2C_VTTP; 
    CREATE VIEW "O2C_VTTP" AS ( 
               SELECT *  
               FROM <%=YOUR_SAP_ECC_DATASOURCE%>."O2C_VTTP" 
           ); 
    DROP VIEW IF EXISTS O2C_VTTK; 
    CREATE VIEW "O2C_VTTK" AS ( 
               SELECT *  
               FROM <%=YOUR_SAP_ECC_DATASOURCE%>."O2C_VTTK" 
           ); 
    DROP VIEW IF EXISTS O2C_VTTS; 
    CREATE VIEW "O2C_VTTS" AS ( 
               SELECT *  
               FROM <%=YOUR_SAP_ECC_DATASOURCE%>."O2C_VTTS" 
           ); 
    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" 
                   ,"DEPARTURE_QUERY" 
                   ,"ARRIVAL_COUNTRY" 
                   ,"ARRIVAL_CITY" 
                   ,"ARRIVAL_QUERY" 
               FROM ( 
                   SELECT DISTINCT 
                       "UNIQUE_STAGE" 
                       ,"DEPARTURE_COUNTRY" 
                      ,"DEPARTURE_CITY" 
                      ,"DEPARTURE_QUERY" 
                       ,"ARRIVAL_COUNTRY" 
                       ,"ARRIVAL_CITY" 
                       ,"ARRIVAL_QUERY" 
                       ,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" 
           );