Structure of transformations

Overview

The following illustration shows the transformation steps of the Process Mining app templates.

The models\ folder in the Transformations sectoion of Data transformations is organized according the structure of the transformation steps.

1. Input

The input step is used to load the raw data. The following operations are typically done to prepare the data for the next transformation steps:

• Select fields with the optional and mandatory macro. A field doesn't need to be present in the raw data when the optional macro is used.
• Type cast fields to the appropriate data types.

• Filter tables to reduce data size early in the transformations.

Note: It is recommended to reduce data size already in the extraction where possible.

Naming convention

If you expect name clashes with table names in next transformation steps, it is best practice to add the suffix _input to the input tables.

2. Objects

In the Object step, input tables are transformed to objec tables. Each object required for the expected events should get its own table. Refer to Designing an event log. Additionally, supporting object can also be defined here.

In the following example 3 input tables Invoices_input, Invoice_types_input, and Customers_input are joined together to create the object table Invoices.

Guidelines

Follow these guideline when creating an objects table.

• There is one object ID field, which is unique for each data record.
• All object fields that are needed for data analysis are present.
• All object fields have names that are easy to understand.

When applicable, the object table relates to another object via an ID field. In the following example, the invoice lines are related to the invoice object via the Invoice_ID field.

Additional transformations

Not all input tables are transformed into object tables. Also, other input tables may contain relevant information, such as the Customers table in the example. It may be convenient to define them in the Objects step as separate tables such that they can be reused in the data transformations.

Naming convention

If the object table names would lead to name clashes later on, add the suffix _base to the tables.

3. Events

Note: The input for Event log and Custom process app templates is already a well defined event log for Process Mining. There is no need to transform the data from the source system into the events for Process Mining here. This means that the 3. events is not present in the transformations for Event log and Custom process apps.

In this transformation step, event tables are created for each object. Check out Designing an event log. Each record in an event table represents one event that took place. There are two scenarios on how the data is structured:

• Timestamp fields: Fields on an object table with a timestamp for an event. For example, the Invoice_created field in an Invoices table.
• Transaction log: A list of events.

Based on how the data is structured, the transformations to create the event tables are different.

Timestamp fields

In this scenario, the values of a timestamp field must be transformed into separate records in an event table. The following example is an invoices table that contains three timestamp fields.

Each timestamp field is used to create a separate event table. For every record that the timestamp field contains a value, create a table with the Invoice ID, the name of the event (Activity), and the timestamp the event took place (Event end).

The Invoices_input table is split into Invoice_events_Create_invoice, Invoice_events_Delete_invoice, and Invoices_events_Change_invoice_price.

The separate event tables can then be merged into a single event table per object, for example Invoices_events.

Transaction log

If events are stored in a transaction log, the relevant events per object should be identified. Create a table per object and store corresponding object ID, the name of the event (Activity), and the timestamp the event took place (Event end).

In the following example, the transaction log contains events for the Purchase Order and Invoice objects.

The following fields are mandatory in an event table. All records in the event tables should contain a value for these fields.

Field	Description
Object ID	ID of the object for which the event happens. For example, the Invoice ID.
Activity	The activity describes which action took place on the object.
Event end	The event end field indicates when the specific event was finished. Ideally, this should be a datetime field, rather than a date.

Naming convention

Name the tables according to the [Activity] + _events to create one event file per activity, or [Object] + _events to create one event file per object. For example Purchase_order_created_events, Purchase_order_approved_events, or one file with all purchase order activities combined Purchase_order_events.

4. Event logs

Single object process

When the process contains one object, no additional transformations are needed in this step. The single object table and events tables are already in the correct format.

Multiple objects process

When multiple object are involved in a process, the events of all objects need to be linked to the main object that is considered the “Case” in the process. Refer to Define the event log for details. The following steps describe how to relate all events to the main object, and how to combine them a single event log.

Object relations

Create an “object-relations” table to centralize the relationships between all object. This object-relations table will contain the ID fields of the related objects.

To create the objects-relations table, join all object tables based on their ID fields:

• Start with the main object
• Join related objects to the main object with a left join.
• If objects do not relate directly to the main object, left join them to the related objectsthat are already joined to the main object.

In the following example, there are three objects: Purchase order,Invoice line, and Invoice. The Purchase order is considered the main object in the process. The Invoice line is directly linked to the Purchase order and the Invoice is linked indirectly via the Invoice line.

Object_relations as (
	select
		Purchase_orders."Purchase_order_ID"
		Invoice_lines.Invoice_line_ID
		"Invoices.Invoice_ID"
	from Purchase_orders
	left join Invoice_lines
		on Purchase_orders."Purchase_order_ID" = "Invoice_lines."Purchase_order_ID"
	left join Invoices
		on Invoice_lines."Invoice_ID" = Invoices."Invoice_ID
)

The following illsutration shows the resulting object-relations table.

Relation tables

The individual relations between the main object and each other objectsobject are stored in separate tables, using the combined information from the object relations table.

Relation_invoice_lines as (
	select
		Object_relations."Purchase_order_ID"
		Object_relations."Invoice_line_ID"
	from Object_relations
	group by "Purchase_order_ID", "Invoice_line_ID"
)

Relation_invoices as (
	select
		Object_relations."Purchase_order_ID"
		Object_relations."Invoice_ID"
	from Object_relations
	group by "Purchase_order_ID", "Invoice_ID"
)

Event log

The next step is to use these relations to add corresponding “Case ID” to each event table. The "Case ID" is obtained via the relation table, where the event information is obtained from the event table. To create the full event log, the event tables for each object are unioned.

Purchase_order_event_log as (
	select
		Purchase_order_events."Purchase_order_ID"
		Purchase_order_events."Activity"
		Purchase_order_events."Event_end"
	from Purchase_order_events
	union all
	select
		Relation_invoice_lines."Purchase_order_ID"
		Invoice_line_events."Activity"
		Invoice_line_events."Event_end"
	from Invoice_line_events
	inner join Relation_invoice_lines
		on Invoice_line_events."Invoice_line_ID" = "Relation_invoice_lines."Invoice_line_ID"
	union all
	select
		Relation_invoices."Purchase_order_ID"
		Invoice_events. "Activity"
		Invoice_events. "Event_end"
	from Invoice_events
	inner join Relation_invoices
		on Invoice_events."Invoice_line_ID" = Relation_invoices."Invoice_line_ID"
)

Naming convention

If the event log table name can lead to name clashes at a later stage, add the suffix  _base to the name of the event log tables.

5. Business logic

In the last transformation step, business logic is added as needed for data analysis. Additional derived fields can be added to existing tables here. For example, specific throughput times or Boolean fields that are used in KPIs in dashboards.

In Process Mining, there are two additional standard tables defined in this transformation step: Tags and Due dates.

Tags

Tags are properties of objects, which signify certain business rules. Tags are typically added to make it easy to analyze these business rules. For example:

• Invoice paid and approved by the same person.
• Invoice approval took more than 10 days.
• Check invoice activity skipped.

Each record in the tags table represents one tag that occurred in the data for a specific case. The mandatory fields for this table are the  Object ID and the  Tag. Not all objects will have a tag and some objects may have multiple tags. The following illustration shows an example Tags table.

Note:

Only one Tags table is allowed in the data model.

Due dates

Due dates represent deadlines in the process. These are added to the data to analyze whether activities are performed on time for these due dates or not.

Each record in the due dates table represents one due date for a certain object. Example due dates are:

• a payment deadline for a payment.
• an approval deadline for an purchase order.

The mandatory fields for this table are the Object ID, Due date, Actual date, and Expected date.

Note:

Only one Due dates table is allowed in the data model.