Azure Synapse SQL Transformation

Die Azure Synapse SQL Transformation wird in der Enterprise Version von process.science für Power BI zur Aufbereitung der Daten verwendet. Das Paket ist so konzipiert, dass es die Daten aus dem Azure Synapse Workspace in eine Form bringt, die für Power BI geeignet ist. Es erstellt vier Tabellen, die für die Analyse in Power BI verwendet werden können.

Vorbereiten der Eingabetabelle

Bevor die Skripte verwendet werden können, müssen Sie sicherstellen, dass Ihre Eingabetabelle die folgenden Anforderungen erfüllt. Die Spaltennamen für case_id, activity und timestamp sind festgelegt und müssen in Ihrer Eingabetabelle vorhanden sein. Die anderen Namen können im Skript geändert werden.

Feldname	Beschreibung	Datentyp	Pflichtfeld
case_id	Über dieses Feld können die Prozessinstanzen unterschieden werden.	nvarchar	✓
activity	Dieses Feld beschreibt die Aktivität, die stattgefunden hat.	nvarchar	✓
activity_grouped	Dieses Feld beschreibt die Gruppe der Aktivität, die stattgefunden hat.	nvarchar	✗
timestamp	Dieses Feld beschreibt, wann ein Ereignis stattgefunden hat.	datetime2	✓
timestamp_end	Dieses Feld kann verwendet werden, um die Dauer von Aktivitäten zu berechnen.	datetime2	✗
sort_key	Dieses Feld kann verwendet werden, um die Sortierung des Ereignisprotokolls anzupassen.	numeric	✗
value	Hier können Sie die Kosten für das aktuelle Ereignis festlegen.	numeric	✗
automated	Hier können Sie festlegen, ob der Vorgang automatisiert wurde oder nicht.	numeric	✗
resource	Über dieses Feld können Nutzerinformationen pro Aktivität mitgegeben werden.	Beliebig	✗
Zusätzliche Aktivitätswerte	Weitere Informationen pro Aktivität, z. B. die Menge der Positionen. Alle Felder, die nicht mit anderen Namen übereinstimmen, werden als zusätzliche Aktivitätswerte abgebildet und in der neu erstellten Ereignisprotokolltabelle gespeichert.	Beliebig	✗
Additional Cases Values	Weitere Informationen pro Fall, z.B. ein Unternehmensbereich. Alle Felder, die mit case_ beginnen, werden als zusätzliche Fallwerte abgebildet und in der neu erstellten Falltabelle gespeichert.	Beliebig	✗
Zusätzliche Aktivitätswerte gruppiert	Weitere Informationen pro Aktivitätsgruppe, z. B. die Menge der Positionen. Alle Felder, die mit _grouped enden, werden als zusätzliche Aktivitätsgruppenwerte abgebildet und in der neu erstellten Ereignisprotokolltabelle gespeichert.	Beliebig	✗

Herunterladen der Skripte

Die Skripte können immer in der aktuellsten Version aus unserem Self Service Portal heruntergeladen werden. Die Skripte sind in einer ZIP-Datei enthalten und müssen entpackt werden.

Installation und Konfiguration

Schema anpassen

Wenn das Ausgabeschema nicht dbo ist, müssen Sie die Skripte anpassen, um das richtige Schema zu verwenden. Ersetzen Sie dafür [dbo] durch das gewünschte Schema.

Erstellen der Prozeduren

Der erste Schritt besteht darin, die Prozeduren zu erstellen, die die Daten in die Tabellen schreiben. Dazu müssen die Skripte 0_CreateHelperProcedures.sql, 1_CreateEventLogProcedure.sql, 2_CreateEventLogGroupedProcedure.sql, 3_CreateAdditionalGroupedTablesProcedure.sql , 4_CreateCasesDataProcedure.sql ausgeführt werden. Dies muss nur einmal durchgeführt werden.

Hilfsfunktionen

Die Funktion ps_dateDiffBig wird verwendet, um die Dauer in Millisekunden zwischen zwei Datumsangaben zu berechnen, da Azure Synapse SQL im Moment DATEDIFF_BIG nicht unterstützt.

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

Ereignisprotokoll (Event Log)

Die Prozedur ps_createEventLogDataTable erstellt die Tabelle eventlog in Ihrem Ausgabeschema und füllt sie mit den Daten aus der Eingabetabelle. Alle zusätzlichen Spalten, die nicht mit case_ anfangen oder mit _grouped enden, werden in der Tabelle eventlog gespeichert.

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)

Sind die dynamischen Spalten (timestamp_end, sort_key, value, automated und resource) nicht in der Eingabetabelle vorhanden, werden sie automatisch mit Standardwerten befüllt.

Ereignisprotokoll gruppiert (Event Log Grouped)

Die Prozedur ps_createEventLogGroupedTable erstellt die Tabelle eventlog_grouped in Ihrem Ausgabeschema und füllt sie mit den Daten aus der Eingabetabelle. Dieses Skript wird nur ausgeführt, wenn die gruppierte Berechnung stattfinden soll, also die Spalte activity_grouped in der Eingabetabelle vorhanden ist. Alle zusätzlichen Spalten mit dem Suffix _grouped werden in der Tabelle eventlog_grouped gespeichert.

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

Zusätzliche gruppierte Tabellen (Additional Grouped Tables)

Die Prozedur ps_createAdditionalGroupedTables erstellt die Tabellen filter_and_grouped und filter_or_grouped. Diese Prozedur wird nur ausgeführt, wenn die gruppierte Berechnung stattfinden soll.

CREATE PROCEDURE [dbo].[ps_createAdditionalGroupedTables]

Falltabelle (Cases Data)

Die Prozedur ps_createCasesDataTable erstellt die Tabelle cases_data in Ihrem Ausgabeschema und füllt sie mit den Daten aus der Eingabetabelle. Alle zusätzlichen Spalten, die mit case_ beginnen, werden in der Tabelle cases_data gespeichert.

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

Erstellen der statischen Tabellen

1. Öffnen Sie das Skript 5_CreateStaticTables.sql und führen Sie es in Ihrer Datenbank aus.
2. Das Skript erstellt die Tabellen filter_and, filter_or und wenn die gruppierte Berechnung stattfinden soll auch die Tabellen filter_and_grouped, filter_or_grouped und activity_group_map in Ihrem Ausgabeschema.

Dieses Skript muss immer dann ausgeführt werden, wenn sich die Struktur der Eingabetabelle ändert.

Laden der Daten

Um die Daten zu laden muss das Skript 6_PerformTransformation.sql ausgeführt werden. Dieses Skript ruft die Prozeduren auf, die die Daten in die Tabellen schreiben. In diesem Skript müssen oben die Informationen zur Eingabetabelle und den dynamischen Spalten angepasst werden.

-- **************** 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

Das Download-Paket enthält auch eine ZIP-Datei für die Pipeline, die in die Synapse-Umgebung importiert werden kann, lediglich die Datenbankverbindung muss angepasst werden.

In diesem Artikel wird beschrieben, wie eine Azure Synapse Pipeline mit Trigger erstellt wird: Azure Synapse Pipeline mit Trigger.