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 identifierACTIVITY
: 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:
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. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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, . | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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') | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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') | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|