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.
![]() |
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.
![]() |
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:
![]() |