Skip to main content

Celonis Product Documentation

Transformations - Global Data Job

The next step is to create a new global data job called “Create Emission Tables and Views”. 

Click on the newly created “Create Emission Tables and Views” data job in the Global Jobs section and perform the following steps:

  • 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 
    ); 
    

Notice

This table is used to store the emissions calculated by the carbon engine. Make sure that this transformation is only be executed at the initial set-up of the app.

disable_transport_emission_table_transformations.png

Warning

It is important that the transformation "Create Table: Carbon Engine Emissions Data" is disabled after the initial set-up. Otherwise, the emission tables would be deleted and recreated each time the data job is executed. The emission would have to be recalculated for all routes over and over again.

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

Note

Make sure to replace <YOUR_SAP_ECC_DATASOURCE> with the data source from which your SAP ECC data was extracted.

To ensure that all necessary tables are available in the data model, execute this data job.