Editing data transformations in a local environment

Introduction

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.

Versioning transformations

Currently, there is no versioning of transformation code in the product. When new transformations are imported, the previous ones get overwritten. It is not possible to revert to the previous version of the transformations.

Note: It is recommended to keep local copies of previous versions of the code when making changes. To track changes, transformations on your local machine can be versioned in a versioning system like GIT or SVN. That way, you can roll back to a previous version using the versioning system, and Import those transformations into the product when needed.

Prerequisites

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.

See Setting up a local test environment.

Customizing the transformations

Each process app has a corresponding set data transformations. Customizing data transformations requires several steps to be performed.

1. Export the transformations from the process app.

2. Open the transformations in Visual Studio Code.

3. Run the transformations*.

4. Edit the transformations.

5. Test the transformations in 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.

* Running and testing the transformations on a local test environment are optional steps.

1. Export the transformations from the process app

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.

The transformations are exported and downloaded to your default download folder as a .zip file.

2. Open the transformations in Visual Studio Code

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.

Date and time formats

Important:

Snowflake uses different date and time formats than SQL Server. If date and time variables are defined for the transformations for your process app, you must change the date and time formats in 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.

If the date and time formats are not defined in your 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
date_format	23	'YYYY-MM-DD'
time_format	14	'hh24:mi:ss.ff3'
datetime_format	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.

3. Run the transformations

Important: Running the transformations is only applicable if you want to test the transformations outside Process Mining on a local test environment.

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.

Before you begin

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 steps

  Step	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.

Set up Profiles.yml

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.

Note: If you start editing transformations, you must first set up a 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.

Below is a template for the 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

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.

Note: By default, the source data is stored in the schema where the transformations run. If your source tables are in a different schema, you can define this schema in the schema_sources variable.

See the official dbt documentation for more information on how to configure your profile.

Running the transformations

Follow these steps to run the transformations.

Step	Action
1	In Visual Studio Code, right-click on the models folder and select Open in Integrated Terminal from the context menu.
2	Enter dbt build at the command prompt in the integrated terminal.

4. Edit the transformations

For guidelines on editing transformation see Editing transformations and Tips for writing SQL.

5. Test the transformations on a local test environment

Important: Test the transformations is only applicable if you want to test the transformations outside Process Mining on a local test environment.

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. Right-click on a table and select Select top 1000 rows. If necessary, modify the query to get the desired results.

Edit and test the transformations on a local test environment until the transformations run without any errors and the data is correct.

6. Import and test the transformations in a test process app in Process Mining

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.

Important: Snowflake uses a different date and time formats than SQL Server. If you have changed the date and time formats in 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.

7. Import the transformations in the process app

Important:

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.