Skip to main content

Celonis Product Documentation

Extract, Transform, and Load for Material Emissions app

Before you start working with the Material Emissions app, you must prepare your environment first.

1.Change standard P2P transformations

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.

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.

  • 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 the SELECT 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 the SELECT query.

2. Create new transformation

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;
3. Make changes to the Data Model

Important

Before this step, make sure to run all transformations.

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.

  1. Add the UNIQUE_MATERIALS_AND_COUNTRIES table to the Data Model.

  2. Join the UNIQUE_MATERIALS_AND_COUNTRIES table with the UNIQUE_MATERIALS_AND_COUNTRIES  1:N EKPO by LAND1 and MATKL.

    MaterialEmissions.png

  3. Reload the Data Model - either Full load or Partial load with UNIQUE_MATERIALS_AND_COUNTRIES and EKPO tables.

4. Run a script for Machine Learning Workbench

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, please 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:

  1. Define the following script parameters to connect to your Data Model and for related API queries:

    • data_pool_id - the id of your data pool

    • data_model_name - the name of your data model

    • spend_unit_var - choose the unit of your spend-based EFs

    • weight_unit_var - choose the unit of your weight-based EFs

    • region_fallback - define if, for low-confidence matches, you accept a fallback recommendation from a broader region

    • po_value_min_threshold - define the value below which we are not interested in getting a recommendation from the API

    • granularity_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)

  2. Set a scheduler in the Machine Learning Workbench to run the script weekly at a certain hour, ideally after the execution of the transformations.