Data Job
Adjust and run global data job
In this step, create a new global data job called “Create Emission Tables and Views”.
![]() |
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.
![]() |
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:
![]() |