SQL Server 2008 (4): Typ 2 Slowly Changing Dimensions mit MERGE
Ich habe es in verschiedenen Vorträgen schon erwähnt, aber bisher bin ich den Beweis schuldig geblieben: Man kann mit dem MERGE-Befehl von SQL Server 2008 und INSERT über DML die Pflege einer Typ 2 Slowly Changing Dimension (SCD) in einem Data Warehouse in nur einem Befehl erledigen. Hier nun das Wie.
Zuerst muss man die Aufgabe definieren, da es verschiedene Arten gibt, Typ 2 SCDs in Tabellen zu gießen.
Gegeben sei also eine Quelltabelle Produkte aus einem Produktivsystem, die in meinem Fall 3 Spalten enthält: Einen Primärschlüssel ProduktID, den Produktnamen PName und die Produktkategorie PKategorie. Die Dimensionstabelle, in die die Änderungen übertragen werden sollen heißt DimProdukt. Sie enthält einen eigenen Primärschlüssel DimProdID; der als IDENTITY-Feld implementiert ist, die 3 Spalten aus Produkte sowie die SCD-Spalten GueltVon, GueltBis und GueltStatus. GueltStatus soll dabei den Wert ‚Aktiv‘ enthalten für aktuell gültige Zeilen, ‚Inaktiv‘ für nicht mehr gültige Zeilen (wo es also eine neuere Zeile für dasselbe Produkt mit dem Status ‚Aktiv‘ oder ‚Gelöscht‘ gibt) und ‚Gelöscht‘ für Produkte, die es in der Quelltabelle nicht mehr gibt. GueltVon und GueltBis enthalten den jeweiligen Gültigkeitszeitraum, für aktive Produkte ist GueltBis NULL.
Die zu implementierende Logik sieht so aus:
· Hat sich ein Produkt in der Quelltabelle geändert (Name oder Kategorie sind anders geworden) so soll in DimProdukt die aktive Zeile für das Produkt auf inaktiv gesetzt und in GueltBis das Datum von gestern eingetragen werden. Es wird eine neue Zeile mit derselben ProduktID und den aktuellen Werten von Name und Kategorie erzeugt. Diese Zeile bekommt den Status ‚Aktiv‘ und ein GueltVon von heute
· Gibt es ein neues Produkt in Produkte, so wird dieses mit dem Status ‚Aktiv‘ und GueltVon von heute in DimProdukt eingetragen
· Wurde ein Produkt in Produkte gelöscht, existiert die ProduktID also mit dem Status ‚Aktiv‘ in DimProdukt aber nicht in Produkte, so bekommt die Zeile in DimProdukt den Status ‚Gelöscht‘ und ein GueltBis von gestern.
Und all das steht in dieser einen Anweisung:
INSERT INTO DimProdukt (ProduktID, PName, PKategorie, GueltVon, GueltStatus)
SELECT pProduktID, pPName, pPKategorie,
CONVERT(date, SYSDATETIME()), 'Aktiv'
FROM
( MERGE DimProdukt d
USING Produkte p
ON d.ProduktID = p.ProduktID
WHEN MATCHED AND d.GueltStatus = 'Aktiv' AND
(d.PName <> p.PName OR d.PKategorie <> p.PKategorie)
THEN
UPDATE SET d.GueltBis = DATEADD(d, -1,
CONVERT(date, SYSDATETIME())), d.GueltStatus = 'Inaktiv'
WHEN NOT MATCHED THEN
INSERT (ProduktID, PName, PKategorie, GueltVon, GueltStatus)
VALUES (p.ProduktID, p.PName, p.PKategorie,
CONVERT(date, SYSDATETIME()), 'Aktiv')
WHEN SOURCE NOT MATCHED AND d.GueltStatus = 'Aktiv' THEN
UPDATE SET d.GueltBis = DATEADD(d, -1,
CONVERT(date, SYSDATETIME())), d.GueltStatus = 'Gelöscht'
OUTPUT p.ProduktID, p.PName, p.PKategorie, $ACTION
) CHANGES (pProduktID, pPName, pPKategorie, ChangeAction)
WHERE ChangeAction ='UPDATE' and pProduktID IS NOT NULL;
Was tut sie?
· Für gelöschte Zeilen ist die Bedingung WHEN SOURCE NOT MATCHED da. Sie setzt die aktive Zeile des gelöschten Produkts auf den Status ‚Gelöscht‘ und das GueltBis-Datum auf gestern
· Neue Zeilen behandelt NOT MATCHED. Diese Bedingung trägt die in Produkte neue Zeile in DimProdukt mit aktivem Status ein.
· Der komplexteste Fall ist die Änderung von Produkten. Das Setzen der bisherigen Zeile für das Produkt auf inaktiv mit GueltBis von gestern erledigt die WHEN MATCHED Bedingung. Durch die zusätzliche Prüfung auf den Status ‚Aktiv‘ und die Ungleichheit von PName oder PKategorie wird sie nur bei wirklich geänderten Zeilen ausgeführt. Nicht geänderte Produkte werden ignoriert.
Nun muss noch eine neue aktive Zeile in die Zieltabelle eingetragen werden, die die geänderten Werte für PKategorie und PName und ein GueltBis von heute enthält. Das macht die umschließende INSERT-Anweisung. Der MERGE-Befehl gibt in seiner OUTPUT-Klausel die ausgeführte Aktion (hier INSERT oder UPDATE) sowie die Werte aus Produkte, die zur Änderung geführt haben aus. Die umschließende INSERT-Anweisung sucht sich durch den Filter auf UPDATE nur die Änderungen heraus. Da der SOURCE NOT MATCHED Zweig für gelöschte Datensätze auch ein UPDATE macht muss zusätzlich überprüft werden, ob die Quellzeile existiert, indem ihre ProduktID auf NOT NULL geprüft wird. Trifft beides zu kann die Zeile eingefügt werden.
Voila, fertig ist unsere Slowly Changing Dimension Pflege in nur einem T-SQL Befehl.
Verbesserungsvorschläge sind willkommen.
Wie immer gibt es hier ein fertiges, mit der Juli CTP von SQL Server 2008 kompatibles Projekt
Gruß,
Steffen