01 - Real Time Transformations for Data Model Tables

Celonis Product Documentation

01 - Real Time Transformations for Data Model Tables

Tip

Data model tables are all tables that are used within the data model, but are neither raw data tables nor activity tables.

Step 1: Identify the Trigger Table
For Data Model Tables, the identification of the trigger table is straightforward in most scenarios as it is the source data table that it represents. In our example it isKNA1. You will save and execute the delta transformation statement under the identified trigger table in the Replication Cockpit.

Step 2: Select from the Staging Table

Replace the table which was identified as Trigger Table to select from the corresponding Staging Table. This will make sure that the transformations are executed against only newly extracted records. The successfully processed records will be automatically cleaned from the staging table so that they are not reprocessed during the next run.

Staging Table Syntax

The syntax of the Staging Table is defined as _CELONIS_TMP_TABLE_TRANSFORM_DATA → for KNA1 it is _CELONIS_TMP_KNA1_TRANSFORM_DATA

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 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 "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

Step 3: Use tables instead of views

Views are calculated/executed each time when they are accessed or loaded in the Data Model. They do not allow insert/update/deletion operations, which is one of the main concepts of delta transformations. Therefore, we need to change them to tables.

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 "_CELONIS_TMP_KNA1_TRANSFORM_DATA" 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 "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

Since the tables did not exist before, they need to be initially created in the beginning. This can be done within a Data Job with the following script

Create DM Table

-- This code creates a DM table for KNA based on the view that is already defined
-- Note: needs to be executed only once

Create table O2C_KNA1 as select * from O2C_KNA1

Step 4: Change to Delete + Insert Approach

In the old transformation logic, everything is cropped and re-created upon each execution. For Delta Transformations we want to incrementally add new records to the existing set.

To achieve this we insert the new values from the staging table after we delete entries with the same primary key in order to avoid duplicates.

Tip

The Delete + Insert approach is suitable for Data Model Tables as the records can be updated in the source system and each record can be easily identified by its unique primary key.

Delete

The delete operation deletes all entries from the Data Model table that have the same primary key as an entry in the staging table. Therefore, all columns of the table's primary key should be checked in the where statement.

Insert

The insert operation inserts all records from the staging table into the Data Model table.

Example

Before

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 "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

After

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

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

Step 5: Delete Temporary Tables and move the query directly into the transformation

For the concept of Delta Transformations, temp tables that are used in multiple different transformations (with different trigger tables) are not possible anymore. The idea is that the operations of the temp tables are implemented as a subquery directly into the transformation. The results are therefore generated dynamically during run time, rather than in advance (before the execution of the transformation).

Example

Before

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

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

After

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

INSERT INTO O2C_KNA1
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT 1 
                FROM VBAK 
        "VBAK"."MANDT" = "KNA1"."MANDT"
                AND "VBAK"."KUNNR" = "KNA1"."KUNNR" 
                        AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
    );

Note

Only select from raw tables (the tables that are extracted via the Replication Cockpit). Do not use tables that are created within other transformations!

Step 6: Select all columns specifically

Metadata changes in the source tables can break the transformations if all columns are referenced (e.g. KNA.*). Therefore, we currently recommend referencing all columns of the Data Model Table (in our case O2C_KNA1) specifically.

Example

Before

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

INSERT INTO O2C_KNA1
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT 1 
                FROM VBAK 
        "VBAK"."MANDT" = "KNA1"."MANDT"
                AND "VBAK"."KUNNR" = "KNA1"."KUNNR" 
                        AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
    );

After

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

INSERT INTO O2C_KNA1
        SELECT 
        "KNA1"."MANDT", "KNA1"."KUNNR", "KNA1"."LAND1", "KNA1"."NAME1", "KNA1"."NAME2", "KNA1"."ORT01",         "KNA1"."PSTLZ", "KNA1"."REGIO", "KNA1"."SORTL", "KNA1"."STRAS", "KNA1"."TELF1", "KNA1"."TELFX", "KNA1"."XCPDK", "KNA1"."ADRNR", "KNA1"."MCOD1", "KNA1"."MCOD2", "KNA1"."MCOD3", "KNA1"."ANRED", "KNA1"."AUFSD", "KNA1"."BAHNE", "KNA1"."BAHNS", "KNA1"."BBBNR", "KNA1"."BBSNR", "KNA1"."BEGRU", "KNA1"."BRSCH", "KNA1"."BUBKZ", "KNA1"."DATLT", "KNA1"."ERDAT", "KNA1"."ERNAM", "KNA1"."EXABL", "KNA1"."FAKSD", "KNA1"."FISKN", "KNA1"."KNAZK", "KNA1"."KNRZA", "KNA1"."KONZS", "KNA1"."KTOKD", "KNA1"."KUKLA", "KNA1"."LIFNR", "KNA1"."LIFSD", "KNA1"."LOCCO", "KNA1"."LOEVM", "KNA1"."NAME3", "KNA1"."NAME4", "KNA1"."NIELS", "KNA1"."ORT02", "KNA1"."PFACH", "KNA1"."PSTL2", "KNA1"."COUNC", "KNA1"."CITYC", "KNA1"."RPMKR", "KNA1"."SPERR", "KNA1"."SPRAS", "KNA1"."STCD1", "KNA1"."STCD2", "KNA1"."STKZA", "KNA1"."STKZU", "KNA1"."TELBX", "KNA1"."TELF2", "KNA1"."TELTX", "KNA1"."TELX1", "KNA1"."LZONE", "KNA1"."XZEMP", 
 "KNA1"."VBUND", "KNA1"."STCEG", "KNA1"."DEAR1", "KNA1"."DEAR2", "KNA1"."DEAR3", "KNA1"."DEAR4", KNA1"."DEAR5", "KNA1"."GFORM", "KNA1"."BRAN1", "KNA1"."BRAN2", "KNA1"."BRAN3", "KNA1"."BRAN4", "KNA1"."BRAN5", "KNA1"."EKONT", "KNA1"."UMSAT", "KNA1"."UMJAH", "KNA1"."UWAER", "KNA1"."JMZAH", "KNA1"."JMJAH", "KNA1"."KATR1", "KNA1"."KATR2", "KNA1"."KATR3", "KNA1"."KATR4", "KNA1"."KATR5", "KNA1"."KATR6", "KNA1"."KATR7", "KNA1"."KATR8", "KNA1"."KATR9", "KNA1"."KATR10", "KNA1"."STKZN", "KNA1"."UMSA1", "KNA1"."TXJCD", "KNA1"."PERIV", "KNA1"."ABRVW", "KNA1"."INSPBYDEBI", "KNA1"."INSPATDEBI", "KNA1"."KTOCD", "KNA1"."PFORT", "KNA1"."WERKS", "KNA1"."DTAMS", "KNA1"."DTAWS", "KNA1"."DUEFL", "KNA1"."HZUOR", "KNA1"."SPERZ", "KNA1"."ETIKG", "KNA1"."CIVVE", "KNA1"."MILVE", "KNA1"."KDKG1", "KNA1"."KDKG2", "KNA1"."KDKG3", "KNA1"."KDKG4", "KNA1"."KDKG5", "KNA1"."XKNZA", "KNA1"."FITYP", "KNA1"."STCDT", "KNA1"."STCD3", "KNA1"."STCD4", "KNA1"."XICMS", "KNA1"."XXIPI", "KNA1"."XSUBT", "KNA1"."CFOPC",   "KNA1"."TXLW1", "KNA1"."TXLW2", "KNA1"."CCC01", "KNA1"."CCC02", "KNA1"."CCC03", "KNA1"."CCC04", "KNA1"."CASSD", "KNA1"."KNURL", "KNA1"."J_1KFREPRE", "KNA1"."J_1KFTBUS", "KNA1"."J_1KFTIND", "KNA1"."CONFS", "KNA1"."UPDAT", "KNA1"."UPTIM", "KNA1"."NODEL", "KNA1"."DEAR6", "KNA1"."/VSO/R_PALHGT", "KNA1"."/VSO/R_PAL_UL", "KNA1"."/VSO/R_PK_MAT", "KNA1"."/VSO/R_MATPAL", "KNA1"."/VSO/R_I_NO_LYR", "KNA1"."/VSO/R_ONE_MAT",     "KNA1"."/VSO/R_ONE_SORT", "KNA1"."/VSO/R_ULD_SIDE", "KNA1"."/VSO/R_LOAD_PREF", "KNA1"."/VSO/R_DPOINT", "KNA1"."ALC", "KNA1"."PMT_OFFICE", "KNA1"."PSOFG", "KNA1"."PSOIS", "KNA1"."PSON1", "KNA1"."PSON2", "KNA1"."PSON3", "KNA1"."PSOVN", "KNA1"."PSOTL", "KNA1"."PSOHS", "KNA1"."PSOST", "KNA1"."PSOO1", "KNA1"."PSOO2", "KNA1"."PSOO3", "KNA1"."PSOO4", "KNA1"."PSOO5", "KNA1"."ZZVPOPNE", "KNA1"."ZZKFZTYPMIN", "KNA1"."ZZKFZTYPMAX",      "KNA1"."ZZHAENGERKZ", "KNA1"."ZZTOBACCOCUST", "KNA1"."ZZTOBCUSTDAT", "KNA1"."ZZF_TYPE", "KNA1"."ZZTOBSERVICE",
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT 1 
                FROM VBAK 
        "VBAK"."MANDT" = "KNA1"."MANDT"
                AND "VBAK"."KUNNR" = "KNA1"."KUNNR" 
                        AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
    );

Step 7: Define Dependencies
As the last step, the corresponding dependent tables need to be identified and configured within the Replication Cockpit. Dependent tables are usually all tables that are being inner joined on or that are used in a Exist-statement. For the above mentioned example, the dependent table of KNA1 would be VBAK.
The dependent tables need to be selected in the Transformation Configuration Tab in the Replication Cockpit.

Was this helpful?

Would you like to provide feedback? Just click here to suggest edits.

Celonis Product Documentation

01 - Real Time Transformations for Data Model Tables

Tip

Staging Table Syntax

Tip

Delete

Insert

Note

Search results