Skip to main content

Celonis Product Documentation

Migration from v2 to v3
Guide to 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 and then 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 data transformation “Create Table: Shipment Network (Signal Link)” 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 data transformation “Create Datamodel Views” 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 added two now 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 data transformation “Create Table: Shipment Network (Signal Link)” 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 like postal codes or street names to the queries if these information are available in your data. But make sure, that any change on this query fields also needs 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 data transformation “Create Datamodel Views” add the DEPARTUER_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 calcluation

        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>, please replace it by your personal Climatiq API key.

    Celoins Team URL

    In the knowledge model search for <YOUR_CELONIS_TEAM_URL>, please replace it by your Celonis Platform-team URL (like "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 on “Edit profile”. 

    In the section “API-Keys”, input a “New API Key Name” like “Emission Calculation” and click on “Create API KEY”. Copy this key to your clipboard.

    rename_cel_api_token.png

    In the studio knowledge model search for <YOUR_PERSONAL_API_KEY>, please replace it by your Climatiq API key which you have 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>. Replace it by 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>. Replace it by 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>. Replace it by 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>. Replace it by 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>. Replace it by 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 of the app.

    • Create the action flows and import the blueprints

      Create a new action flow inside the app package and call 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 copy/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, please test and activate them. If the execution was successful, please publish the package.

    • Set up the “trigger carbon engine” skill

      Go to the “Trigger Carbon Engine” skill, click Edit and select the “execute action flow” module. Make sure that the Action Flow “Emission Calculation - user input” is chosen. After that, 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.