Die Aktualisierung von Bewegungsdaten nimmt während der täglichen Aufbereitung einer SQL-Datenbank in der Regel den Großteil der benötigten Gesamtzeit in Anspruch. Durch eine Partitionierung von Bewegungsdaten kann diese Dauer deutlich reduziert werden. Dieser Beitrag stellt einen erprobten Ansatz zur Partitionierung von Bewegungsdaten innerhalb einer SQL-Datenbank vor.

Im täglichen ETL-Prozess von Datenmodellen beansprucht die Aufbereitung der Bewegungsdaten oftmals einen nicht unerheblichen Anteil an der Gesamtdauer. Eine Partitionierung von Bewegungsdaten sollte für die meisten Measure Groups eines Datenmodells in Erwägung gezogen werden, um die Dauer der Datenaufbereitung zu verkürzen. Die Partitionierung von Bewegungsdaten ermöglicht es, die Datensätze einer Measure Group in mehrere Zieltabellen aufzuteilen und diese zielgerichtet zu aktualisieren. DeltaMaster ETL bietet hierfür die Möglichkeit pro Measure Group mehrere Quell-Tabellen oder Quell-Views zu verwenden und für jede dieser Quellen eine separate Partition in Form einer eigenen T_FACT-Tabelle zur Verfügung zu stellen. Hierfür muss im ETL-Bericht Measure Groups die Option Partition per Source Table aktiviert werden. Das Löschen und Befüllen der einzelnen T_FACT-Tabellen kann hierbei im ETL-Bericht Measure Group Source Tables/Partitions über die beiden Optionen Delete Partition Content und Fill Partition individuell gesteuert werden.

Im Folgenden soll eine Empfehlung für die Partitionierung von Bewegungsdaten gegeben werden, welche sich mittlerweile in einigen Kundenprojekten als robust und flexibel erwiesen hat. Der Ansatz verwendet drei Partitionen pro Measure Group, da dies die maximale Anzahl an zulässigen OLAP-Partitionen pro Measure Group in der Standard-Edition des Microsoft SQL Servers ist. Eine Verwendung von mehr als drei Partitionen pro Measure Group würde den Einsatz der teureren Enterprise-Edition des Microsoft SQL Servers bedingen. Als Partitionierungskriterium eignet sich der primäre Zeitstempel der Bewegungsdaten oftmals am besten.

Als Beispiel dient eine Measure Group namens Buchungen, deren primärer Zeitstempel in den importierten Bewegungsdaten das Buchungsdatum ist. Da die enthaltenen Zeiträume der einzelnen Partitionen aufeinander aufbauen, orientieren sich diese hinsichtlich des inhaltlichen Zeitverlaufs an der Reihenfolge ihrer Befüllung während einer Datenaufbereitung. DeltaMaster ETL befüllt die Partitionen einer Measure Group in aufsteigender Reihenfolge. Die Partition 1 wird vor der Partition 2 befüllt und die Partition 2 vor der Partition 3.

Es wird angenommen, dass im Vorsystem Korrekturbuchungen rückwirkend bis maximal zu Beginn des Vorjahres durchgeführt werden. Veränderungen in den Jahren vor dem Vorjahr sind nicht zu erwarten, da deren Jahresabschlüsse bereits erfolgt sind. Die erste Partition wird mit den Bewegungsdaten, welche ein Buchungsdatum vor dem ersten Januar des Vorjahres besitzen, befüllt. Eine Aktualisierung dieser Partition ist nur jeweils zum Jahreswechsel am ersten Januar notwendig, da das bisherige Vorjahr nun das Vorvorjahr darstellt. Sollte das Datenvolumen der Measure Group sehr hoch und hierdurch die Neu-Befüllung der ersten Partition viel Zeit benötigen, können die Daten des bisherigen Vorjahres auch direkt aus der T_FACT-Tabelle der zweiten Partition in die T_FACT-Tabelle der ersten Partition per INSERT INTO überführt und im Anschluss aus der T_FACT-Tabelle der zweiten Partition per DELETE gelöscht werden.

Des weiteren erfolgt die Annahme, dass im Zeitraum zwischen dem ersten Januar des Vorjahres und dem letzten Tag des Vorvormonats nur selten rückwirkende Korrekturbuchungen im Vorsystem erzeugt werden, da die Monatsabschlüsse in diesem Zeitraum abgeschlossen sind. Alle Datensätze mit einem Buchungsdatum in diesem Zeitraum werden in die zweite Partition der Measure Group geladen. Diese Partition wird jeweils am Sonntag aktualisiert, was einen Kompromiss zwischen der Berücksichtigung von Korrekturbuchungen und der Dauer der täglichen Datenaufbereitung darstellt. Die Häufigkeit einer Aktualisierung der zweiten Partition muss individuell im Projekt abgestimmt werden. Der Sonntag wird als Aktualisierungszeitpunkt gewählt, da davon ausgegangen wird, dass ein späterer Abschluss der Datenaktualisierung an diesem Wochentag nicht kritisch ist.

Für den Umgang mit einem sehr hohen Datenvolumen innerhalb einer Measure Group könnten alternativ alle Monate vor dem Vormonat nur als Monatssalden im Datenmodell vorgehalten werden, während der Vormonat und der aktuelle Monat tagesgenau abgebildet werden. In diesem Fall müsste die zweite Partition auch jeweils zum Monatswechsel am ersten Tag eines jeden Monats aktualisiert werden.

Im vorliegenden Fallbeispiel werden rückwirkend bis zum ersten Tag des Vormonats die meisten Korrekturbuchungen im Vorsystem erzeugt, da der Monatsabschluss des Vormonats erst im laufenden Monat erfolgt. Dementsprechend werden die Daten ab dem ersten Tag des Vormonats in die dritte Partition der Measure Group geladen und die Neu-Befüllung dieser Partition erfolgt täglich.

Sollten sich die drei Quell-Views der drei Partitionen nur in der Filterung des Buchungsdatums unterscheiden, dann ist es auch möglich nur eine einzige Quell-View ohne Filterung des Buchungsdatums zu verwenden. Die Filterung des Buchungsdatums kann pro Partition im Bericht Measure Group Source Tables/Partitions im Feld Where Clause definiert werden. Die hinterlegte Where-Clause der Partitionen wird bei der erneuten Erstellung der P_FACT-Prozeduren in dessen SQL-Code eingefügt. Hierdurch müssen Anpassungen innerhalb der Quell-Logik nur in einer View anstatt in drei Views berücksichtigt werden. Sollten aber bereits die Importdaten in verschiedenen Tabellen partitioniert vorliegen, dann ist es aus Performancegründen effizienter, unterschiedliche Quell-Views zu erstellen, welche nur auf die für sie relevanten Importtabellen und Zeiträume verweisen. In diesem Fall ist die Verwendung der Where-Clause in den P_FACT-Prozeduren für die Partitionierung nicht geeignet.

Zusätzlich wird angenommen, dass im Datenmodell rollierend nur die Bewegungsdaten der letzten x Jahre vorgehalten werden müssen. Zur Dynamisierung dieser Anforderung wird eine Skalarwert-Funktion erstellt, welche ausgehend vom aktuellen Datum den ersten Tag des ersten relevanten Jahres zurückgibt. Die Verwendung einer zentralen Funktion in der jeweils ersten Partition aller Measure Groups hat den Vorteil, dass bei einer Änderung des modellweiten Zeitraums der Bewegungsdaten die Anpassung nur in dieser zentralen Funktion und nicht in mehreren Quell-Views durchgeführt werden muss. Im Beispiel sollen das aktuelle Jahr und die letzten vier zurückliegenden Jahre im Datenmodell verfügbar sein. Die folgende Funktion liefert den ersten Januar vor vier Jahren zurück.

CREATE FUNCTION [dbo].[F_APP_ReturnFirstDay] () 

RETURNS datetime 

BEGIN 

   DECLARE @FirstDay date 

   SET @FirstDay =  

      (SELECT CONVERT(datetime, CONVERT(varchar(8), (YEAR(GETDATE()) - 4)  

      * 10000 + 101))) 

   RETURN @FirstDay 

END

Im Folgenden werden die drei Quell-Views des Fallbeispiels beschrieben.

Die Quellview der ersten Partition ermittelt zunächst in einer Common Table Expression (CTE) für den ersten Tag der Partition den ersten relevanten Tag im Datenmodell aus der obigen Funktion [dbo].[F_APP_ReturnFirstDay], sowie für den letzten Tag der Partition den 31. Dezember im Vorvorjahr. Im Anschluss wird in der WHERE-Bedingung das Buchungsdatum über diese beiden Zeitstempel eingeschränkt. Die Ermittlung dieser Zeitstempel erfolgt vorgelagert zu Beginn der View in einer CTE, damit diese nicht für jeden Datensatz der Hauptabfrage berechnet werden müssen.

CREATE VIEW [dbo].[V_Import_FACT_Buchungen_1] AS 
 WITH 

   -- [ErsterTag], [LetzterTag] 
   CTE_Zeitraum AS 
   ( 
   SELECT 
      -- erster relevanter Tag im Datenmodell 
      [dbo].[F_APP_ReturnFirstDay] ()	 AS [ErsterTag], 
 
      -- 31. Dezember im Vorvorjahr 
      CONVERT(datetime, CONVERT(varchar(8), (YEAR(GETDATE()) - 2)  
      * 10000 + 1231))			                     AS [LetzterTag] 
   ) 

SELECT 
   bu.[Buchungsdatum], 
   bu.[Kunde], 
   bu.[Artikel], 
   bu.[Umsatz] 

FROM 
   -- Buchungen 
   [dbo].[T_Import_Buchungen]	AS bu 

   -- Einschränkung über Buchungsdatum	 
   INNER JOIN 
   CTE_Zeitraum			AS zt 
      ON bu.[Buchungsdatum] BETWEEN zt.[ErsterTag] AND zt.[LetzterTag]

Die Quellview der zweiten Partition ermittelt äquivalent zur Quellview der ersten Partition zunächst in einer CTE die Zeitstempel zur Einschränkung des Buchungsdatums. Für diese View sind die Zeitstempel für den ersten Tag der Partition der Folgetag des letzten Tags in der ersten Partition und für den letzten Tag der Partition der letzte Tag im Vorvormonat. Falls die erste Partition nicht befüllt ist, wird für den ersten Tag der Partition der erste relevante Tag im Datenmodell aus der Funktion [dbo].[F_APP_ReturnFirstDay] ermittelt. Im Anschluss wird in der WHERE-Bedingung das Buchungsdatum über diese beiden Zeitstempel eingeschränkt.

CREATE VIEW [dbo].[V_Import_FACT_Buchungen_2] AS 
WITH 
 
   -- [ErsterTag], [LetzterTag] 
   CTE_Zeitraum AS 
   ( 
   SELECT 
      -- Folgetag des letzten Tags in der ersten Partition  
      -- oder erster relevanter Tag im Datenmodell 
      COALESCE 
             ( 
             (SELECT DATEADD(DAY, 1, MAX([TagID])) FROM [T_FACT_Buchungen_01]), 
             [dbo].[F_APP_ReturnFirstDay] () 
             )							AS [ErsterTag], 

      -- letzter Tag im Vorvormonat 
      DATEADD(DAY, -1, CONVERT(datetime, CONVERT(varchar(8), 
      (YEAR(DATEADD(MONTH, -1, GETDATE())) * 100  
      + MONTH(DATEADD(MONTH, -1, GETDATE()))) * 100 + 1)))	AS [LetzterTag] 
   ) 

SELECT 
   bu.[Buchungsdatum], 
   bu.[Kunde], 
   bu.[Artikel], 
   bu.[Umsatz] 
FROM 
   -- Buchungen 
   [dbo].[T_Import_Buchungen]	   AS bu 

   -- Einschränkung über Buchungsdatum	 
   INNER JOIN 
   CTE_Zeitraum			   AS zt 
      ON bu.[Buchungsdatum] BETWEEN zt.[ErsterTag] AND zt.[LetzterTag]

Die Quellview der dritten Partition ermittelt zu Beginn in einer CTE für den ersten Tag der Partition den Folgetag des letzten Tags in der zweiten Partition. Falls die zweite Partition nicht befüllt ist, wird für den ersten Tag der Partition der Folgetag des letzten Tags in der ersten Partition ermittelt. Falls die erste Partition ebenfalls nicht befüllt ist, wird für den ersten Tag der Partition der erste relevante Tag im Datenmodell aus der Funktion [dbo].[F_APP_ReturnFirstDay] ermittelt. Im Anschluss wird in der WHERE-Bedingung das Buchungsdatum über diesen Zeitstempel eingeschränkt.

CREATE VIEW [dbo].[V_Import_FACT_Buchungen_3] AS 

WITH 
   
   -- [ErsterTag] 
   CTE_Zeitraum AS 
   ( 
   SELECT 
      -- Folgetag des letzten Tags in der zweiten Partition 
      -- oder Folgetag des letzten Tags in der ersten Partition 
      -- oder erster relevanter Tag im Datenmodell 

      COALESCE 
             ( 
             (SELECT DATEADD(DAY, 1, MAX([TagID])) FROM [T_FACT_Buchungen_02]), 
             (SELECT DATEADD(DAY, 1, MAX([TagID])) FROM [T_FACT_Buchungen_01]), 
             [dbo].[F_APP_ReturnFirstDay] () 
             )	    AS [ErsterTag] 
   )	 
SELECT 
   bu.[Buchungsdatum], 
   bu.[Kunde], 
   bu.[Artikel], 
   bu.[Umsatz] 

FROM 
   -- Buchungen 
   [dbo].[T_Import_Buchungen]	AS bu 

   -- Einschränkung über Buchungsdatum 
   INNER JOIN 
   CTE_Zeitraum			AS zt 
      ON bu.[Buchungsdatum] >= zt.[ErsterTag]

Ob eine Partition während einer Datenaufbereitung gelöscht und neu befüllt werden soll, wird durch Aktivierung bzw. Deaktivierung der im ETL-Bericht Measure Group Source Tables/Partitions enthalte-nen Optionen Delete Partition Content und Fill Partition gesteuert. Diese beiden Optionen werden im Fallbeispiel über die beiden Prozeduren [dbo].[P_Transform_00_PreProcess] und [dbo].[P_Trans-form_99_PostProcess] dynamisch gesteuert. Beide Prozeduren werden jeweils zu Beginn und Ende einer Datenaufbereitung ausgeführt. In der Prozedur [dbo].[P_Transform_00_PreProcess] wird abhän-gig vom aktuellen Tag die Neu-Befüllung der ersten und zweiten Partition aktiviert, während die Neu-Befüllung der dritten Partitionstabelle immer aktiv ist.

ALTER PROCEDURE [dbo].[P_Transform_00_PreProcess] AS

-- Ausführung am ersten Januar
IF MONTH(GETDATE()) = 1 AND DAY(GETDATE()) = 1

BEGIN

   -- Aktivierung der Aktualisierung der ersten Partition der Buchungen
   UPDATE
      [dbo].[T_Model_Fact_SourceTable]
   SET
      [DoDeleteTable] = 1,
      [DoFillTable] = 1
   WHERE
      [FactID] = 1
      AND [SourceTableID] = 1

END

-- Ausführung am Sonntag
IF DATEPART(WEEKDAY, GETDATE()) = 7

BEGIN

   -- Aktivierung der Aktualisierung der zweiten Partition der Buchungen
   UPDATE
      [dbo].[T_Model_Fact_SourceTable]
   SET
      [DoDeleteTable] = 1,
      [DoFillTable] = 1
   WHERE
      [FactID] = 1
      AND [SourceTableID] = 2

END

In der Prozedur [dbo].[P_Transform_99_PostProcess] wird die Neu-Befüllung der ersten und zweiten Partition wieder deaktiviert.

ALTER PROCEDURE [dbo].[P_Transform_99_PostProcess] AS

-- Deaktivierung der Aktualisierung der ersten und der zweiten Partition der Buchungen
UPDATE
   [dbo].[T_Model_Fact_SourceTable]
SET
   [DoDeleteTable] = 0,
   [DoFillTable] = 0
WHERE
   [FactID] = 1
   AND [SourceTableID] IN (1, 2)