Celonis Product Documentation

Create a new transformation with the title “Create Table: Carbon Engine Emissions Data”:

-- Query 1
DROP TABLE IF EXISTS "TRANSPORT_EMISSIONS_DATA";

-- Query 2
CREATE TABLE "TRANSPORT_EMISSIONS_DATA" (
     UNIQUE_STAGE_IDENTIFIER      VARCHAR(2000)     PRIMARY KEY
     ,UNIQUE_STAGE                VARCHAR(2000)
     ,TRANSPORT_MODE_MAIN_LEG     VARCHAR(2000)
     ,CO2E                        FLOAT
     ,CO2E_UNIT                   VARCHAR(2000)
     ,GEOJSON_VISUALIZATION       VARCHAR(65000)
     ,ERROR                       VARCHAR(2000)
     ,"ERROR_MESSAGE"             VARCHAR(2000)
     ,_CELONIS_CHANGE_DATE        DATE
);

-- Query 1
DROP TABLE IF EXISTS "TRANSPORT_SECTIONS_EMISSIONS_DATA";

-- Query 2
CREATE TABLE "TRANSPORT_SECTIONS_EMISSIONS_DATA" (
     UNIQUE_STAGE_IDENTIFIER           VARCHAR(2000)
     ,LEG_IDENTIFIER                   VARCHAR(2000)
     ,TRANSPORT_MODE_MAIN_LEG          VARCHAR(2000)
     ,UNIQUE_STAGE                     VARCHAR(2000)
     ,DEPARTURE_POINT                  VARCHAR(2000)
     ,DEPARTURE_POINT_LATITUDE         FLOAT
     ,DEPARTURE_POINT_LONGITUDE        FLOAT
     ,DEPARTURE_GEOCODING_CONFIDENCE   FLOAT
     -- ,DEPARTURE_FIELD_SCORE_CITY       FLOAT
     -- ,DEPARTURE_FIELD_SCORE_COUNTRY    FLOAT
     ,ARRIVAL_POINT                    VARCHAR(2000)
     ,ARRIVAL_POINT_LATITUDE           FLOAT
     ,ARRIVAL_POINT_LONGITUDE          FLOAT
     ,ARRIVAL_GEOCODING_CONFIDENCE     FLOAT
     -- ,ARRIVAL_FIELD_SCORE_CITY         FLOAT
     -- ,ARRIVAL_FIELD_SCORE_COUNTRY      FLOAT
     ,TRANSPORT_MODE                   VARCHAR(2000)
     ,DISTANCE                         FLOAT
     ,CO2E                             FLOAT
     ,CO2E_UNIT                        VARCHAR(2000)
     ,ERROR                            VARCHAR(2000)
     ,"ERROR_MESSAGE"                  VARCHAR(2000)
     ,_CELONIS_CHANGE_DATE             DATE
);

-- Query 1
DROP TABLE IF EXISTS "TRANSPORT_EMISSION_FACTORS";

-- Query 2
CREATE TABLE "TRANSPORT_EMISSION_FACTORS" (
     UNIQUE_STAGE_IDENTIFIER     VARCHAR(2000)
     ,LEG_IDENTIFIER             VARCHAR(2000)
     ,FACTOR_IDENTIFIER          VARCHAR(2000)
     ,TRANSPORT_MODE_MAIN_LEG    VARCHAR(2000)
     ,UNIQUE_STAGE               VARCHAR(2000)
     ,SECTOR                     VARCHAR(2000)
     ,CATEGORY                   VARCHAR(2000)
     ,ID                         VARCHAR(2000)
     ,UUID                       VARCHAR(2000)
     ,UNIT                       VARCHAR(2000)
     ,UNCERTAINTY                VARCHAR(2000)
     ,SOURCE                     VARCHAR(2000)
     ,"YEAR"                     VARCHAR(2000)
     ,REGION                     VARCHAR(2000)
     ,PARAMETER_VALUE            FLOAT
     ,LCA_ACTIVITY               VARCHAR(2000)
     ,DATA_QUALITY_FLAGS         VARCHAR(2000)
     ,ACCESS_TYPE                VARCHAR(2000)
     ,CALCULATION_ORIGIN         VARCHAR(2000)
     ,CALCULATION_METHOD         VARCHAR(2000)
     ,CO2E_FROM_SOURCE           FLOAT
     ,CO2E_OTHER                 FLOAT
     ,CO2                        FLOAT
     ,CH4                        FLOAT
     ,N2O                        FLOAT
     ,CO2E                       FLOAT
     ,ERROR                      VARCHAR(2000)
     ,"ERROR_MESSAGE"            VARCHAR(2000)
     ,_CELONIS_CHANGE_DATE       DATE
);

Create a new transformation with the title “Create Datamodel Views”:

DROP VIEW IF EXISTS O2C_VTTP;
CREATE VIEW "O2C_VTTP" AS (
     SELECT *
     FROM <YOUR_SAP_ECC_DATASOURCE>."O2C_VTTP"
   );

DROP VIEW IF EXISTS O2C_VTTK;
CREATE VIEW "O2C_VTTK" AS (
     SELECT *
     FROM <YOUR_SAP_ECC_DATASOURCE>."O2C_VTTK"
   );

DROP VIEW IF EXISTS O2C_VTTS;
CREATE VIEW "O2C_VTTS" AS (
     SELECT *
     FROM <YOUR_SAP_ECC_DATASOURCE>."O2C_VTTS"
   );

DROP VIEW IF EXISTS O2C_VTSP;
CREATE VIEW "O2C_VTSP" AS (
     SELECT *
     FROM <YOUR_SAP_ECC_DATASOURCE>."O2C_VTSP"
   );

DROP VIEW IF EXISTS O2C_SHIPMENT_NETWORK;
CREATE VIEW "O2C_SHIPMENT_NETWORK" AS (
     SELECT *
     FROM <YOUR_SAP_ECC_DATASOURCE>."SHIPMENT_NETWORK"
   );

DROP VIEW IF EXISTS O2C_UNIQUE_SHIPPING_STAGES;
CREATE VIEW "O2C_UNIQUE_SHIPPING_STAGES" AS (
     SELECT *
     FROM <YOUR_SAP_ECC_DATASOURCE>."UNIQUE_SHIPPING_STAGES"
   );

DROP VIEW IF EXISTS O2C_UNIQUE_SHIPPING_ROUTES;
CREATE VIEW "O2C_UNIQUE_SHIPPING_ROUTES" AS (
     SELECT
          "UNIQUE_STAGE"
          ,"DEPARTURE_COUNTRY"
          ,"DEPARTURE_CITY"
          ,"ARRIVAL_COUNTRY"
          ,"ARRIVAL_CITY"
     FROM (
          SELECT DISTINCT
               "UNIQUE_STAGE"
               ,"DEPARTURE_COUNTRY"
               ,"DEPARTURE_CITY"
               ,"ARRIVAL_COUNTRY"
               ,"ARRIVAL_CITY"
               ,ROW_NUMBER() OVER (PARTITION BY "UNIQUE_STAGE") AS ROW_NUM
          FROM
               <YOUR_SAP_ECC_DATASOURCE>."UNIQUE_SHIPPING_STAGES"
     ) AS "UNIQUE_SHIPPING_STAGES"
     WHERE ROW_NUM = 1
   );

DROP VIEW IF EXISTS O2C_TRANSPORT_EMISSIONS_DATA;
CREATE VIEW "O2C_TRANSPORT_EMISSIONS_DATA" AS (
     SELECT *
     FROM "TRANSPORT_EMISSIONS_DATA"
  ); 

DROP VIEW IF EXISTS O2C_TRANSPORT_SECTIONS_EMISSIONS_DATA;
CREATE VIEW "O2C_TRANSPORT_SECTIONS_EMISSIONS_DATA" AS (
     SELECT *
     FROM "TRANSPORT_SECTIONS_EMISSIONS_DATA"
   );

DROP VIEW IF EXISTS O2C_TRANSPORT_EMISSION_FACTORS;
CREATE VIEW "O2C_TRANSPORT_EMISSION_FACTORS" AS (
     SELECT *
     FROM "TRANSPORT_EMISSION_FACTORS"
   );