Skip to main content

Step 2: Setting up Real-Time Transformations for Activities

To move your Activity transformations to real-time, you must transition from a single, massive activity table to a decentralized model where each trigger table manages its own specific activities.

Step 2.1 - Identifying Your Trigger Tables

For activities, the "Trigger Table" is the one where a new or updated record creates the event (e.g., the table containing the timestamp).

  • Common Triggers: VBFA (for status changes), CDHDR (for record changes), or BKPF (for financial headers).

  • Action: You will save your SQL logic in the Replication Cockpit under each specific trigger table.

Step 2.2 - Splitting the Activity Table

In a real-time environment, multiple tables may try to write activities simultaneously, causing "table locks." To prevent this, you must create a dedicated table for each trigger.

  • The Rule: Create one table per trigger (e.g., _CEL_O2C_CDHDR_ACTIVITIES).

  • Initial Setup: Run a one-time script in a Data Job to initialize these tables using the structure of your existing activity table.

    CREATE TABLE _CEL_O2C_CDHDR_ACTIVITIES LIKE _CEL_O2C_ACTIVITIES;

Warning

You must manually initialize your dedicated activity tables in a Data Job (using the LIKE syntax) before activating the transformation in the Replication Cockpit.

Step 2.3 - Choosing a Delta Approach

Depending on how your activity behaves, choose one of these three logic patterns for your SQL:

Insert

Insert where not exists

Merge

When to use?

  • no updates of activities required after the first creation

  • no updates possible in the trigger table (insert only) or new activity for every update wanted

  • no updates of activities are required after the first creation

  • updates are possible in the trigger table

  • updates of activities after the first creation required

  • updates are possible in the trigger table

Implications

Creation or updating a record in the trigger table always creates a new activity.

Creation or updating a record in the trigger table creates a new activity when one with the specified conditions does not exist yet.

Creation or updating a record in the trigger table creates a new activity and updates existing ones that fulfill the specified condition.

Disadvantage

Activities cannot be updated, once they were created

very complex script (might require introducing an Activity Key)

Performance

+++

++

+

Tip

An Activity Key is typically a concatenation of your Case Key, Activity Name, and Eventtime. This unique identifier allows the MERGE statement to find and update the exact row without creating duplicates.

Step 2.4 - Implementing the SQL logic

Update your transformation script to pull from the staging table. Use the "After" logic below as a template to avoid duplicates.

Example: Insert where not exists

INSERT INTO _CEL_O2C_VBAP_ACTIVITIES ( ... )
SELECT * FROM (
    SELECT 
        VBAP.MANDT || VBAP.VBELN || VBAP.POSNR AS _CASE_KEY,
        'Create Sales Order Item' AS ACTIVITY_EN,
        ...
    FROM _CELONIS_TMP_VBAP_TRANSFORM_DATA AS VBAP
) AS NEW_ACTIVITIES 
WHERE NOT EXISTS (
    SELECT 1 FROM _CEL_O2C_VBAP_ACTIVITIES AS OLD_ACTIVITIES
    WHERE NEW_ACTIVITIES._ACTIVITY_KEY = OLD_ACTIVITIES._ACTIVITY_KEY
);

Step 2.5 - Creating a Union view

Combine your individual activity tables into one "Union View." This view acts as the single source of truth for your Data Model.

  • Action: Create a view that uses UNION ALL to pull from every dedicated activity table.

  • Benefit: This only needs to be created once; it automatically reflects new data from all triggers.

CREATE VIEW _CEL_O2C_ACTIVITIES AS (
    SELECT * FROM _CEL_O2C_VBAK_ACTIVITIES   
    UNION ALL
    SELECT * FROM _CEL_O2C_VBAP_ACTIVITIES    
    -- Add all other dedicated activity tables here
);

Note

Always use UNION ALL instead of UNION. UNION ALL is significantly faster because it does not perform a distinct check, which is unnecessary since your source activity tables are already decentralized by trigger.

Step 2.6 - Replacing temporary table and define dependencies

  • No Temp Tables: Real-Time Transformations do not support global temporary tables. Move this logic into subqueries or CTEs within your main script.

  • Set Dependencies: List any tables used in INNER JOIN or EXISTS statements as Dependencies in the Replication Cockpit to ensure data is synchronized.

Next steps