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)
Update your shipping emissions app to the latest version
Go to the Settings page in the app package.
Click on Dependencies.
Update the app.
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.
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.
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.
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
Add connection to “Get user specific parameters” module (the recommended option here is the choose “Celonis User” as connection type
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"}
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.
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.
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.
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.
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
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.