Skip to main content

Celonis Product Documentation

Migration Guide
Migrate the shipping emissions reduction app from v2.X to v3.X

In this guide, you will learn how to migrate your shipping emissions app based on v 2.X to v 3.X. In version 3.X, we decided to change the emissions calculation by our carbon engine from a user-hosted approach to a centrally hosted approach. This change will significantly simplify the implementation of the app, provide more flexibility and improve usability. Unfortunately, this was only possible through drastic changes.

Migration instructions

(estimated effort: 1 h)

  1. Update your shipping emissions app to the latest version

    • Go to the Settings page in the app package.

    • Click on Dependencies.

    • Update the app.

  2. Adapt your data transformations to align with the newest app version

    • Go to the data pool used for the app

    • Update the shipping type metadata tables

      Why?

      With the new setup, the shipping type configuration will be managed within the app. So the previous shipping type metadata file is not needed anymore.

      How?

      Important

      A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.

      In the Create Table: Shipment Network (Signal Link) data transformation update the SHIPPING_TYPES table to the following version:

      DROP TABLE IF EXISTS SHIPPING_TYPES;
      CREATE TABLE SHIPPING_TYPES AS
      SELECT DISTINCT
      "VTTS"."MANDT"
      ,"VTTS"."TKNUM"
      ,"VTTS"."TSNUM"
      ,COALESCE("VTTS"."VSART","VTTK"."VSART") AS "VSART"
      ,COALESCE("VTTS"."BEZEI","VTTK"."BEZEI") AS "BEZEI"
      FROM "O2C_VTTS" AS "VTTS"
      LEFT JOIN "O2C_VTSP" AS "VTSP" ON 1=1
      AND "VTTS"."MANDT" = "VTSP"."MANDT"
      AND "VTTS"."TKNUM" = "VTSP"."TKNUM"
      AND "VTTS"."TSNUM" = "VTSP"."TSNUM"
      LEFT JOIN "O2C_VTTP" AS "VTTP" ON 1=1
      AND "VTSP"."MANDT" = "VTTP"."MANDT"
      AND "VTSP"."TKNUM" = "VTTP"."TKNUM"
      AND "VTSP"."TPNUM" = "VTTP"."TPNUM"
      LEFT JOIN "O2C_VTTK" AS "VTTK" ON 1=1
      AND "VTTP"."MANDT" = "VTTK"."MANDT"
      AND "VTTP"."TKNUM" = "VTTK"."TKNUM";

      In the Create Datamodel Views data transformation update the O2C_SHIPPING_TYPE_METADATA table to the following version:

      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 <%=INSERT_YOUR_DATA_JOB_HERE%>."SHIPPING_TYPES" AS "SHIPPING_TYPES"
      );
    • Update the route tables

      Why? 

      In the previous versions we used a combination of country and city as input for the carbon engine to calculate the emissions for a route. Now we will add two new columns called DEPARTURE_QUERY and ARRIVAL_QUERY. From now on these columns will be used as input for the carbon engine. As default the query is still the combination of country and city, but it provides you with the flexibility to adjust it based on your needs. So, for example, if you have the zip code or street data available, you can add it to the query to make the emissions calculation more detailed.

      How?

      In the Create Table: Shipment Network (Signal Link) data transformation add the following two columns to SHIPMENT_DELIVERY_TMP:

      DEPARTURE_QUERY

      ...
      ,COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_COUNTRY", "ADDRESS_BACKUP"."DEPARTURE_COUNTRY") ||'-'|| COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_CITY", "ADDRESS_BACKUP"."DEPARTURE_CITY") AS "DEPARTURE_QUERY" -- Departure Query
      ...

      ARRIVAL_QUERY

      ...
      ,COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_COUNTRY", "ADDRESS_BACKUP"."ARRIVAL_COUNTRY") ||'-'|| COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_CITY", "ADDRESS_BACKUP"."ARRIVAL_CITY") AS "ARRIVAL_QUERY" -- Arrival Query
      ...

      Important

      The two new fields DEPARTURE_QUERY and ARRIVAL_QUERY will be used to specify the route for the emission calculation. It is possible to add additional address information such as postal codes or street names to the queries if these information are available in your data. But make sure that any change on these query fields also need to be reflected in the UNIQUE_STAGE_IDENTIFIER column as this Identifier needs to be the combination of DEPARTURE_QUERY-ARRIVAL_QUERY-SHIPPING_TYPE.

      In addition, add the DEPARTURE_QUERY and ARRIVAL_QUERY (bold) columns to the UNIQUE_SHIPPING_STAGES table:

      DROP TABLE IF EXISTS UNIQUE_SHIPPING_STAGES;
      CREATE TABLE UNIQUE_SHIPPING_STAGES AS
      SELECT
      ...
      ,"DEPARTURE_QUERY"
      ...
      ,"ARRIVAL_QUERY"
      ...
      FROM (
      SELECT
      ...
      ,"DEPARTURE_QUERY"
      ...
      ,"ARRIVAL_QUERY"
      ...
      ) AS "UNIQUE_SHIPPING_STAGES"
      WHERE ROW_NUM = 1
      ;

      In the Create Datamodel Views data transformation, add the DEPARTURE_QUERY and ARRIVAL_QUERY (bold) columns to the O2C_UNIQUE_SHIPPING_ROUTES table:

      DROP VIEW IF EXISTS O2C_UNIQUE_SHIPPING_ROUTES;
      CREATE VIEW "O2C_UNIQUE_SHIPPING_ROUTES" AS (
      SELECT
      ...
      ,"DEPARTURE_QUERY"
      ...
      ,"ARRIVAL_QUERY"
      ...
      FROM (
      SELECT
      ...
      ,"DEPARTURE_QUERY"
      ...
      ,"ARRIVAL_QUERY"
      ...
      ) 
      AS "UNIQUE_SHIPPING_STAGES"WHERE ROW_NUM = 1
      );
    • Re-load data model

      To reflect the changes, please make sure to re-run the Create Table: Shipment Network (Signal Link) and Create Datamodel Views transformations. It is also necessary to re-load the data model.

  3. Knowledge Model Configuration

    By moving the carbon engine from the MLWB to the centralized backend version, it is necessary to configure your user-specific parameters in the app knowledge model. The following parameters needs to be set before the emission calculation can be executed:

    • Carbon Engine API Key

    • Celonis API Token

    • Celonis URL

    • Data Pool ID

    • Data Model ID

    • Space ID

    • Package ID

    • Knowledge Model ID

    For this purpose, add the following yaml configuration to the Variables section in your knowledge model:

      - displayName: Endpoint URL for our carbon engine
        id: VAR_CARBON_ENGINE_ENDPOINT_URL
        value: https://beta4.api.climatiq.io/freight/intermodal
     - displayName: API key for our carbon engine
        id: VAR_CARBON_ENGINE_API_KEY
        value: Bearer <YOUR_CLIMATIQ_API_KEY>
     - displayName: Celonis API token required for triggering the emission calculation
        id: VAR_CELONIS_API_TOKEN
        value: <YOUR_PERSONAL_API_KEY>
      - description: "Example: https://companyA.eu-1.celonis.cloud/"
        displayName: Your Celonis Platform team URL
        id: VAR_CELONIS_URL
        value: <YOUR_CELONIS_URL>
      - displayName: ID of the data pool the app is using
        id: VAR_DATA_POOL_ID
        value: <YOUR_DATA_POOL_ID>
      - displayName: ID of the data model the app is using
        id: VAR_DATA_MODEL_ID
        value: <YOUR_DATA_MODEL_ID>
      - displayName: ID of the space where the application is stored
        id: VAR_SPACE_ID
        value: <YOUR_SPACE_ID>
      - displayName: ID of the app package
        id: VAR_PACKAGE_ID
        value: <YOUR_PACKAGE_ID>
      - displayName: ID of the app knowledge model
        id: VAR_KNOWLEDGE_MODEL_ID
        value: <YOUR_KNOWLEDGE_MODEL_ID>
      - id: VAR_CONFIG_TEST_DEPARTURE_CITY
        displayName: VAR_CONFIG_TEST_DEPARTURE_CITY
        value: ${{RT_VAR_CONFIG_TEST_DEPARTURE_CITY}}
      - id: VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_DEPARTURE_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_DEPARTURE_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_DEPARTURE_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_ARRIVAL_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_ARRIVAL_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_ARRIVAL_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_SHIPPING_TYPE
        displayName: VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_SHIPPING_TYPE
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_A_EXISTING_CONFIG_SHIPPING_TYPE}}
      - id: VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_DEPARTURE_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_DEPARTURE_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_DEPARTURE_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_ARRIVAL_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_ARRIVAL_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_ARRIVAL_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_SHIPPING_TYPE
        displayName: VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_SHIPPING_TYPE
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_B_EXISTING_CONFIG_SHIPPING_TYPE}}
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_DEPARTURE_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_DEPARTURE_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_DEPARTURE_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_ARRIVAL_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_ARRIVAL_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_ARRIVAL_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_SHIPPING_TYPE
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_SHIPPING_TYPE
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_SHIPPING_TYPE}}
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_DEPARTURE_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_DEPARTURE_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_DEPARTURE_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_ARRIVAL_LOCATION
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_ARRIVAL_LOCATION
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_ARRIVAL_LOCATION}}
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_SHIPPING_TYPE
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_SHIPPING_TYPE
        value: ${{RT_VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_SHIPPING_TYPE}}
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_EMISSIONS
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_EMISSIONS
        value: "0"
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_DISTANCE
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_DISTANCE
        value: "0"
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_ERROR_MESSAGE
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_ERROR_MESSAGE
        value: "'No error'"
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_EMISSIONS
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_EMISSIONS
        value: "0"
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_DISTANCE
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_DISTANCE
        value: "0"
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_ERROR_MESSAGE
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_ERROR_MESSAGE
        value: "'No error'"
      - id: VAR_LAST_EMISSION_CALC_TIME
        displayName: VAR_LAST_EMISSION_CALC_TIME
        value: "'1900-01-01 23:59:59'"
      - id: VAR_TEST_ST_CONFIG_DISTANCE
        displayName: VAR_TEST_ST_CONFIG_DISTANCE
        value: "0"
      - id: VAR_TEST_ST_CONFIG_EMISSIONS
        displayName: VAR_TEST_ST_CONFIG_EMISSIONS
        value: "0"
      - id: VAR_TEST_ST_CONFIG_ERROR_MESSAGE
        displayName: VAR_TEST_ST_CONFIG_ERROR_MESSAGE
        value: "'No error'"
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_ROUTE_LAST_CALC
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_ROUTE_LAST_CALC
        value: "'undefined'"
      - id: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_SHIPPING_TYPE_LAST_CALC
        displayName: VAR_BENCHMARKING_TRANSPORT_A_NEW_CONFIG_SHIPPING_TYPE_LAST_CALC
        value: "'None'"
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_ROUTE_LAST_CALC
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_ROUTE_LAST_CALC
        value: "'undefined'"
      - id: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_SHIPPING_TYPE_LAST_CALC
        displayName: VAR_BENCHMARKING_TRANSPORT_B_NEW_CONFIG_SHIIPPING_TYPE_LAST_CALC
        value: "'None'"
      - id: VAR_LAST_EMISSION_CALC_COUNT_ROUTES
        displayName: VAR_LAST_EMISSION_CALC_COUNT_ROUTES
        value: "0"
      - id: VAR_TEST_ST_CONFIG_LAST_SHIPPING_TYPE
        displayName: VAR_TEST_ST_CONFIG_LAST_SHIPPING_TYPE
        value: "'None'"
    

    Carbon Engine API Key

    • In the knowledge model, search for <YOUR_CLIMATIQ_API_KEY> and replace it with your personal Climatiq API key.

    Celonis Team URL

    • In the knowledge model, search for <YOUR_CELONIS_TEAM_URL> and replace it with your Celonis Platform-team URL (such as "https://companyA.eu-1.celonis.cloud/").

    Celonis API Token

    • This application token is needed to trigger the carbon engine via an action flow. The best way to do so is by going to your Celonis profile and clicking Edit profile.

    • In the API-Keys section, input a “New API Key Name” such as “Emission Calculation” and then click Create API KEY. Copy this new key to your clipboard.

      rename_cel_api_token.png
    • In the studio knowledge model search for <YOUR_PERSONAL_API_KEY> and replace it with the Climatiq API key which you received from us.

    Data Pool ID

    • Navigate to the data pool you are using for the app. Copy the id from the URL.

    • Example: https://your_team.eu-1.celonis.cloud/integration/ui/pools/0baf644f-b9b7-4ca2-b375-9f875828a969

    • In the studio knowledge model search for <VAR_DATA_POOL_ID> and replace it with the copied ID.

    Data Model ID

    • Navigate to the data model you are using for the app. Copy the id from the URL.

    • Example: https://your_team.eu-1.celonis.cloud/integration/ui/pools/0baf644f-b9b7-4ca2-b375-9f875828a969/data-configuration/process-data-models/141d3e14-8854-411c-8f30-0083a6c3f4df?tab=data-model

    • In the studio knowledge model search for <VAR_DATA_MODEL_ID> and replace it with the copied ID.

    Space ID

    • Navigate to the space in which the app is located. Copy the id from the URL.

    • Example: https://sustainability-dev.beta.celonis.cloud/package-manager/ui/studio/ui/spaces/92650a1a-6f0e-48e3-ae10-06b984d75780/nodes/8deb29ae-3615-4d6f-b84b-0c4d4af98b45

    • In the studio knowledge model search for <YOUR_SPACE_ID> and replace it with the copied ID.

    Package ID

    • Navigate to the settings of your app. Copy the id from the URL.

    • Example: https://sustainability-dev.beta.celonis.cloud/package-manager/ui/studio/ui/spaces/92650a1a-6f0e-48e3-ae10-06b984d75780/nodes/4c1c8985-6ad5-46c2-b38b-61aeaef4c53a/settings?tab=GENERAL

    • In the studio knowledge model search for <YOUR_PACKAGE_ID> and replace it with the copied ID.

    Knowledge Model ID

    • Navigate to the knowledge model of your app. Copy the id from the URL.

    • Example: https://sustainability-dev.beta.celonis.cloud/package-manager/ui/studio/ui/spaces/92650a1a-6f0e-48e3-ae10-06b984d75780/nodes/3813376e-b5f5-474d-bfaa-4c73bec5e38d

    • In the studio knowledge model search for <YOUR_KNOWLEDGE_MODEL_ID> and replace it with the copied ID.

  4. Set up the emission calculation trigger

    • Download the action flow blueprints

      To trigger the carbon engine inside the app, we are using two action flows (“Emission Calculation - user input” and “Emission Calculation - data based”). Please download the blueprints of these action flows from the Marketplace page in the app.

    • Create the action flows and import the blueprints

      Create a new action flow inside the app package and name it “Emission Calculation - user input”. Import the corresponding blueprint.

    Set up the action flows the following way:

    • Add a hook to “Connect to Celonis” module

      hook_connect_to_cel.png

      Add connection to “Get user specific parameters” module (the recommended option here is the choose “Celonis User” as connection type

      add_celonis_user.png
    • Create the json structure for the “Create API input json” module by using the json generator and then copy and paste the following example:

      {"data_pool_id": "abc123", "data_model_id": "abc123", "space_id": "abc123", "package_id": "abc123", "knowledge_model_id": "abc123", "skill_id": 1, "departure_query": "abc123", "arrival_query": "abc123", "shipping_type_selected": "abc123"}

      create_json.png

    Save the action flow

    Create another action flow inside the app package and call it “Emission Calculation - data based”.  Import the corresponding blueprint.

    • Select the same user connection for the “Get user specific parameters” module as above

    • Select the same data structure for the “Create API input json” module (json will look a bit different) as above

    Save the action flow

    Important

    After setting up the action flows, test and activate them. If the execution was successful, publish the package.

    Set up the Trigger Carbon Engine skill

    • Go to the Trigger Carbon Engine skill and click Edit.

    • Select the “execute action flow” module.

    • Make sure that the Action Flow “Emission Calculation - user input” is chosen.

    • Then publish the package again.

  5. Set permissions

    When the carbon engine is triggered via the action flow for the first time in your Celonis Platform team, an application key is created (called “shipping-emissions-job”). You can manage the key in the Applications tab of the Admin & Settings section in the Celonis Platform

    shipping_emissions_job.png

    By default, the key does not have any permissions in the team yet. In the Permissions tab you need to set the necessary permissions for services used in the app.

    permissions1.png

    In addition, you have to set the permissions on the specific Celonis Platform objects used for the emission calculation. This is necessary, because, for example, the carbon engine is pulling and pushing data from your data model. Therefore it is required to give the AppKey of the carbon engine permission on that data pool.

    permissions2.png

    Please provide permissions for the “shipping-emissions-job” on the following components:

    • The data pool which is used by the app

    • The data model which is used by the app

    • The studio in which the app package is located

    • The app package

  6. Configure the carbon engine and start the emission calculation

    Go to the “Configuration Carbon Engine” view in the App Configuration folder and then follow the guide to configure your shipping types for the emission calculation.