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.

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

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.

  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.

    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:

  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.