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”.

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

    -- 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)
         ,CO2E                        FLOAT
         ,CO2E_UNIT                   VARCHAR(2000)
         ,GEOJSON_VISUALIZATION       VARCHAR(65000)
         ,ERROR                       VARCHAR(2000)
         ,"ERROR_MESSAGE"             VARCHAR(2000)
         ,_CELONIS_CHANGE_DATE        DATE
    );
    
    -- 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)
         ,TRANSPORT_MODE_MAIN_LEG          VARCHAR(2000)
         ,UNIQUE_STAGE                     VARCHAR(2000)
         ,DEPARTURE_POINT                  VARCHAR(2000)
         ,DEPARTURE_POINT_LATITUDE         FLOAT
         ,DEPARTURE_POINT_LONGITUDE        FLOAT
         ,DEPARTURE_GEOCODING_CONFIDENCE   FLOAT
         -- ,DEPARTURE_FIELD_SCORE_CITY       FLOAT
         -- ,DEPARTURE_FIELD_SCORE_COUNTRY    FLOAT
         ,ARRIVAL_POINT                    VARCHAR(2000)
         ,ARRIVAL_POINT_LATITUDE           FLOAT
         ,ARRIVAL_POINT_LONGITUDE          FLOAT
         ,ARRIVAL_GEOCODING_CONFIDENCE     FLOAT
         -- ,ARRIVAL_FIELD_SCORE_CITY         FLOAT
         -- ,ARRIVAL_FIELD_SCORE_COUNTRY      FLOAT
         ,TRANSPORT_MODE                   VARCHAR(2000)
         ,DISTANCE                         FLOAT
         ,CO2E                             FLOAT
         ,CO2E_UNIT                        VARCHAR(2000)
         ,ERROR                            VARCHAR(2000)
         ,"ERROR_MESSAGE"                  VARCHAR(2000)
         ,_CELONIS_CHANGE_DATE             DATE
    );
    
    -- 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)
         ,SECTOR                     VARCHAR(2000)
         ,CATEGORY                   VARCHAR(2000)
         ,ID                         VARCHAR(2000)
         ,UUID                       VARCHAR(2000)
         ,UNIT                       VARCHAR(2000)
         ,UNCERTAINTY                VARCHAR(2000)
         ,SOURCE                     VARCHAR(2000)
         ,"YEAR"                     VARCHAR(2000)
         ,REGION                     VARCHAR(2000)
         ,PARAMETER_VALUE            FLOAT
         ,LCA_ACTIVITY               VARCHAR(2000)
         ,DATA_QUALITY_FLAGS         VARCHAR(2000)
         ,ACCESS_TYPE                VARCHAR(2000)
         ,CALCULATION_ORIGIN         VARCHAR(2000)
         ,CALCULATION_METHOD         VARCHAR(2000)
         ,CO2E_FROM_SOURCE           FLOAT
         ,CO2E_OTHER                 FLOAT
         ,CO2                        FLOAT
         ,CH4                        FLOAT
         ,N2O                        FLOAT
         ,CO2E                       FLOAT
         ,ERROR                      VARCHAR(2000)
         ,"ERROR_MESSAGE"            VARCHAR(2000)
         ,_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_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"
              ,"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