Extract, Transform, and Load for Material Emissions app
Before you start working with the Material Emissions app, you must prepare your environment first.
Make the following changes to your existing transformation to:
Add the field LAND1 (from the LFA1 table) to the EKPO table.
To add two columns with conversion factors from EKPO.GEWEI into kgs and EKPO.MEINS into kgs, for every PO, to standardize the weights for the app.
Important
The following changes to queries have been tested to work with standard installations. If you use any custom queries you might need to make additional changes.
In the data job SAP P2P - Daily Update, change the transformation Create Temp Table: EKKO EKPO to:
DROP TABLE IF EXISTS TMP_P2P_EKKO_EKPO; CREATE TABLE TMP_P2P_EKKO_EKPO AS( SELECT EKKO.MANDT ,EKKO.EBELN ,EKPO.EBELP ,CAST(EKKO.AEDAT AS DATE) AS "AEDAT" ,EKKO.ERNAM ,EKKO.EKORG ,EKPO.KONNR ,EKPO.KTPNR ,EKKO.BSTYP ,EKPO.BANFN ,EKPO.BNFPO ,EKKO.WAERS ,EKKO.LIFNR ,LFA1.LAND1 --For Country ,EKKO.BUKRS ,EKPO.NETWR ,EKPO.NETPR ,EKKO.WKURS ,EKPO.MATNR ,EKPO.WERKS ,EKKO.MANDT || EKKO.EBELN AS "TABKEY_EKKO" ,EKKO.MANDT || EKKO.EBELN || EKPO.EBELP AS "_CASE_KEY" ,EKPO.MANDT || EKPO.BANFN || EKPO.BNFPO AS "TABKEY_EBAN" ,CAST(EKPO.AEDAT AS DATE) AS "EKPO_AEDAT" FROM EKKO AS EKKO JOIN EKPO AS EKPO ON EKKO.MANDT = EKPO.MANDT AND EKKO.EBELN = EKPO.EBELN LEFT JOIN LFA1 AS LFA1 ON --For Country EKKO.MANDT = "LFA1"."MANDT" AND EKKO.LIFNR = "LFA1"."LIFNR" WHERE EKKO.BSTYP = '<%=purchaseDoc%>' ); -- ALTER TABLE EKPO ADD COLUMN FACTOR_TO_CONVERT_FROM_GEWEI_TO_KG FLOAT; UPDATE EKPO SET FACTOR_TO_CONVERT_FROM_GEWEI_TO_KG = (CASE WHEN GEWEI = 'KG' THEN 1 WHEN GEWEI= 'G' THEN 0.001 WHEN GEWEI= 'MG' THEN 0.000001 WHEN GEWEI= 'LB' THEN 0.45359237 WHEN GEWEI= 'TO' THEN 1000 WHEN GEWEI= 'TON' THEN 1016.04691 WHEN GEWEI= 'OZ' THEN 0.02834952 END); -- ALTER TABLE EKPO ADD COLUMN FACTOR_TO_CONVERT_FROM_MEINS_TO_KG FLOAT; UPDATE EKPO SET FACTOR_TO_CONVERT_FROM_MEINS_TO_KG = (CASE WHEN MEINS = 'KG' THEN 1 WHEN MEINS= 'G' THEN 0.001 WHEN MEINS= 'MG' THEN 0.000001 WHEN MEINS= 'LB' THEN 0.45359237 WHEN MEINS= 'TO' THEN 1000 WHEN MEINS= 'TON' THEN 1016.04691 WHEN MEINS= 'OZ' THEN 0.02834952 END); SELECT ANALYZE_STATISTICS ('TMP_P2P_EKKO_EKPO');
In the data job SAP P2P - Full transformations, change the transformation Create Temp Table: EKKO EKPO using the same method as suggested in the previous bullet point.
In the data job SAP P2P - Full transformations, in transformation Create Table : P2P_EKPO_STAGING, add the columns
E.LAND1,
to the list of fields in theSELECT
query.In the data job SAP P2P - Full transformations, in transformation Create View: P2P_EKPO, add the columns
P2P_EKPO_STAGING.LAND1,
to the list of fields in theSELECT
query.
Tip
You need to uncomment and execute the two lines with ALTER TABLE EKPO ADD COLUMN
just once. After one execution, comment out the two lines again.
To feed the Knowledge Model and the views you must create the Material Emissions data job with a new transformation: Create UNIQUE_MATERIALS_AND_COUNTRIES table.
-- This query should only run one time, when you are installing the app. -- Create initial table with unique materials and countries where they are acquired from DROP TABLE IF EXISTS UNIQUE_MATERIALS_AND_COUNTRIES CASCADE; CREATE TABLE "UNIQUE_MATERIALS_AND_COUNTRIES" ( "MATERIAL_DESCRIPTION" VARCHAR(128) ,"MATERIAL_GROUP" VARCHAR(128) ,"COUNTRY" VARCHAR(8) -- ,"ACTIVITY_ID_PER_WEIGHT" VARCHAR(256) ,"ID_PER_WEIGHT" VARCHAR(128) ,"EMISSIONS_PER_WEIGHT" FLOAT ,"CONFIDENCE_PER_WEIGHT" VARCHAR(16) ,"SOURCE_OF_EMISSIONS_PER_WEIGHT" VARCHAR(128) ,"DATA_QUALITY_FLAG_PER_WEIGHT" VARCHAR(128) ,"SOURCE_LCA_ACTIVITY_PER_WEIGHT" VARCHAR(128) ,"REVIEW_STATE_PER_WEIGHT" VARCHAR(128) ,"LAST_HUMAN_REVIEW_AT_PER_WEIGHT" VARCHAR(128) -- ,"ACTIVITY_ID_PER_SPEND" VARCHAR(256) ,"ID_PER_SPEND" VARCHAR(128) ,"EMISSIONS_PER_SPEND" FLOAT ,"CONFIDENCE_PER_SPEND" VARCHAR(16) ,"SOURCE_OF_EMISSIONS_PER_SPEND" VARCHAR(128) ,"DATA_QUALITY_FLAG_PER_SPEND" VARCHAR(128) ,"SOURCE_LCA_ACTIVITY_PER_SPEND" VARCHAR(128) ,"REVIEW_STATE_PER_SPEND" VARCHAR(128) ,"LAST_HUMAN_REVIEW_AT_PER_SPEND" VARCHAR(128) -- ,"IN_HOUSE_EMISSIONS" FLOAT ,"SUPPLIER_SPECIFIC_EMISSIONS" FLOAT ,"UNIQUE_KEY" VARCHAR(128) PRIMARY KEY ,"_CELONIS_CHANGE_DATE" DATE ); -- Insert initial (material + countries) from In house file INSERT INTO UNIQUE_MATERIALS_AND_COUNTRIES SELECT DISTINCT "sheet"."MATERIAL_GROUP_DESCRIPTION" ,"sheet"."MATERIAL_GROUP" ,"sheet"."COUNTRY" ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,"sheet"."IN_HOUSE_EMISSIONS" ,"sheet"."SUPPLIER_SPECIFIC_EMISSIONS" ,CONCAT("sheet"."MATERIAL_GROUP","sheet"."COUNTRY") ,NULL FROM "In_house_material_emissions_Sheet1" AS "sheet" ; -- Insert initial (materials + countries) from POs INSERT INTO UNIQUE_MATERIALS_AND_COUNTRIES SELECT DISTINCT "EKPO"."MATKL_TEXT" ,"EKPO"."MATKL" ,"EKPO"."LAND1" ,NULL --insert new rows with no activity id ,NULL --insert new rows with no id ,NULL --insert new rows with no emissions per weight ,NULL --insert new rows with no confidence ,NULL --insert new rows with no source ,NULL --insert new rows with no data quality flag ,NULL --insert new rows with no source lca activity ,NULL --insert new rows with no review state ,NULL --insert new rows with no review date ,NULL --insert new rows with no activity id ,NULL --insert new rows with no id ,NULL --insert new rows with no emissions per spend ,NULL --insert new rows with no confidence ,NULL --insert new rows with no source ,NULL --insert new rows with no data quality flag ,NULL --insert new rows with no source lca activity ,NULL --insert new rows with no review state ,NULL --insert new rows with no review date ,NULL --insert new rows with no in house emissions ,NULL --insert new rows with no supplier emissions , CONCAT("EKPO"."MATKL","EKPO"."LAND1") ,NULL FROM "P2P_EKPO" AS "EKPO" WHERE "EKPO"."MATKL_TEXT" != '' AND "EKPO"."LAND1" != '' AND CONCAT("EKPO"."MATKL","EKPO"."LAND1") NOT IN (SELECT "UNIQUE_KEY" FROM UNIQUE_MATERIALS_AND_COUNTRIES) --just insert the rows that dont yet exist in UNIQUE_MATERIALS_AND_COUNTRIES ; select * from UNIQUE_MATERIALS_AND_COUNTRIES;
Important
Before this step, make sure to run all transformations.
Add the UNIQUE_MATERIALS_AND_COUNTRIES table to the Data Model.
Join the UNIQUE_MATERIALS_AND_COUNTRIES table with the UNIQUE_MATERIALS_AND_COUNTRIES 1:N EKPO by LAND1 and MATKL.
Reload the Data Model - either Full load or Partial load with UNIQUE_MATERIALS_AND_COUNTRIES and EKPO tables.
Tip
If the current Data Model doesn't have the tables tcurf, tcurx, tcurr, you need to add these as well and you can leave them as “loose” tables without any link to other tables.
The script gets the emission factors for your materials and then reloads the table you just created with the new data.
Before you begin:
The script is not part of the app package. To get the script, reach out to us directly.
Make sure your Machine Learning Workbench has permission to interact with “Data Integration”. In your Celonis Platform instance, go to Admin & Settings > Permissions. In the Services section of the Permissions screen, click Edit. Check that the key you used for your Machine Learning Workbench has permission to access the data pools and models.
To run the script:
Define the following script parameters to connect to your Data Model and for related API queries:
data_pool_id
- the id of your data pooldata_model_name
- the name of your data modelspend_unit_var
- choose the unit of your spend-based EFsweight_unit_var
- choose the unit of your weight-based EFsregion_fallback
- define if, for low-confidence matches, you accept a fallback recommendation from a broader regionpo_value_min_threshold
- define the value below which we are not interested in getting a recommendation from the APIgranularity_var
- define the granularity of your mapping (usually MATERIAL_GROUP or MATERIAL_NUMBER)climatiq_key
- the API key for the customer to access the Climatiq API. (Sustainability GTM responsible for providing this key)
Set a scheduler in the Machine Learning Workbench to run the script weekly at a certain hour, ideally after the execution of the transformations.