Skip to main content

Celonis Product Documentation

CREATE_EVENTLOG
Description

CREATE_EVENTLOG returns an activity table that is based on a given lead object and a set of included event types.

The main purpose of using CREATE_EVENTLOG is to generate an event log which is based on a given object perspective. The user defines the object as base for the projection as well as event types which will be included in the projection. The CREATE_EVENTLOG operator then calculates the resulting event instances which are generated by traversing the most direct join path between the object table and the event type tables.

The result tables are generated as followed: For each event type provided in the input parameters, the most direct path is calculated. Most direct means that the number of traversed objects is minimized. Direct paths between objects and events are preferred over connections via another object. In case two paths have equal length, one path is chosen deterministically. After calculating the mapping for each event type, the resulting events are then merged into one event table and the result contains each event instance at most once per case (deduplication). The result is joined to the respective lead object and contains all dimensions available on the event types. The calculated event log can be used as a drop in replacement for event logs when using PQL.

Syntax

CREATE_EVENTLOG returns a table. To access the columns, the TABLE.COLUMN syntax is used.

 CREATE_EVENTLOG ( object_table_name [ FILTER lead_object_filter ] , INCLUDE [included_event_1 [ VIA ( relationship_1 ) ] [ FILTER event_filter_1 ], included_event_2 [ VIA ( relationship_2 ) ] [ FILTER event_filter_2 ], ...]).column_name

  • object_table_name: The name of the object table, specifying the lead object that all events should be projected to.

  • lead_object_filter: An optional filter expression for the lead object table.

  • included_event_i: The name of the i-th event table that should be included in the projection.

  • relationship_i: The optional VIA keyword can be used per event to specify a named relationship over which to project. This overrides the default projection path.

  • event_filter_i: An optional filter expression for the i-th event table.

  • column_name: The name of the column, that should be accessed. This can be either one of the standard event log columns LEAD_OBJECT_ID, ACTIVITY, TIMESTAMP or any of the individual attributes from included event tables.

LEAD_OBJECT_ID, ACTIVITY and TIMESTAMP columns are generated by default, all other columns, consisting of individual attributes from included event tables, are lazily generated on demand.

  • LEAD_OBJECT_ID: Case identifier

  • ACTIVITY: Activity name

The result table of CREATE_EVENTLOG can be used like a classical event log and can for example be used to calculate KPIs across different objects (from the perspective of the given lead object).

This schema demonstrates how a simplified data model using different projections could look like. The data model contains three different object tables:

  • Sales Order: The Sales Order Header table.

  • Sales Order Item: The Sales Order Item table which is directly connected to the sales order header table.

  • Delivery Item: Deliveries are represented in this table. For the sake of simplicity, there is no delivery header table used in this example.

Additionally, there are several event type tables in this model:

  • Create SO:This is a shared event table which is directly connected to the Sales Order object table and, via a mapping table, connected to the Sales order item table.

  • Add SO Item: This object type table is directly connected to the Sales Order Item table.

  • Create Delivery Item: This is a shared event table which is directly connected to the Delivery Item object table and, via a mapping table, also connected to the Sales order item table.

The mapping tables are necessary to bind the shared events to the objects.

The following example table schema visualizes the lead object tables, the event type tables as well as the projections which are generated:

ProjectOnObject.png
Examples

[1]

This example demonstrates how the CREATE_EVENTLOG operator generates an eventlog based on the SalesOrderItem Object tables including the CreateDeliveryItem event type. The preferred path to the (potentially shared) event type table is via the mapping table M2 to minimize the number of involved objects. Note that the CreateDeliveryItem event (E105) does not show up in the result set as it is not joined via mapping table M2.

Query

Column1 

         CREATE_EVENTLOG("o_celonis_SalesOrderItem",INCLUDE["e_celonis_CreateDeliveryItem"])."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG("o_celonis_SalesOrderItem",INCLUDE["e_celonis_CreateDeliveryItem"])."ACTIVITY"

Column3 

         CREATE_EVENTLOG("o_celonis_SalesOrderItem",INCLUDE["e_celonis_CreateDeliveryItem"])."TIMESTAMP"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

Column2 : string

Column3 : date

'O10'

'e_celonis_CreateDeliveryItem'

Fri Feb 03 2023 00:00:00.000

'O11'

'e_celonis_CreateDeliveryItem'

Sat Mar 04 2023 00:00:00.000

'O12'

'e_celonis_CreateDeliveryItem'

Sun Mar 05 2023 00:00:00.000

'O13'

'e_celonis_CreateDeliveryItem'

Mon Mar 06 2023 00:00:00.000

'O14'

'e_celonis_CreateDeliveryItem'

Thu Mar 16 2023 00:00:00.000

[2]

This example demonstrates how the CREATE_EVENTLOG operator generates an eventlog based on the SalesOrderItem Object tables including the AddSalesOrderItem events which are directly connected to the SalesOrderItem objects. Additionally, the CreateDeliveryItem objects are included which can be reached through the mapping table M2. Note that the CreateDeliveryItem event (E105) does not show up in the result set as it is not joined via mapping table M2.

Query

Column1 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" ] ) ."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" ] ) ."ACTIVITY"

Column3 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" ] ) ."TIMESTAMP"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

Column2 : string

Column3 : date

'O10'

'e_celonis_AddSalesOrderItem'

Thu Feb 02 2023 00:00:00.000

'O10'

'e_celonis_CreateDeliveryItem'

Fri Feb 03 2023 00:00:00.000

'O11'

'e_celonis_AddSalesOrderItem'

Fri Mar 03 2023 00:00:00.000

'O11'

'e_celonis_CreateDeliveryItem'

Sat Mar 04 2023 00:00:00.000

'O12'

'e_celonis_AddSalesOrderItem'

Sat Mar 04 2023 00:00:00.000

'O12'

'e_celonis_CreateDeliveryItem'

Sun Mar 05 2023 00:00:00.000

'O13'

'e_celonis_AddSalesOrderItem'

Sun Mar 05 2023 00:00:00.000

'O13'

'e_celonis_CreateDeliveryItem'

Mon Mar 06 2023 00:00:00.000

'O14'

'e_celonis_AddSalesOrderItem'

Mon Mar 06 2023 00:00:00.000

'O14'

'e_celonis_CreateDeliveryItem'

Thu Mar 16 2023 00:00:00.000

'O15'

'e_celonis_AddSalesOrderItem'

Fri Apr 07 2023 00:00:00.000

[3]

In this example the DeliveryItems object table is the basis of the projection. It uses a join path through the SalesOrder Item object path and the mapping table M1 to connect the shared event type "Create SO". Note that event E003 does not show up in the result set, due to a missing join partner in mapping table M1.

Query

Column1 

         CREATE_EVENTLOG("o_celonis_DeliveryItem",INCLUDE["e_celonis_CreateSalesOrder"])."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG("o_celonis_DeliveryItem",INCLUDE["e_celonis_CreateSalesOrder"])."ACTIVITY"

Column3 

         CREATE_EVENTLOG("o_celonis_DeliveryItem",INCLUDE["e_celonis_CreateSalesOrder"])."TIMESTAMP"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

Column2 : string

Column3 : date

'100'

'e_celonis_CreateSalesOrder'

Wed Feb 01 2023 00:00:00.000

'101'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

'102'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

'103'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

'104'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

[4]

In this example the SalesOrderItem object table is the basis of the projection. It uses the default option of directly connected events, i.e. in this case AddSalesOrderItem, CreateDeliveryItem and CreateSalesOrder, .

Query

Column1 

         CREATE_EVENTLOG("o_celonis_SalesOrderItem")."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG("o_celonis_SalesOrderItem")."ACTIVITY"

Column3 

         CREATE_EVENTLOG("o_celonis_SalesOrderItem")."TIMESTAMP"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

Column2 : string

Column3 : date

'O10'

'e_celonis_CreateSalesOrder'

Wed Feb 01 2023 00:00:00.000

'O10'

'e_celonis_AddSalesOrderItem'

Thu Feb 02 2023 00:00:00.000

'O10'

'e_celonis_CreateDeliveryItem'

Fri Feb 03 2023 00:00:00.000

'O11'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

'O11'

'e_celonis_AddSalesOrderItem'

Fri Mar 03 2023 00:00:00.000

'O11'

'e_celonis_CreateDeliveryItem'

Sat Mar 04 2023 00:00:00.000

'O12'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

'O12'

'e_celonis_AddSalesOrderItem'

Sat Mar 04 2023 00:00:00.000

'O12'

'e_celonis_CreateDeliveryItem'

Sun Mar 05 2023 00:00:00.000

'O13'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

'O13'

'e_celonis_AddSalesOrderItem'

Sun Mar 05 2023 00:00:00.000

'O13'

'e_celonis_CreateDeliveryItem'

Mon Mar 06 2023 00:00:00.000

'O14'

'e_celonis_CreateSalesOrder'

Wed Mar 01 2023 00:00:00.000

'O14'

'e_celonis_AddSalesOrderItem'

Mon Mar 06 2023 00:00:00.000

'O14'

'e_celonis_CreateDeliveryItem'

Thu Mar 16 2023 00:00:00.000

'O15'

'e_celonis_AddSalesOrderItem'

Fri Apr 07 2023 00:00:00.000

[5]

It can happen that the most direct path between an event table and an object table does not suit your use case. This example shows how MERGE_EVENTLOG can be used such that we force a specific path for the projection. One of the CreateSalesOrder events (E003) does not show up when projected to the SalesOrderItem object over the most direct path because of a missing join partner. However, it does show up when we force the projection to traverse the SalesOrder object instead of taking the most direct path.

Query

Column1 

         MERGE_EVENTLOG ( CREATE_EVENTLOG("o_celonis_SalesOrderItem",INCLUDE["e_celonis_AddSalesOrderItem"])."ACTIVITY" , CREATE_EVENTLOG("o_celonis_SalesOrder",INCLUDE["e_celonis_CreateSalesOrder"])."ACTIVITY" )

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

'e_celonis_CreateSalesOrder'

'e_celonis_AddSalesOrderItem'

'e_celonis_CreateSalesOrder'

'e_celonis_AddSalesOrderItem'

'e_celonis_CreateSalesOrder'

'e_celonis_AddSalesOrderItem'

'e_celonis_CreateSalesOrder'

'e_celonis_AddSalesOrderItem'

'e_celonis_CreateSalesOrder'

'e_celonis_AddSalesOrderItem'

'e_celonis_CreateSalesOrder'

'e_celonis_AddSalesOrderItem'

[6]

This example shows how a named relationship can be specified to influence the projection path of an included event type. Instead of the most direct path, the event e_celonis_AddSalesOrderItem is projected via the o_celonis_ SalesOrderItem object (since this is the E2O connection associated with relationship name 'Relationship C')

Query

Column1 

         CREATE_EVENTLOG ( "o_celonis_SalesOrder" , INCLUDE [ "e_celonis_AddSalesOrderItem" VIA ( 'Relationship C' ) ] ) ."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG ( "o_celonis_SalesOrder" , INCLUDE [ "e_celonis_AddSalesOrderItem" VIA ( 'Relationship C' ) ] ) ."ID"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

Time : date

SalesOrder_ID : string

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O01'

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O01'

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O02'

'O12'

e_celonis_CreateSalesOrder

ID : string

Time : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O01'

'O12'

null

r_e_celonis_CreateSalesOrder_SalesOrder

ID : string

OBJECT_ID : string

'E001'

'O01'

'E001'

'O02'

'E002'

'O01'

'E002'

'O02'

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_AddSalesOrderItem.SalesOrder_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrder.ID

o_celonis_SalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrder.OBJECT_ID

Result

Column1 : string

Column2 : string

'O01'

'E010'

'O01'

'E011'

[7]

This example shows how a named relationship can be specified to influence the projection path of an included event type. Instead of the most direct path, the event e_celonis_CreateSalesOrder is projected via the r_e_celonis_CreateSalesOrder_SalesOrder mapping (since this is the E2O connection associated with relationship name 'Relationship D')

Query

Column1 

         CREATE_EVENTLOG ( "o_celonis_SalesOrder" , INCLUDE [ "e_celonis_CreateSalesOrder" VIA ( 'Relationship D' ) ] ) ."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG ( "o_celonis_SalesOrder" , INCLUDE [ "e_celonis_CreateSalesOrder" VIA ( 'Relationship D' ) ] ) ."ID"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

Time : date

SalesOrder_ID : string

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O01'

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O01'

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O02'

'O12'

e_celonis_CreateSalesOrder

ID : string

Time : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O01'

'O12'

null

r_e_celonis_CreateSalesOrder_SalesOrder

ID : string

OBJECT_ID : string

'E001'

'O01'

'E001'

'O02'

'E002'

'O01'

'E002'

'O02'

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_AddSalesOrderItem.SalesOrder_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrder.ID

o_celonis_SalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrder.OBJECT_ID

Result

Column1 : string

Column2 : string

'O01'

'E001'

'O01'

'E002'

'O02'

'E001'

'O02'

'E002'

[8]

In this example we define a filter on the lead object (sales order item), because we do not want to include a certain sales order. While the common table of the filter expression is "o_celonis_SalesOrder_Q", the filter is automatically propagated to the lead object table and will be respected when computing the event log.

Query

Column1 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" FILTER "o_celonis_SalesOrder"."ID" != 'O02' , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" ] ) ."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" FILTER "o_celonis_SalesOrder"."ID" != 'O02' , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" ] ) ."ACTIVITY"

Column3 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" FILTER "o_celonis_SalesOrder"."ID" != 'O02' , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" ] ) ."TIMESTAMP"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

Column2 : string

Column3 : date

'O10'

'e_celonis_AddSalesOrderItem'

Thu Feb 02 2023 00:00:00.000

'O10'

'e_celonis_CreateDeliveryItem'

Fri Feb 03 2023 00:00:00.000

'O15'

'e_celonis_AddSalesOrderItem'

Fri Apr 07 2023 00:00:00.000

[9]

In this example we define a filter on the included 'create delivery' activity, because we do not want to include events that are more recent than a certain date. The filter is respected during the computation of the event log and the result only contains the desired events.

Query

Column1 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" FILTER "e_celonis_CreateDeliveryItem"."Time" < {d '2023-03-06' } ] ) ."LEAD_OBJECT_ID"

Column2 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" FILTER "e_celonis_CreateDeliveryItem"."Time" < {d '2023-03-06' } ] ) ."ACTIVITY"

Column3 

         CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" , INCLUDE [ "e_celonis_AddSalesOrderItem" , "e_celonis_CreateDeliveryItem" FILTER "e_celonis_CreateDeliveryItem"."Time" < {d '2023-03-06' } ] ) ."TIMESTAMP"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

Column2 : string

Column3 : date

'O10'

'e_celonis_AddSalesOrderItem'

Thu Feb 02 2023 00:00:00.000

'O10'

'e_celonis_CreateDeliveryItem'

Fri Feb 03 2023 00:00:00.000

'O11'

'e_celonis_AddSalesOrderItem'

Fri Mar 03 2023 00:00:00.000

'O11'

'e_celonis_CreateDeliveryItem'

Sat Mar 04 2023 00:00:00.000

'O12'

'e_celonis_AddSalesOrderItem'

Sat Mar 04 2023 00:00:00.000

'O12'

'e_celonis_CreateDeliveryItem'

Sun Mar 05 2023 00:00:00.000

'O13'

'e_celonis_AddSalesOrderItem'

Sun Mar 05 2023 00:00:00.000

'O14'

'e_celonis_AddSalesOrderItem'

Mon Mar 06 2023 00:00:00.000

'O15'

'e_celonis_AddSalesOrderItem'

Fri Apr 07 2023 00:00:00.000