In Projekten trifft man häufig auf Bewegungsdaten, die mehrere Millionen Datensätze enthalten. Ein übliches Verfahren besteht darin, die daraus resultierende Faktentabelle nach Jahren zu partitionieren. In DeltaMaster gibt es die Möglichkeit, über einen SQL-Durchgriff auf die relationalen Daten der Faktentabelle zuzugreifen. Um auch auf Daten in verschiedenen Partitionen zugreifen zu können, muss eine sogenannte V_SEC-View angelegt werden, die die partitionierten Daten wieder vereinigt. Dieser Blogbeitrag zeigt, wie die V_SEC-View in eine partitionierte View konvertiert werden kann, um die Performance des SQL-Durchgriffs zu verbessern.

Partitionierte View

Im Folgenden wird die Definition einer Partitionierten View erläutert, anschließend wird die Vorgehensweise dargestellt, um diesen Ansatz auf eine V_SEC-View anzuwenden.

Eine partitionierte View ist eine Abfrage, die Daten aus mehreren Tabellen mit gleicher Struktur beinhaltet. Die Daten sind durch eine UNION ALL-Anweisung verbunden. Darüber hinaus muss die View eine Partitionierungsspalte enthalten, wie zum Beispiel das Jahr. Die enthaltenen Werte in dieser Spalte dürfen nur in jeweils einer der Quelltabellen vorhanden sein, um eine eindeutige Identifikation der Daten sicherzustellen. Als Nächstes muss ein CHECK-Constraint für die Partitionierungsspalte definiert werden. Die Partitionierungsspalte muss Teil des Primary-Key sein. Eine Abfrage auf die partitionierte View mit einer WHERE-Bedingung, in der auf die Partitionierungsspalte gefiltert wird, ermöglicht den Zugriff auf nur die Partition oder Partitionen, die die gewünschten Daten enthalten.

Szenario

Im folgenden Szenario wird mit DeltaMaster ETL ein Datenmodel auf Basis der Microsoft-Demo-Daten Contoso aufgebaut. Das Model beinhaltet die MeasureGroup „Inventory“, welche nach Jahren in vier Partitionen geteilt ist. Jede Partition enthält ungefähr 4 Mio. Datensätze.

Abbildung 1: Partitionen von MeasureGroup “Inventory”

Implementierung

Info-Feld für Partitionierungsspalte

Jede Tabelle, welche in der zukünftigen View verwendet und abgefragt werden soll, muss eine Partitionierungsspalte enthalten. Hierzu wird ein Info-Feld für die MeasureGroup „Inventory“ hinzugefügt.

Abbildung 2: Info-Feld für Partitionierungsspalte

CHECK-Constraint für Partitionierungsspalte

Wie in Punkt 1 erwähnt, muss ein CHECK-Constraint für die Partitionierungsspalte definiert werden. Um die Constraints automatisch zu erstellen, wird die DeltaMaster-ETL-Prozedur „P_BC_CreateSnowflake_PostProcess“ verwendet.

ALTER PROCEDURE [dbo].[P_BC_CreateSnowflake_PostProcess]  
AS 

-- Create check constraints 
ALTER TABLE [dbo].[T_FACT_01_Inventory_01]  
ADD CONSTRAINT CK_FACT_01_Inventory_PartitionYear2017 CHECK (PartitionYear = 2017) 

ALTER TABLE [dbo].[T_FACT_01_Inventory_02]  
ADD CONSTRAINT CK_FACT_01_Inventory_PartitionYear2018 CHECK (PartitionYear = 2018) 

ALTER TABLE [dbo].[T_FACT_01_Inventory_03]  
ADD CONSTRAINT CK_FACT_01_Inventory_PartitionYear2019 CHECK (PartitionYear = 2019) 

ALTER TABLE [dbo].[T_FACT_01_Inventory_04]  
ADD CONSTRAINT CK_FACT_01_Inventory_PartitionYear2020 CHECK (PartitionYear = 2020) 

GO 

Auf diese Weise werden bei jeder Modelländerung die CHECK-Constraints erneut generiert.

Bezüglich der Bedingung, dass die Partitionierungsspalte Teil des PrimaryKey sein soll (siehe Punkt „Partitionierte View“), ist Folgendes anzumerken. Im gezeigten Szenario ist der Wert der Partitionierungsspalte für alle Datensätze einer T_FACT-Tabelle gleich, sodass die Aufnahme dieser Spalte in den PrimaryKey keinen Einfluss auf die Leistung der Abfrage hat. Aus diesem Grund bleibt die vorhandene Definition des PrimaryKey in die T_FACT-Tabellen unverändert.

V_SEC-View

Um den Zugriff auf die relationalen Daten der MeasureGroup „Inventory“ durch einen SQL-Durchgriff zu ermöglichen, wird die View „V_SEC_FACT_01_Inventory_01“ erstellt.

CREATE VIEW [dbo].[V_SEC_FACT_01_Inventory_01] 
AS 
SELECT * 
FROM [dbo].[T_FACT_01_Inventory_01] 

UNION ALL 

SELECT * 
FROM [dbo].[T_FACT_01_Inventory_02] 

UNION ALL 

SELECT * 
FROM [dbo].[T_FACT_01_Inventory_03] 

UNION ALL 

SELECT * 
FROM [dbo].[T_FACT_01_Inventory_04] 

GO

SQL-Durchgriff

Ein SQL-Durchgriff für die MeasureGroup „Inventory“ wird in DeltaMaster wie gewohnt erstellt.

Abbildung 3: SQL-Durchgriff

Der SQL-Durchgriff erzeugt eine SQL-Abfrage, welche die V_SEC-View und die T_DIM-Tabellen abfragt, wie unten zu sehen ist.

SELECT TOP 1000  

… 
FROM [dbo].[V_Sec_Fact_01_Inventory_01] 
INNER JOIN [dbo].[T_DIM_01_04_Day] ON … 
INNER JOIN [dbo].[T_DIM_01_03_Month] ON … 
INNER JOIN [dbo].[T_DIM_02_01_ValueType] ON … 
INNER JOIN [dbo].[T_DIM_03_01_PeriodView] ON … 
INNER JOIN [dbo].[T_DIM_04_01_Cumulation] ON … 
WHERE  
([dbo].[T_DIM_01_03_Month].[MonthID]=202006) AND  
([dbo].[T_DIM_02_01_ValueType].[ValueTypeID]=1) AND  
([dbo].[T_DIM_03_01_PeriodView].[PeriodViewID]=1) AND  
([dbo].[T_DIM_04_01_Cumulation].[CumulationID]=1)

Es muss der von DeltaMaster generierten Abfrage eine Filterbedingung für die Partitionierungsspalte „PartitionYear“ hinzugefügt werden. Dies kann mithilfe der Option „Zusätzliche WHERE-Bedingung“ (SQL-Durchgriff – Einstellungen) durch folgenden Ausdruck erreicht werden:

PartitionYear = LEFT(<viewXkey>,4)

Wobei der Platzhalter <viewXkey> den Schlüssel des Elements zurückgibt, X ist die ID der Dimension Periode.

Mit der LEFT-Funktion wird das Jahr zurückgegeben, unabhängig von der Dimensionsebene (Jahr, Quartal, Monat, Tag).

Abbildung 4: Einstellungen SQL-Durchgriff

Performance Analyse

Zu Testzwecken wurden zwei SQL-Durchgriff-Berichte gebaut:

– „SQL-DT Inventory (without Filter)“, hier wurde ein SQL-Durchgriff ohne Filter auf die Partitionierungsspalte definiert. Es werden Daten aus Juni 2020 angezeigt.

– „SQL-DT Inventory (with Filter)“, hier wurde ein SQL-Durchgriff mit Filter auf die Partitionierungsspalte definiert, wie in Punkt „SQL-Durchgriff“ beschrieben. Es werden ebenso Daten aus Juni 2020 angezeigt.

Die Abfrage aus dem Bericht „SQL-DT Inventory (without Filter)“, wie im folgenden Ausführungsplan ersichtlich, greift auf alle vier Partitionen zu, obwohl nur Daten aus dem Jahr 2020 (in Tabelle T_FACT_01_Inventory_04) abgefragt werden.

Abbildung 5: Ausführungsplan für Abfrage aus “ SQL-DT Inventory (without Filter)“

Im Vergleich dazu greift die Abfrage aus dem Bericht „SQL-DT Inventory (with Filter)“ nur auf die Partition zu, in der sich die Daten aus dem Jahr 2020 befinden. Dies reduziert die Gesamtausführungszeit der Abfrage.

Abbildung 6: Ausführungsplan für Abfrage aus „SQL-DT Inventory (with Filter)“

Die nächste Abbildung zeigt das SQL-Log aus „SQL-DT Inventory (without Filter)“. Es werden die Top 1000 Datensätze abgefragt mit einer Ausführungszeit von 4,820 Sekunden.

Abbildung 7: Sql.log aus „SQL-DT Inventory (without Filter)“

Das SQL-Log aus „SQL-DT Inventory (with Filter)“ zeigt die zusätzliche WHERE-Bedingung für die Partitionierungsspalte, sowie eine Ausführungszeit von 0,024 Sekunden für die Top 1000 Datensätze.

Abbildung 8: Sql.log aus „SQL-DT Inventory (with Filter)“

Die Leistung der Berichte wurde ebenso für weitere Mengen von Datensätzen getestet. Die Ausführungszeiten in Sekunden sind in folgender Tabelle zusammengefasst.

Tabelle 1: Ausführungszeiten SQL-Durchgriff-Berichte

Aus Performancegesichtspunkten wird empfohlen, immer die Partitionierungsspalte mitabzufragen, insbesondere bei MeasureGroups mit großen Datenmengen.

–

Partitionierte View: https://docs.microsoft.com/de-de/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15

Microsoft-Demo-Daten Contoso: https://www.microsoft.com/de-de/download/details.aspx?id=18279