Skip to main content

Celonis Product Documentation

Data Job
Adjust and run global data job

In this step, create a new global data job called “Create Emission Tables and Views”. This is where the tables are created in which the output data of the Carbon Engine is stored. In addition, the views are created, which are used in the app's data model.

image53.png

Click on the new “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" 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
    );
    

Note

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.

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

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.

After performing these steps, there should be a global data job called “Create Emission Tables and Views” that contains two transformations as shown in the following screenshot:

image52.png