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