Azure Synapse SQL Transformation

Azure Synapse SQL Transformation is used in the Enterprise version of process.science for Power BI to prepare the data. The package is designed to transform the data from the Azure Synapse Workspace into a form that is suitable for Power BI. It creates four tables that can be used for analysis in Power BI.

Preparing the input table

Before the scripts can be used, you must ensure that your input table fulfils the following requirements. The column names for case_id, activity and timestamp are fixed and must be present in your input table. The other names can be changed in the script.

Field Name	Description	Data Type	Mandatory
case_id	Through this field the process instances can be differentiated.	nvarchar	✓
activity	This field describes the activity that has taken place.	nvarchar	✓
activity_grouped	This field describes the group of the activity that has taken place.	nvarchar	✗
timestamp	This field describes when an event has taken place.	datetime2	✓
timestamp_end	This field can be used to calculate the duration of activities.	datetime2	✗
sort_key	This field can be used to adjust the sorting of the event log.	numeric	✗
value	This allows you to set the costs for the current event.	numeric	✗
automated	This allows you to set wether the activity was automated or not.	numeric	✗
resource	This allows you to set user information for each activity.‚	Any	✗
Additional Activity Values	Further information per activity, e.g. quantity of items. All fields, not matching other names will be mapped as additional activity values and will be stored in the newly created event log table.	Any	✗
Additional Cases Values	Further information per case, e.g. a company division. All fields starting with case_ will be mapped as additional cases values and will be stored in the newly created cases table.	Any	✗
Additional Activity Values Grouped	Further information per activity group, e.g. quantity of items. All fields ending with _grouped will be mapped as additional activity group values and will be stored in the newly created event log table.	Any	✗

Downloading the scripts

The scripts can always be downloaded in the latest version from our Self Service Portal. The scripts are contained in a ZIP file and must be unpacked.

Installation and configuration

Customise schema

If the output schema is not dbo, you must customise the scripts to use the correct schema. To do this, replace [dbo] with the desired schema.

Creating the procedures

The first step is to create the procedures that write the data to the tables. To do this, the scripts 0_CreateHelperProcedures.sql, 1_CreateEventLogProcedure.sql, 2_CreateEventLogGroupedProcedure.sql, 3_CreateAdditionalGroupedTablesProcedure.sql , 4_CreateCasesDataProcedure.sql must be executed. This only needs to be done once.

Helper functions

The function ps_dateDiffBig is used to calculate the duration in milliseconds between two dates since Azure Synapse SQL does not support DATEDIFF_BIG at the moment.

CREATE FUNCTION dbo.ps_dateDiffBig(@startDate DATETIME2, @endDate DATETIME2)
RETURNS BIGINT

Event log

The procedure ps_createEventLogDataTable creates the table eventlog in your output schema and fills it with the data from the input table. All additional columns that do not begin with case_ or end with _grouped are stored in the eventlog table.

CREATE PROCEDURE [dbo].[ps_createEventLogTable]  
	 @SchemaName					NVARCHAR(10), 
	 @TableName						NVARCHAR(100),
	 @ActivityGroupedName			NVARCHAR(50),
	 @TimestampEndName				NVARCHAR(50),
	 @ValueName						NVARCHAR(50),
	 @AutomatedName					NVARCHAR(50),
	 @ResourceName					NVARCHAR(50),
	 @SortKeyName					NVARCHAR(50)

If the dynamic columns (timestamp_end, sort_key, value, automated and resource) are not present in the input table, they are automatically filled with default values.

Event log grouped

The procedure ps_createEventLogGroupedTable creates the table eventlog_grouped in your output schema and fills it with the data from the input table. This script is only executed if the grouped calculation is to take place, i.e. the activity_grouped column is present in the input table. All additional columns with the suffix _grouped are saved in the eventlog_grouped table.

CREATE PROCEDURE [dbo].[ps_createEventLogGroupedTable]  
	 @SchemaName					NVARCHAR(10), 
	 @TableName						NVARCHAR(100),
	 @ActivityGroupedName			NVARCHAR(50),
	 @TimestampEndName				NVARCHAR(50),
	 @ValueName						NVARCHAR(50),
	 @AutomatedName					NVARCHAR(50),
	 @ResourceName					NVARCHAR(50),
	 @SortKeyName					NVARCHAR(50),
	 @PerformGroupedCalculation 	BIT

Additional grouped tables

The procedure ps_createAdditionalGroupedTables creates the tables filter_and_grouped and filter_or_grouped. This procedure is only executed if the grouped calculation is to take place.

CREATE PROCEDURE [dbo].[ps_createAdditionalGroupedTables]

Cases table

The procedure ps_createCasesDataTable creates the table cases_data in your output schema and fills it with the data from the input table. All additional columns beginning with case_ are stored in the cases_data table.

CREATE PROCEDURE [dbo].[ps_createCasesDataTable]  
	 @SchemaName					NVARCHAR(10), 
	 @TableName						NVARCHAR(100),
	 @PerformGroupedCalculation		INT

Creating the static tables

1. open the script 5_CreateStaticTables.sql and execute it in your database.
2. the script creates the tables filter_and, filter_or and if the grouped calculation is to take place also the tables filter_and_grouped, filter_or_grouped and activity_group_map in your output schema.

This script must be executed whenever the structure of the input table changes.

Loading the data

To load the data, the script 6_PerformTransformation.sql must be executed. This script calls the procedures that write the data to the tables. In this script, the information on the input table and the dynamic columns must be adapted above.

-- **************** Define the table and column names here ********************** --

DECLARE @SchemaName						NVARCHAR(10) = 'dbo';						-- Change input schema name here
DECLARE @TableName						NVARCHAR(100)= 'EventLogP2P';			    -- Change input event log name here
DECLARE @ActivityGroupedName			NVARCHAR(50) = 'activity_grouped';			-- Change activity_grouped column name here
DECLARE @TimestampEndName				NVARCHAR(50) = 'timestamp_end';				-- Change timestamp_end column name here
DECLARE @ValueName						NVARCHAR(50) = 'value';						-- Change value column name here
DECLARE @AutomatedName					NVARCHAR(50) = 'automated';					-- Change automated column name here
DECLARE @ResourceName					NVARCHAR(50) = 'user_name';					-- Change resource column name here
DECLARE @SortKeyName					NVARCHAR(50) = 'sort_key';					-- Change sort_key column name here
DECLARE @PerformGroupedCalculation		INT;										-- DO NOT CHANGE OR REMOVE

-- **************** Define the table and column names here ********************** --

Pipeline & Scheduling

The download package also contains a ZIP file for the pipeline, which can be imported into the Synapse environment, only the database connection needs to be adjusted.

This article describes how to create an Azure Synapse Pipeline with Trigger: Azure Synapse Pipeline with Trigger.