ETL for Process Mining

ETL for Process Mining

Process mining depends on data, which originates from various systems, making ETL a critical component. ETL, short for Extract, Transform, Load, is a data warehousing process that extracts data from source systems, transforms it as needed, and loads it into a data warehouse or process mining tool. This process is essential for collecting, cleaning, organizing, and preparing data for analysis.

Here’s a guide to performing ETL for process mining effectively.

Global Approach

The most important rule: don’t rush into extracting data. Data extraction is both costly and time-consuming.

Start by defining your project goals and identifying the processes you want to analyze. Choose one process to begin with and create a quick outline using a BPMN model. Add data to the model to align with your project goals. Begin with readily available data, such as Excel files, easily exportable data, or data already used for other analytics. Next, identify any data gaps and extract only the data necessary to achieve your goals. Resist the urge to gather all data ‘just in case’—excess data will slow you down. The speed of your continuous improvement cycle often depends more on data gathering than on implementation.

Start with simple file uploads. Automate data loading only when it makes sense, such as when data is frequently updated and continuous analysis is required. In many cases, static analysis is preferred for stability. Whatever approach you choose, don’t let it slow you down. It’s better to upload data quarterly in a few minutes than to spend weeks automating, only to discover the data is incorrect or insufficient for your business case.

What Data is Needed?

Process mining requires specific data: a case ID, a timestamp, and an activity. Additional data, such as cost, user, team, or CO2 footprint, can enhance your analysis. You can also include extra dimensions for charts or additional measures for metrics.

Obtaining some process mining data is usually straightforward, as the required fields are common. However, creating a single dataset with all the necessary data can be challenging, often requiring significant transformations to combine and unify separate pieces into one file.

Don’t worry about having everything at once—start with what you have.

What Data Format is Needed?

Although advanced data formats exist, most tools still rely on simple text files. Use comma-separated (CSV) or tab-separated (TSV/TXT) files. Avoid fixed-width text files, as most tools cannot process them.

Files should begin with a header row, followed by data rows that match the header’s fields and order.

If you need non-English characters, use UTF-8 encoding. Ensure fields do not contain separators or end-of-line characters. You can use quotes around fields, but avoid quotes within fields. If necessary, replace quotes with another character to simplify processing.

Readily Available Data

Start by listing easily accessible data. Consider these sources:

• Monthly or weekly Excel reports with raw data. Use Excel to reformat if needed.
• Process mining data from other tools, often requiring no additional preprocessing.
• Standard export options from systems like HR, financial, or ITSM systems. Export to a format your process mining tool supports.
• Exports from analytical tools reporting on required data. Use pivot tables and exports to create the right format.
• Data warehouses with cleaned and combined data. Use warehouse tools to select and export data as CSV.

Process Systems

Data is often stored in systems like SAP, Workday, Salesforce, or ServiceNow. First, check if a simple export meets your needs, as this is the fastest way to create value. If not, use ETL tools to extract, transform, and load data into your process mining tool.

Depending on your organization, you may need to involve IT, system owners, or data warehousing teams. While this can slow down data gathering, don’t bypass these teams—they have procedures and experience that can speed up the process. Work in an agile loop with them, starting with easily available data and avoiding requests for everything at once, which can cause delays.

Initially, request data in text format. Later, automate using your process mining tool’s API or built-in ETL tools.

Built-in ETL Tools in Process Mining Tools

We generally advise against using built-in ETL tools from process mining vendors. While they may seem convenient, they have significant limitations:

• Lower quality compared to dedicated ETL tools.
• Use of proprietary technology instead of industry standards like SQL, increasing training needs and reducing expertise availability.
• Vendor lock-in, making it harder to switch tools.
• Creation of data silos, limiting data reuse in other analytics or AI projects.

Third-Party ETL Tools

Many third-party ETL tools can handle process mining needs. While process mining requires specific data, the operations are standard.

Prefer SQL-based tools for easier reuse of ETL logic and better long-term maintainability. Use in-house tools to avoid delays or project blocks caused by adopting new tools.

Common third-party ETL tools for process mining:

• CData: Excellent for extraction, often used with other tools.
• dbt: A SQL-based transformation tool with features for handling large transformations.
• BigQuery: A managed data warehouse by Google, ideal for fast SQL queries on large datasets.
• Snowflake: A cloud-based platform for scalable storage and computing, used for transformation and analysis.
• DataBricks: A unified analytics platform combining data engineering, machine learning, and analytics.
• Talend: A graphical ETL tool supporting various data sources.
• Apache Nifi: An open-source ETL tool for data flow automation and real-time processing.

Specialized ETL Tools for Process Mining

Specialized ETL tools for process mining combine third-party ETL advantages with process mining features and templates.

Examples:

• Evidant: Data Transform Refinery. Focuses on process mining data extraction and transformation for large volumes of data.
• Konekti: Designed for creating process data models accurately and quickly.

Takeaway

ETL is not the goal of process mining projects but often a necessary step. Set up your ETL process to avoid delays:

• Use readily available data.
• Start with manual uploads; automate when appropriate.
• Use existing tools, preferring SQL.

Most importantly, start small with the data you need and expand gradually. Avoid gathering all data upfront, as this can derail your project.