Skip to main content

Celonis Product Documentation

03 - Compatibility of Data Jobs and Real Time Transformations

This section explains how you can ensure compatibility between your Full Transformations (run by Data Jobs) and your Delta Transformations (run by the Replication Cockpit). As a prerequisite, we assume that all Data Model Tables and Activities have been converted as explained in the last two sections.

Why is this needed?

The combination of Data Jobs and the Replication Cockpit is needed because the latter one currenlty can only handle Delta Loads of your Extractions and Transformations. All the remaining capabilities still need to be configured via the Data Jobs.

Full Load

Delta Load

Extraction

Data Job

Replication Cockpit

Transformation

Data Job

Replication Cockpit

Data Model Load

Data Job

Data Job

Step-by-step Instructions

With the Delta Transformations, some new concepts for the configuration of your tables & views have been introduced. In order to allow the execution of Full Loads, some modifications on your Full Transformation scripts in the Data Jobs are required as well. Note: These changes do not affect the resulting data in any way, they just impact the way how it is structured and stored.

Note

All steps that are described in this section require changes only in the Data Job that contains the Full Transformations.

  • Step 1: Convert all Data Model views into tables

    To harmonize with the insert/update/delete operations within the Replication Cockpit, we need to change all Data Model items, that were added to the Replication Cockpit, from views to tables. With the usage of views, these operations wouldn't be possible.

    Example

    Before

    DROP VIEW IF EXISTS "O2C_KNA1";

CREATE VIEW "O2C_KNA1" AS (
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "KNA1" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

    After

    DROP TABLE IF EXISTS "O2C_KNA1";

CREATE TABLE "O2C_KNA1" AS (
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "KNA1" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

    Warning

    Before the first execution of the new statement, the existing view needs to be dropped manually.

  • Step 2: Use specific Activity tables

    To harmonize with the insert/update/delete operations of activities within the Replication Cockpit, we also need to switch the activities to insert to the trigger table specific Activity tables.

    Make sure to use the same Activity tables as in the Replication Cockpit for all Activities.

    For an activity triggered by CDHDR in the O2C process, this would be _CEL_O2C_CDHDR_ACTIVITIES.

    Example

    Before

    INSERT INTO "_CEL_O2C_ACTIVITIES"
...

    After

    INSERT INTO "_CEL_O2C_CDHDR_ACTIVITIES"
...

    Note

    In case an activity was not converted to the Delta Transformation logic, we recommend using the specific table " CEL_O2C_DJ_ACTIVITIES" (DJ = Data Job).

    The generic activity table is needed in another place (see next step).

  • Step 3: Initialization of specific & generic Activity tables

    All the specific activity tables need to be dropped and re-created at the beginning of the Full Transformations.

    For the creation, you should adapt the existing statement in the transformation "Create Table: Activities" to the specific Activity tables.

    Additionally, we create a new view that unions all specific Activity tables (=> generic Activity tables). This view will be used in the Data Model and will be executed each time the Data Model load is triggered. As the same name is used, no changes in the Data Model are required.

    Example

    Before

    DROP TABLE IF EXISTS "_CEL_O2C_ACTIVITIES";

CREATE TABLE "_CEL_O2C_ACTIVITIES" (
        "_CASE_KEY" VARCHAR(50)
        ,"ACTIVITY_DE" VARCHAR(300)
        ,"ACTIVITY_DETAIL_DE" VARCHAR(300)
        ,"ACTIVITY_EN" VARCHAR(200)
        ,"ACTIVITY_DETAIL_EN" VARCHAR(300)
        ,"EVENTTIME" DATETIME
        ,"_SORTING" INT
        ,"USER_NAME" VARCHAR(80)
        ,"USER_TYPE" VARCHAR(20)
    ,"CHANGED_TABLE" VARCHAR(20)
    ,"CHANGED_TABLE_TEXT" VARCHAR(200)
    ,"CHANGED_FIELD" VARCHAR(20)
    ,"CHANGED_FIELD_TEXT" VARCHAR(200)
    ,"CHANGED_FROM" VARCHAR (50)
    ,"CHANGED_TO" VARCHAR(50)
    ,"CHANGED_FROM_FLOAT" FLOAT
    ,"CHANGED_TO_FLOAT" FLOAT
        ,"CHANGE_NUMBER" VARCHAR(50)
        ,"TRANSACTION_CODE" VARCHAR(20)
    ,"MANDT" VARCHAR(3)
    ,"VBELN" VARCHAR(10)
    ,"POSNR" VARCHAR(6)
    ,"_ACTIVITY_KEY" VARCHAR(50)
);

    After

    DROP TABLE IF EXISTS _CEL_O2C_CDHDR_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_AUFK_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_BSAD_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_BSAK_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_EKBE_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_JCDS_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_LIPS_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_NAST_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_VBAP_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_VBFA_ACTIVITIES;
DROP TABLE IF EXISTS _CEL_O2C_VBRP_ACTIVITIES;

--------------------------------------------------------

CREATE TABLE "_CEL_O2C_CDHDR_ACTIVITIES" (
        "_CASE_KEY" VARCHAR(50)
        ,"ACTIVITY_DE" VARCHAR(300)
        ,"ACTIVITY_DETAIL_DE" VARCHAR(300)
        ,"ACTIVITY_EN" VARCHAR(200)
        ,"ACTIVITY_DETAIL_EN" VARCHAR(300)
        ,"EVENTTIME" DATETIME
        ,"_SORTING" INT
        ,"USER_NAME" VARCHAR(80)
        ,"USER_TYPE" VARCHAR(20)
    ,"CHANGED_TABLE" VARCHAR(20)
    ,"CHANGED_TABLE_TEXT_DE" VARCHAR(200)
    ,"CHANGED_TABLE_TEXT_EN" VARCHAR(200)
    ,"CHANGED_FIELD" VARCHAR(20)
    ,"CHANGED_FIELD_TEXT_DE" VARCHAR(200)
    ,"CHANGED_FIELD_TEXT_EN" VARCHAR(200)
    ,"CHANGED_FROM" VARCHAR (100)
    ,"CHANGED_TO" VARCHAR(100)
    ,"CHANGED_FROM_FLOAT" FLOAT
    ,"CHANGED_TO_FLOAT" FLOAT
        ,"CHANGE_NUMBER" VARCHAR(50)
        ,"TRANSACTION_CODE" VARCHAR(20)
    ,"MANDT" VARCHAR(3)
    ,"VBELN" VARCHAR(10)
    ,"POSNR" VARCHAR(6)
    ,"_CELONIS_CHANGE_DATE" DATETIME
);

CREATE TABLE   _CEL_O2C_AUFK_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_BSAD_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_BSAK_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_EKBE_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_JCDS_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_LIPS_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_NAST_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_VBAP_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_VBFA_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;
CREATE TABLE   _CEL_O2C_VBRP_ACTIVITIES   LIKE _CEL_O2C_CDHDR_ACTIVITIES ;


------------------------------------------

DROP VIEW IF EXISTS _CEL_O2C_ACTIVITIES;

CREATE VIEW _CEL_O2C_ACTIVITIES AS(
    SELECT * FROM "_CEL_O2C_CDHDR_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_AUFK_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_BSAD_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_BSAK_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_EKBE_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_JCDS_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_LIPS_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_NAST_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_VBAP_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_VBFA_ACTIVITIES"
        UNION ALL
    SELECT * FROM "_CEL_O2C_VBRP_ACTIVITIES"
)
;

    Warning

    Before the first execution of the new statement, the existing Activity table (_CEL_O2C_ACTIVITIES) needs to be dropped manually.

  • Step 4: Organize Data Jobs

    To enable appropriate scheduling of the required Data Jobs we recommend the separation into the following 3 Data Jobs:

    • Full Load: contains Full Transformation (with the steps mentioned in this section) => scheduled at maximum once a week

    • Daily Load: contains the transformations that haven't been converted to the Replication Cockpit (e.g. Currency Conversion, Name Mapping Tables) => scheduled once a day

    • Data Model Load: contains the load of the Data Model => scheduled as frequent as possible dependent on the Duration (each 30 min to each hour)