Real time extractions through Replication Cockpit
The Replication Cockpit enables you to execute real time extractions from your source systems based on triggers. This ensures that your using the latest data in the Celonis Platform with minimal setup and maintenance system.
Supported source systems
You can use the Replication Cockpit for real time extractions from the following source systems:
Azure SQL
Azure Synapse
Microsoft SQL Server
Oracle
Oracle 11g
SAP S/4HANA
SAP S/4HANA (Encrypted)
Setting up real time extractions
When configuring the real time extractions, the following steps are needed for data pools where the Replication Cockpit is configured:
Activating the live extraction mode in the Celonis Platform.
Creating the changelog tables to store the changes in the source system (performed in the database).
Installing triggers to monitor the changes in the source systems (performed in the database).
Step 1: Activating live extraction mode
The first step is to activate the live extraction mode in the Celonis Platform:
From your data pool diagram, click Data Connections and open the required data connection.
Enable Live Data.
Step 2: Creating the changelog tables
The tables which should be extracted need to be added to the Replication Cockpit.
Database tables in the Replication Cockpit have four options in the context menu:
Start: Starts the real-time extraction for that table
Stop: Stops the real-time extraction for that table
Delete: Deletes the table from the real-time cockpit
Trigger Code: This option will display the “Create Table” statement for a changelog table and a “Create Trigger” statement. The statement ist dynamically generated according to the table and database type and copy&paste of the statement should work without any modification.
Step 3: Installing triggers
Initially, in order to start a real-time extraction for the table, users need to execute the trigger code that is supplied with the table in the Replication Cockpit.
Trigger code will have two separate statements:
“Create Table” statement for a changelog table:
This table should not exist before there “create if not exists” should not be used.
This table will contain the changes for that specific table
This table will be named as CEL_CL_{TARGET_TABLE_NAME}
This table will have the following structure;
CEL_ID (varchar 40): Random UUID serves as the primary key for the table
ROW_ID (varchar 255): Unique identifier for the record being changed
CEL_CHANGE_TYPE (type enum(‘I’, ‘U’,’D’)): Defined type of the change either Insert, Update or Delete
CEL_CHANGE_DATE (type datetime): Timestamp of the change
CEL_EXTRACTED (type boolean): if it is already extracted or not
“Trigger Table” statement to create the trigger:
This statement can be ignored if a trigger already exists with the same name
This trigger will insert the changes to the change log table for the corresponding table.
This trigger will be named as CEL_TR_{TARGET_TABLE_NAME}
Real time extractions
During the startup of the database extractor server, a request to integration will be made to get a list of real-time extractions that need to be executed.
For uplinked database servers: Only the real-time tables where the data sources uses that uplink, will be retrieved
For the cloud database servers: All the real-time tables where the data source connection type is direct
For the retrieved real-time tables, a continuous extraction will be created.
This extraction will query the corresponding change log table where CEL_EXTRACTED=false
Extracted data will be inserted into replicated tables using the real-time service
The inserted records are then streamed to the Transformation Service, where the records are processed according to the transformation logic of each table, as defined in EC.
The transformed records are inserted/merged into the transformed tables