- 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
- Editing data transformations in a local environment
- Setting up a local test environment
- Editing transformations
- Designing an event log
- Structure of transformations
- Tips for writing SQL
- TemplateOne app template
- Purchase to Pay app template
- Order to Cash app template
- Basic troubleshooting guide
Editing data transformations in a local environment
When you create a process app from an app template, you want to make sure the data used in the process app reflects your business process in the correct way. With Data transformations you can customize the data used for Process Mining.
Data transformations enable you to:
- add new fields for
group by
andfilters
. For example, Quality check type. - add events. For example, Invoice quality check done.
- add tags. For example, Failed to meet SLA,Four eyes violation.
- add business logic for calculating KPI's. For example,
Case_has_maverick_buying
. - define new input fields for calculations.
When starting editing data transformations it is strongly recommended that you:
- have in-depth knowledge of SQL;
- are familiar with the source system that is used for data extraction.
Tools
A code editor is required for editing data transformations. Visual Studio Code is the recommended code editor for editing data transformations. You can download Visual Studio Code from Download Visual Studio Code webpage.
It is recommended to run and test the data transformations outside Process Mining, before you import the edited transformation in your process app. To run and test the data transformations, a local test environment is required.
Each process app has a corresponding set data transformations. Customizing data transformations requires several steps to be performed.
-
Export the transformations from the process app.
-
Open the transformations in Visual Studio Code.
-
Run the transformations*.
-
Edit the transformations.
-
Test the transformations in a local test environment.*
-
Import and test the transformations in a test process app in Process Mining.
-
Import the transformations in the process app.
* Running and testing the transformations on a local test environment are optional steps.
Follow these steps to export the transformations from the process app.
Step |
Action |
---|---|
1 |
Open Process Mining portal. |
2 |
Go to the Process apps tab and locate the app for which you want to export the transformations. |
3 |
Click on in the Actions column of the process app for which you want to export the transformations and select Export transformations. |
.zip
file.
Follow these steps to open the transformations in Visual Studio Code.
Step |
Actions |
---|---|
1 |
In Windows Explorer, create a folder for the exported transformations in the folder where your virtual environment is located. For example,
C:\My_transformations\TemplateOne-MultiFiles .
|
2 |
Unzip the exported transformations
.zip file in the folder.
|
See the illustration below for an example.
Step |
Action |
---|---|
3 |
In Visual Studio Code, go to File -> Open Folder... and select the folder that contains the unzipped transformations. |
See the illustration below for an example.
The dbt project is interpreted.
dbt_project.yml
to the format required by SQL Server. Before you create the .zip
file with the new transformations, you must change the date and time formats back to the format required by Snowflake.
dbt_project.yml
file, the default values are used.
For SQL Server date and time formats are defined by integers and for Snowflake defined by strings.
Below is an overview of the different (default) formats for SQL Server and Snowflake.
variable |
SQL Server format |
Snowflake format |
---|---|---|
|
23 |
'YYYY-MM-DD' |
|
14 |
'hh24:mi:ss.ff3' |
|
21 |
'YYYY-MM-DD hh24:mi:ss.ff3' |
See also:
-
The officical Snowflake documentation on Date and Time Formats in Conversions using Snowflake.
-
The official Microsoft SQL Server documentation on Date and Time Conversions using SQL Server.
It is good practice to run the transformations before you start editing the transformations. This enables you to check whether the set up is correct and the data is loaded correctly.
To run the transformations you must:
-
create a new database schema for the process app. You can do this in Microsoft SQL Server Management Studio. Follow these steps.
Step
Action
1
In the Object Explorer in Microsoft SQL Server Management Studio, go the folder of your database in the Databases folder.
2
Right-click on Security and select New -> Schema...
See the illustration below.
See the official Microsoft documentation for more information.
-
load data in the new schema and make sure that the required input tables are available in the schema. You can either load data using an extractor load data directly from
.csv
files in Microsoft SQL Server Management Studio. Follow these stepsStep
Action
1
In the Object Explorer in Microsoft SQL Server Management Studio, locate your database in the Databases folder.
2
Right-click on the name of your database and select Tasks -> Import Flat File...
Note: It is recommended to use a small dataset for testing data transformations. The lower the record count of the dataset, the faster transformations will be executed.
When you open a dbt project in Visual Studio Code for the first time, a dbt message is displayed, indicating that the profile corresponding to your new transformations cannot be found. See the illustration below for an example.
profiles.yml
file that is connected to your Microsoft SQL Server database. For new transformations, you can extend the profiles.yml
file by adding a new entry.
profiles.yml
file, or click the link to download the template: profiles.yml.
my-app-template:
outputs:
default:
type: sqlserver
driver: 'ODBC Driver 17 for SQL Server'
server:
port:
user:
password:
database:
schema:
target: default
my-app-template:
outputs:
default:
type: sqlserver
driver: 'ODBC Driver 17 for SQL Server'
server:
port:
user:
password:
database:
schema:
target: default
Step |
Action |
---|---|
1 |
Open a text editor. For example Notepad++. |
2 |
Copy and paste the content from the template above. |
3 |
Replace
my-app-template with the profile name as displayed in the dbt_project.yml .
For example
uipathTemplateOne . See the illustration below.
|
3 |
Edit the file, such that it points to the schema that was just created. |
4 |
Save the
profiles.yml file in the folder that does not contain a dbt project (see screenshot below).
For example,
C:\My_transformations .
|
5 |
Create an environment variable called DBT_PROFILES_DIR with the file path of the folder that contains the
profiles.yml file.
|
schema_sources
variable.
See the official dbt documentation for more information on how to configure your profile.
For guidelines on editing transformation see Editing transformations and Tips for writing SQL.
Follow these steps to test the transformations.
Step |
Action |
---|---|
1 |
Enter
dbt build at the command prompt in the integrated terminal in Visual Studio Code.
|
2 |
Check the data in SQL Server Management Studio to verify the values.
|
Edit and test the transformations on a local test environment until the transformations run without any errors and the data is correct.
It is strongly recommended to run the new transformations in a test process app in Process Mining before importing the transformations in the original process app. Since the transformations are tested on a local SQL Server environment, errors may occur when running the new transformations in Snowflake.
See also 4. Edit the transformations.
dbt_project.yml
to to test the transformations SQL Server you must change the date and time formats back to the format required by Snowflake,
before you create the .zip
file with the new transformations.
Follow these steps to import and test the transformations in a test process app in Process Mining.
Step |
Action |
---|---|
1 |
In Windows Explorer, go to folder where your transformations are stored For example,
C:\My_transformations\TemplateOne-MultiFiles and add the transformations to a new .zip file.
|
2 |
Go to the Process Mining portal, and create a new process app based on the same app template as your process app. |
3 |
On the Process apps tab and locate the test process app. |
4 |
Click on in the Actions column of the process app for which you want to import the transformations and select Import transformations. |
5 |
Select the
.zip file that contains your new transformations, and click on OK.
|
6 |
Check the dashboards to see if the data is presented correctly. |
When the transformations are imported the new transformations are automatically run. This will immediately affect the data displayed in the published process app. It is strongly recommended to test the new transformations in a separate test process app to prevent any errors (see Step 4: Import and test the transformations in a test process app in Process Mining).
Running an erroneous transformation will result in the published app not being visible for end users.
Follow these steps to import the customized transformations in the process app.
Step |
Action |
---|---|
1 |
Open the Process Mining portal. |
2 |
Go to the Process apps tab and locate the app for which you want to import the data transformations. |
3 |
Note: If you have added new input tables or new input fields for existing tables to the data transformations, you must upload a
new dataset before importing the data transformations.
Click on in the Actions column of the process app for which you want to import the transformations and select Import transformations. |
The transformations are imported and and run, and the new data is displayed in the process app.
- Introduction
- Versioning transformations
- Prerequisites
- Customizing the transformations
- 1. Export the transformations from the process app
- 2. Open the transformations in Visual Studio Code
- Date and time formats
- 3. Run the transformations
- Before you begin
- Set up Profiles.yml
- Running the transformations
- 4. Edit the transformations
- 5. Test the transformations on a local test environment
- 6. Import and test the transformations in a test process app in Process Mining
- 7. Import the transformations in the process app