From Raw Tables to an Event Log

A set of raw tables becomes an event log once you have found three things in it.

  1. The events. Each event is something that happened at a moment: an order placed, an invoice approved, a ticket closed. Look for columns holding a date or a timestamp. Each of them usually marks an event, and the column name tells you the activity.
  2. The case. A case is the thing the process moves through from start to end: one order, one claim, one patient visit. Choose the ID column that every event can be traced back to.
  3. The order. The timestamp puts the events of a case in order. Where two events share a timestamp, a sequence column, if there is one, breaks the tie.

A worked example

The Order Management (OCEL 2.0) data set includes a SQLite database. It has a table of events, a table of objects, a table linking each event to the objects it touched, and one table per activity holding its timestamps. Pick orders as the case, and this query returns the flat event log for orders:

SELECT eo.ocel_object_id AS case_id,
       e.ocel_type       AS activity,
       t.ocel_time       AS timestamp
FROM event e
JOIN event_object eo ON eo.ocel_event_id = e.ocel_id
JOIN object o        ON o.ocel_id = eo.ocel_object_id AND o.ocel_type = 'orders'
JOIN (SELECT ocel_id, ocel_time FROM event_placeorder
      UNION ALL SELECT ocel_id, ocel_time FROM event_confirmorder
      UNION ALL SELECT ocel_id, ocel_time FROM event_payorder
      UNION ALL SELECT ocel_id, ocel_time FROM event_paymentreminder) t ON t.ocel_id = e.ocel_id
ORDER BY case_id, timestamp;

It returns 6,566 events for 2,000 orders, across four activities: place order, confirm order, pay order and payment reminder.

case_id    activity       timestamp
o-990001   place order    2023-04-03T10:08:18+00:00
o-990001   confirm order  2023-04-04T14:34:29+00:00
o-990001   pay order      2023-04-05T15:47:04+00:00
o-990002   place order    2023-04-03T14:36:55+00:00

In mindzie

Load the tables as raw data and choose what one case is. See Loading Data and Choosing What a Case Is.

Where to find raw tables

Raw Tables and Databases lists every data set that comes as tables.