Einsatz von Merge bei Historisierung von Attributen (Teil 2)

In einem früheren Blogbeitrag lernten wir wie die Inhalte zweier Tabellen mit Hilfe von “MERGE” synchronisiert werden können. Heute schauen wir uns an, wie “MERGE” uns bezüglich Historisierung der Daten behilflich sein kann.

Sicher können Sie sich noch an die Historisierung von Attributen erinnern? (Blogbeitrag aus Juni 2011: Historische Betrachtung von Stammdaten im Data Warehouse).

Dort wurde zusätzlich zu mehreren Lösungsansätzen auch der optimale Ansatz vorgestellt, nämlich das Historisieren durch Zeitbetrachtung. Dabei kamen Update- und Insert-Statements zum Zuge.

Die Ansätze, um Änderungen in Dimensionstabellen zeitbezogen zu erfassen und gegebenenfalls historisch zu dokumentieren, sind auch unter dem Begriff “Slowly Changing Dimensions” Typ 2 (deutsch: sich langsam verändernde Dimensionen) bekannt.

Man kann mit dem MERGE-Befehl von SQL Server 2008 die Pflege einer Typ 2 Slowly Changing Dimension (SCD) in einem Data Warehouse in nur einem Befehl erledigen.

Schauen wir uns einmal alle möglichen Fälle bei einem “Slowly Changing Dimensions” (SCD2) an:

• Hat sich ein Kunde in der Quelltabelle geändert (Klassifikation ist anders) so soll in der Dimensionstabelle die aktive Zeile für den Kunden auf inaktiv (0) gesetzt und in GueltBIS das Datum von gestern eingetragen werden. Es wird eine neue Zeile mit derselben KundeID und den aktuellen Werten von Name und Klassifikation erzeugt. Diese Zeile bekommt den Status Aktiv (1) und ein GueltVON von heute.
• Gibt es einen neuen Kunden in der Importtabelle, so wird dieser mit dem Status Aktiv (1) und GueltVON von heute in die Dimensionstabelle eingetragen.
• Wurde ein Kunde im Vorsystem gelöscht, existiert die KundeID also mit dem Status Aktiv (1) in der Dimensionstabelle aber nicht in der Importtabelle, so bekommt die Zeile in der Dimensionstabelle den Status Gelöscht (2) und ein GueltBIS von gestern.

INSERT INTO T_Dim_Kunde (KundeID, KundeName, KundeVorname, KundeClass, aktiv, GueltVON)
SELECT tmv.KundeID, tmv.KundeName, tmv.KundeVorname, tmv.KundeClass, 1, getdate()
FROM (
MERGE T_Dim_Kunde as kd
USING TMV_Import_Kunde as tmv
ON kd.KundeID = tmv.KundeID

WHEN MATCHED
AND kd.aktiv = 1 AND kd.KundeClass <> tmv.KundeClass
THEN
UPDATE SET kd.aktiv = 0, kd.GueltBIS = getdate()-1

WHEN NOT MATCHED
THEN
insert (KundeID, KundeName, KundeVorname, KundeClass, aktiv, GueltVON)
values (tmv.KundeID, tmv.KundeName, tmv.KundeVorname, tmv.KundeClass, 1, getdate())

WHEN SOURCE NOT MATCHED
AND kd.aktiv = 1
THEN
UPDATE SET kd.GueltBIS = getdate()-1, kd.aktiv = 2

OUTPUT tmv.KundeID, tmv.KundeName, tmv.KundeVorname, tmv.KundeClass, $ACTION
) CHANGES (KundeID, KundeName, KundeVorname, KundeClass, ChangeAction)
WHERE ChangeAction ='UPDATE' and KundeID IS NOT NULL;

Zusammenfassung

• Für gelöschte Zeilen ist die Bedingung WHEN SOURCE NOT MATCHED da. Sie setzt die aktive Zeile des gelöschten Kunden auf den Status Gelöscht (2) und das GueltBis-Datum auf gestern.
• Neue Zeilen behandelt NOT MATCHED. Diese Bedingung trägt neue Kunden aus der Importtabelle in die Dimensionstabelle mit aktivem Status ein.
• Der komplexteste Fall ist die Änderung von Kunden. Die Bedingung WHEN MATCHED setzt den Kunden auf inaktiv mit GueltBis von gestern. Durch die zusätzliche Prüfung auf den Status Aktiv (1) und die Ungleichheit von der Spalte “Klassifikation” wird sie nur bei wirklich geänderten Zeilen ausgeführt. Nicht geänderte Kunden werden ignoriert.

Nun muss noch eine neue aktive Zeile in die Dimensionstabelle eingetragen werden, die den geänderten Wert für die Spalte “Klassifikation” und ein GueltBis von heute enthält. Das macht die INSERT-Anweisung. In der OUTPUT-Klausel des MERGE-Befehls stehen die ausgeführte Aktion (hier INSERT oder UPDATE) sowie die Werte aus der Importtabelle, die zur Änderung geführt haben. Die umschließende INSERT-Anweisung sucht sich durch die WHERE-Klausel (die Aktion UPDATE) nur die Änderungen heraus. Da der SOURCE NOT MATCHED Fall für gelöschte Datensätze auch ein UPDATE macht, muss zusätzlich überprüft werden, ob der Quelldatensatz existiert, indem ihre KundeID auf NOT NULL geprüft wird. Trifft beides zu kann die Zeile eingefügt werden.