Skip to main content

Celonis Product Documentation

Set up of a Multi-Event Log Data Pool

Data Extraction

All data for your Multi-Event Log Data Model needs to be extracted and stored in the same Data Pool.

Data Transformation

Use Case 3 (as mentioned here mentions simplified scripts, because writing transformations are a lot easier in Multi-Event Log scenarios.

Instead of having one case key and mapping all activities to this perspective, the Multi-Event Log allows keeping different perspectives (that means case definitions) for different processes. For each process, users can simply create a separate activity table with the corresponding case key of that process. This takes away the burden to create complicated joins between source tables for each activity definition. Instead, the link between processes is shifted from the transformation to the Data Model. Their relationship is simply modeled via join key relationships in the visual Data Model Editor.

Let’s look at an example, the definition of the ‘Clear Invoice’ activity in the SAP Purchase-To-Pay process. Usually, this activity is defined in the Purchase-To-Pay process with the following SQL statement:

SELECT DISTINCT
E._CASE_KEYAS"_CASE_KEY"
,E.MANDT AS"MANDT"
,E.EBELN AS"EBELN"
,E.EBELP AS"EBELP"
,'Gleiche Rechnung aus'AS"ACTIVITY_DE"
,'Clear Invoice'AS"ACTIVITY_EN"
,CASE
WHENCAST(BKPF_Z."CPUDT"AS DATE)=CAST(B."AUGDT"AS DATE)
THENCAST(BKPF_Z."CPUDT"AS DATE)+CAST(BKPF_Z."CPUTM"as TIME)
ELSECAST(B."AUGDT"AS DATE)+CAST('23:59:59'as TIME)
END AS"EVENTTIME"
,2500AS"_SORTING"
,BKPF_Z.USNAM AS"USER_NAME"
,USR02.USTYP AS"USER_TYPE"
,BKPF_Z.TCODE AS"TRANSACTION_CODE"
FROM
RSEG ASRSEG
INNERJOINTMP_P2P_EKKO_EKPO ASEON 1=1
ANDRSEG.MANDT=E.MANDT
ANDRSEG.EBELN=E.EBELN
ANDRSEG.EBELP=E.EBELP
INNER JOINTMP_P2P_BKPF_BSEG ASBON 1=1
ANDB.MANDT=RSEG.MANDT
AND B.BUKRS=BKPF_Z.BUKRS
AND B.AUGBL=BKPF_Z.BELNR
AND B.AUGGJ=BKPF_Z.GJAHR
LEFT JOINUSR02ASUSR02ON1=1
AND BKPF_Z.MANDT=USR02.MANDT
AND BKPF_Z.USNAM=USR02.BNAME
WHERE
B.AUGBLIS NOT NULL AND
BKPF_Z.CPUDTIS NOT NULL;

The details like the ‘User Name’ for the ‘Clear Invoice’ activity are not stored in the Purchase Order table (EKPO). They are stored in the Accounting Document Header table (BKPF). To add this information, it is necessary to create this lengthy join starting from EKKO/EKPO to RSEG to BKPF/BSEG to the clearing document in BKPF (declared as BKPF_Z).

With the Multi-Event Log approach, users can save lines of code and the associated headache. The clearing activity can be written together with all other invoice-related activities into a separate activity table with the case key of the corresponding invoice document. This way, the table join is much shorter. The link to a purchase document is not necessary at this point. The SQL statement can be reduced to the following expression:

SELECT DISTINCT
B."_CASE_KEY" AS"_CASE_KEY"
,'Clear Invoice'AS"ACTIVITY_EN"
,CASE
WHENCAST(BKPF_Z."CPUDT"ASDATE)=CAST(B."AUGDT"AS DATE)
THENCAST(BKPF_Z."CPUDT"ASDATE)+CAST(BKPF_Z."CPUTM"as TIME)
ELSECAST(B."AUGDT"ASDATE)+CAST('23:59:59'as TIME)
END AS"EVENTTIME"
,BKPF_Z."USNAM" AS"USER_NAME"
,USR02."USTYP" AS"USER_TYPE"
,900AS"_SORTING"
,B."MANDT" AS"MANDT"
,B."BUKRS" AS"BUKRS"
,B."BELNR" AS"BELNR"
,B."BUZEI" AS"BUZEI"
,BKPF_Z."TCODE" AS"TRANSACTION_CODE"
FROM
"TMP_AP_BKPF_BSEG" ASB
LEFT JOIN"BKPF" ASBKPF_ZON1=1
ANDB."MANDT"=BKPF_Z."MANDT"
ANDB."BUKRS"=BKPF_Z."BUKRS"
ANDB."AUGBL"=BKPF_Z."BELNR"
ANDB."AUGGJ"=BKPF_Z."GJAHR"
LEFT JOIN"USR02"ASUSR02ON1=1
ANDBKPF_Z."MANDT"=USR02."MANDT"
ANDBKPF_Z."USNAM"=USR02."BNAME"
WHERE
B.AUGBLIS NOT NULL AND
BKPF_Z.CPUDTIS NOT NULL;

The example above contains two instead of the previous four table joins. But writing scripts this way, not only simplifies the necessary joins. Using a separate activity table for invoice documents allows users to additionally include invoices in the Data Model that do not have a link to a purchase document. For the SAP example, invoices in the Data Model are not restricted to MM-related invoices (link to Purchase Order). FI invoices are also part of the Data Model which would otherwise only be visible in the Accounts Payable process.

Data Model

The setup of a Multi-Event Log Data Model is similar to the setup of a Data Model with one Event Log. First, all tables of a Data Model need to be added. Afterward, the foreign key relationships between tables need to be defined. Now, every table that serves as an Event Log can be configured as an Activity table by choosing the option “Set as Activity table” shown in the following screenshot.

50731725.png

After choosing the option “Set as Activity table” the configuration mask is automatically opened and Case ID, Activity Name, and Timestamp columns can be defined as usual. Afterward, the configured activity table is marked with an A in the Data Model.

50731727.png

To assign the corresponding case table to an activity table, the option “Assign case table” in the menu of the Activity table needs to be chosen as shown in the following screenshots.

1) Open Activity Table Settings and select “Assign case table”:

50731728.png

2) Choose a case table from the list:

50731729.png

The Default of the Activity table needs to be chosen as shown in the following screenshots.

One Activity Table can be defined as the default Activity table.

50731760.png