Event Ordering for Identical Timestamps

This article contains information how to define order of events correctly in situations where there are identical event timestamps within same cases. In QPR ProcessAnalyzer, the order of events in those situations are based on the order rows in the eventlog data.

Introduction

The order in which events occur within cases is important in process mining, because the order is visualized for example in flowcharts. Also the order affects to which variation the case belongs to. Mostly the order of events comes from event timestamps which are always available in the eventlog data. There is still an exception, when there are events that have the same timestamp within a same case, because the eventlog data cannot reveal the order in which the events occurred. In many eventlogs, events with exactly identical timestamps is quite rare, but the situation may occur quite often in following circumstances:

• The origin system creates several events during a single processing and records all the events using the same timestamp.
• Accurate timestamps are not available (e.g. in millisecond level), but for example only the date of occurrence is available. In this situation, all events that occurred within the same day, have the same recorded timestamp.

If the order of events is known, it's possible to adjust the order in QPR ProcessAnalyzer as explained below.

Event Order in QPR ProcessAnalyzer

In QPR ProcessAnalyzer, ordering of events having identical timestamps within the same case is based on the order of rows in the eventlog data. In practice, if the model uses a datatable for events, the order of rows in the datatable determines the order of events. Thus, if the ordering is a concern, make sure to order the data as desired in the datatable, when new data is imported to the datatable.

This same logic also applies for models, that get their data from an ODBC datasource or from a loading script.

Eventlog Sorting Example

This example script sorts rows in an events datatable based on the defined rules: event order starting from the first is Delivery planned, Invoice sent, Payment received. Other event types are equal and thus they are not reordered. The script reads the events datatable to a temporary table to the scripting database, and writes the data back to the datatable after sorting it. Each sorted event type name are mapped to an integer value which is used as the basis for sorting. The data is sorted primarily based on the case id and secondarily based on the event timestamp, and only if they are equal, the defined event ordering takes effect as the third sorting criteria.

(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'ProjectName', 'MyProject') UNION ALL
(SELECT 'DataTableName', 'MyModel events') UNION ALL 
(SELECT 'TargetTable', '#EventsTemp')
(SELECT 'MaximumCount', '0') UNION ALL
--#GetAnalysis

(SELECT 'ProjectName', 'MyProject') UNION ALL
(SELECT 'DataTableName', 'MyModel events') UNION ALL
(SELECT 'Append', '0');

SELECT *
FROM #EventsTemp
ORDER BY
[CaseId],
[EventTimestamp],
(CASE [EventType]   
WHEN 'Delivery planned' THEN 1
WHEN 'Invoice sent' THEN 2
WHEN 'Payment received' THEN 3
ELSE 4 END)
--#ImportDataTable