- Release notes
- Before you begin
- Getting started
- Integrations
- Working with process apps
- Working with dashboards and charts
- Working with process graphs
- Working with Discover process models and Import BPMN models
- Showing or hiding the menu
- Context information
- Export
- Filters
- Sending automation ideas to UiPath® Automation Hub
- Tags
- Due dates
- Compare
- Conformance checking
- Root cause analysis
- Simulating automation potential
- Starting a Task Mining project from Process Mining
- Triggering an automation from a process app
- Viewing Process data
- Creating apps
- Loading data
- Customizing process apps
- Publishing process apps
- App templates
- Additional resources
- Out-of-the-box Tags and Due dates
- Editing data transformations in a local environment
- Setting up a local test environment
- Designing an event log
- Extending the SAP Ariba extraction tool
- Performance characteristics
Editing data transformations in a local environment
It is strongly recommended to customize data transformations from within Process Mining using the Data Transformations editor. See Data transformations for more information on how to customize data transformations from within Process Mining.
This page describes how to customize data transformations outside Process Mining.
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 and filters. 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.
You can either customize the transformations from within Process Mining with Data Transformations, or you can export the transformations from Process Mining and edit and test them using a local test environment.
Editing the transformations using the Data Transformations editor in Process Mining does not require a local development environment.
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.
Permissions
You need Edit transformations permission for the process app for customizing data transformations.
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 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:
encrypt:
trust_cert:
target: default
my-app-template:
outputs:
default:
type: sqlserver
driver: 'ODBC Driver 17 for SQL Server'
server:
port:
user:
password:
database:
schema:
encrypt:
trust_cert:
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 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 Process Mining.
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 |
Import the transformations in the test process app. |
4 |
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 6: 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.
- Introduction
- Data Transformations editor
- 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