Skip to main content

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"
            WHERE 1=1
                AND "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"
            WHERE 1=1
                AND "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"
            WHERE 1=1
                AND "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"
            WHERE 1=1
                AND "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"
            WHERE 1=1
                AND "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 WHERE 1=1
                                    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"
            WHERE 1=1
                AND "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 WHERE 1=1
                                    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"
            WHERE 1=1
                AND "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 WHERE 1=1
                                    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 
            WHERE 1=1
                    AND "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 WHERE 1=1
                                    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 
            WHERE 1=1
                    AND "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 WHERE 1=1
                                    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 
            WHERE 1=1
                    AND "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.