- Release notes
- Before you begin
- Getting started
- Integrations
- Managing access
- Working with process apps
- Creating apps
- Loading data
- Uploading data
- Retrieving the SQL Server database parameters
- Setting up a SQL Server account for data upload using an extractor
- Loading data using Theobald Xtract Universal
- Customizing process apps
- Data transformations
- TemplateOne app template
- Purchase to Pay app template
- Order to Cash app template
- Basic troubleshooting guide
Structure of transformations
Below is an overview of the transformation steps of the Process Mining app templates.
models\ folder is organized according the structure of the transformation steps.
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.
In the entities step, input tables are transformed to entity tables. Each entity required for the expected events should get its own table. See Designing an event log. Additionally, supporting entities can also be defined here.
Invoices_input, Invoice_types_input, and Customers_input are joined together to create the entity table Invoices.
Follow these guideline when creating an entity table.
- There is one entity ID field, which is unique for each data record.
- All entity fields that are needed for data analysis are present.
- All entity fields have names that are easy to understand.
Invoice_ID field.
Not all input tables are transformed into entity 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 entities step as separate tables such that they can be reused in the data transformations.
3. events is not present in the transformations for TemplateOne-SingleFile and TemplateOne-MultiFiles process apps.
In this transformation step, event tables are created for each entity. See 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 entity table with a timestamp for an event. For example, the
Invoice_createdfield in anInvoicestable. - Transaction log: A list of events.
Based on how the data is structured, the transformations to create the event tables are different.
In this scenario, the values of a timestamp field must be transformed into separate records in an event table. The below 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).
Invoices_input table is split into Invoice_events_Create_invoice, Invoice_events_Delete_invoice, and Invoices_events_Change_invoice_price.
Invoices_events.
If events are stored in a transaction log, the relevant events per entity should be identified. Create a table per entity and store corresponding entity ID, the name of the event (Activity), and the timestamp the event took place (Event end).
In the below example, the transaction log contains events for the Purchase Order and Invoice entities.
The following fields are mandatory in an event table. All records in the event tables should contain a value for these fields.
|
Field |
Description |
|---|---|
|
Entity ID |
ID of the entity for which the event happens. For example, the Invoice ID. |
|
Activity |
The activity describes which action took place on the entity. |
|
Event end |
The event end field indicates when the specific event was finished. Ideally, this should be a datetime field, rather than a date. |
When the process contains one entity, no additional transformations are needed in this step. The single entity table and events tables are already in the correct format.
When multiple entities are involved in a process, the events of all entities need to be linked to the main entity that is considered the “Case” in the process. See Define the event log. The below steps describe how to relate all events to the main entity, and how to combine them a single event log.
Create an “entity-relations” table to centralize the relationships between all entities. This entity-relations table will contain the ID fields of the related entities.
To create the entity-relations table, join all entity tables based on their ID fields:
- Start with the main entity
- Join related entities to the main entity with a left join.
- If entities do not relate directly to the main entity, left join them to the related entities that are already joined to the main entity.
In the below example, there are three entities: Purchase order,Invoice line, and Invoice. The Purchase order is considered the main entity in the process. The Invoice line is directly linked to the Purchase order and the Invoice is linked indirectly via the Invoice line.
Below is the resulting entity-relations table.
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.
Tags and Due dates.
Tags are properties of cases, 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 "Case ID" and the "Tag". Not all cases will have a tag and some cases may have multiple tags. Below is an example Tags table.
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 event. Example due dates are:
- a payment deadline for a payment event.
- an approval deadline for an approval event.
Event ID, Due date, Actual date, and Expected date.
Not all events will have a due date and some events may have multiple due dates.
