The event log describes the events of the end-to-end process. Creating an event log is only needed when the process contains more than one entity. Only one entity in the process can function as the main entity which will be tracked throughout the process. This main entity is named the case in the process. Each record in the event log table represents one event for a certain case.
To illustrate the difference between the events tables as described in 3. Events and the event log table, consider the following example from an Order-to-Cash process with two entities: Sales order and Delivery. One sales order (SO1) is linked to two deliveries (D1 and D2).
For the sales order and the delivery entity there are two separate event tables, which contain the following information:
Each of these event tables describes the events per entity. The event log table should describe the events for the main entity while taking into account the related entities. When taking the Delivery entity as the main entity, the following event log table can be created.
A simple union of the separate event tables would not result in the correct
Case ID for all events. Note the Create SO event for SO1. The Create SO event happens only once in the process, but since Delivery is the main entity, the event occurs twice in the event log.
To create the end-to-end event log based on the separate event tables, it is needed to know how the entities in the process relate to each other. This information can be stored in the entity relation table. The entity relation table helps to centralize the relationships between all entities.
The information to link entities together should be available in the entity tables. Continuing the example of sales order and deliveries, that means that the delivery should have the property to which sales order it relates to. The entity relation table holds all the relationships between instances. This can be structured in the following way:
Each column represents one entity in the process. Each row contains a unique combination of entity IDs that indicate that they are related. Because multiple deliveries can be related to one sales order, the sales order SO1 occurs in multiple records of this table.
The entity relation table can be created by joining the entity tables.
- A full join is used for the main entities that can function as the case. The full join ensures that the instances of the main entity are included in the entity relations, also when the instance has no link to others.
- A left join is used for the other entities that will not be cases.
Having more than one main entity in the entity relation table is only needed when a connector is created that allows for multiple perspectives. For example, a Purchase-to-Pay process where either the purchase order can be set as the case or, alternatively, the invoice.
Only one entity can be the main entity at the same time in an event log.
An entity relation table is created to link the following entities:
- sales order,
- delivery cancellation,
The sales order indicates the start of the process and has no reference to another entity. The three tables below show the other three entity tables that contain a reference to another entity. Other properties of these entities are not considered in this example.
The following information is stored in these table:
- Both deliveries D1 and D2 are linked to sales order SO1.
- Delivery cancellation C1 cancels delivery D1.
- Invoices I1 and I2 are created for delivery D1, invoice I3 is created for delivery D2, and invoice I4 has no link to a delivery.
Either the sales order, delivery, or invoice entity can be set as a case in the process. The delivery cancellation does not have to be a possible case in this process.
For this process, the entities can be joined as displayed below.
Sales_order full outer join Delivery on Sales_order."Sales_order_ID" = Delivery."Sales_order_ID" left join Delivery_cancellation on Delivery."Delivery_ID" = Delivery_cancellation."Delivery_ID" full outer join Invoice on Delivery. "Delivery_ID" = Invoice."Delivery_ID"
The use of the different join types is shown. A left join is used for the delivery cancellation, since in this process, this entity cannot be a case ID. All other entities are joined with a full join. The table below shows the results of joining the entities.
For every instance, the table contains information on how it relates to other instances. Consider having the delivery as the case, then for:
- delivery D1, sales order SO1, delivery cancellation C1 and invoices I1 and I2 are related.
- delivery D2, sales order SO1 and invoice I3 are related.
The entity relation table is used to create the event log table. For this, you must first define which entity is the main entity. Together with a process expert, you need to define what this main entity in the process is. The identifier of that entity will function as the case ID of your process.
Consider the example where the delivery is defined as the case. Here, two records of the entity relation table contain the same information with respect to the sales order relation: the first two records indicate that sales order SO1 is linked to case D1.
When generating events based on this information, you do not want to have twice all the events for SO1. You want to create an event log table in whcih events of each instance are created once per case ID.
When generating the events for case D1, events are generated once for SO1, D1, C1, I1, and I2. For case D2, events are generated once for SO1, D2, and, I3. Note that for SO1 events are generated twice, but only once per case ID. Invoice I4 is not linked to any delivery and therefore events will not be generated. See the illustration below.
To illustrate the creation of the event log table, consider the example that sales order SO1 is linked to two deliveries D1 and D2. The delivery is defined as the main entity in the process. The entity relation table looks in that scenario as displayed below.
Below are the event tables for the two entities.
Each event table is joined with the entity relation table on the entity ID. It is important to make sure to join on the entity relation table where once the entity ID is present per case. These joins are then unioned together to create the complete event log. In SQL statements, this would look as follows:
select Entity_relations_delivery."Delivery_ID" as "Case_ID", Delivery_events."Activity", Delivery_events."Event_end", from Delivery_events inner join (select distinct "Delivery_ID" from Entity_relations) as Entity_relations_delivery on Delivery_events."Delivery_ID"= Entity_relations.delivery."Delivery_ID" union all select Entity_relations_sales_order."Delivery_ID" as "Case_ID", Sales_order_events."Activity", Sales_order_events."Event_end", from Sales_order_events inner join (select distinct "Delivery_ID", "Sales_order_ID" from Entity_relations) as Entity_relations_sales_order on Sales_order_events."Sales_order_ID"= Entity_relations.delivery."Delivery_ID
Since the delivery is the main entity, the delivery ID column is renamed to case ID. Also, the other two mandatory attributes activity and event end are defined. The ‘select distinct’ construction in the join prevents duplicate events in the event log.
The result of this union will be the event log table as displayed below.
Updated 3 months ago