MERGE in T-SQL – Der unbekannte Befehl im BI Projekt für ELT

Wenn ich Diskussionen über die Entscheidung für eine ETL oder ELT Architektur führe oder auch nur moderiere, dann bringe ich als ein mögliches Beispiel für eine ELT Architektur gerne die Verwendung des MERGE Befehls. Nur wenige scheinen diesen Befehl bisher aktiv zu nutzen, dabei ist er schon seit SQL Server 2008 dabei und gehört auch zum SQL:2008 Standard!

Was macht nun der Befehl MERGE? Laut den BOL das hier:

“Führt Einfüge-, Aktualisierungs- oder Löschvorgänge in einer Zieltabelle anhand der Ergebnisse eines Joins mit einer Quelltabelle aus. Sie können z. B. zwei Tabellen synchronisieren, indem Sie Zeilen in einer Tabelle anhand von Unterschieden, die in der anderen Tabelle gefunden wurden, einfügen, aktualisieren oder löschen.”

Und damit ist er perfekt geeignet für den Ladeprozess von Dimensionen in Data Warehouse/BI Projekten. Denn dort benötigen wir ja sowohl ein INSERT als auch ein UPDATE für unsere Geschäftsobjekte in den Dimensionen. Ok, und ganz selten mal ein DELETE, sollte aber nicht die Regel sein.

Wie wende ich MERGE nun an? Auf den ersten Blick wirkt der MERGE Befehl ein wenig “unübersichtlich”.

MERGE <Ziel>
USING <qQuelle>
ON <Bedingung>
WHEN MATCHED THEN <meist ein Update>
WHEN NOT MATCHED THEN <meist ein Insert>;

Wie muss ein MERGE Befehl gelesen werden? Also, wir haben hinter dem MERGE das sogenannte “Ziel” und hinter dem USING die “Quelle”. Über das ON werden diese beiden verbunden (join). Nun kommt das WHEN MATCHED THEN. Hier kommt unsere Aktion hin, welche ausgeführt wird bei einer Erfüllung der Bedingung. Ok, und bei WHEN NOT MATCHED kommt halt die Aktion, wenn die Bedingung nicht erfüllt wurde. Soweit ganz einfach, oder?

Hier ein erstes einfaches Beispiel:

MERGE DimTarget as t
USING ELTSource as s
ON t.bk = s.bk
WHEN MATCHED THEN
    update set t.bk = s.bk, t.attribute1 = s.attribute1, t.attribute2 = s.attribute2 
WHEN NOT MATCHED THEN
    insert (bk, attribute1, attribute2)
    values (s.bk, s.attribute1, s.attribute2);

Das war doch schon nicht schlecht. Nun ist es aber so, dass hier wenn immer der BK (BusinessKey, Geschäftsschlüssel aus der DWH Lehre…) gleich ist auch ein Update durchgeführt wird. Das sollte natürlich nur sein, wenn sich was an den Attributen geändert hat! Eine erste Idee wäre nun die ON Klausel zu erweitern, aber dadurch würden wir unser Zielt nicht erreichen, da dann ja der Vergleich auf den BK nicht mehr passt. Wir benötigen eigentlich eine Zweiteilung der Bedingung, also wenn BK gleich und Attribute ungleich. MERGE unterstützt dies, indem wir das “WHEN MATCHED” erweitern!

MERGE DimTarget as t
USING ELTSource as s
ON t.bk = s.bk 
WHEN MATCHED AND (t.attribute1 != s.attribute1 or t.attribute2 != s.attribute2) 
    THEN
        update set t.bk = s.bk, t.attribute1 = s.attribute1, t.attribute2 = s.attribute2 
WHEN NOT MATCHED
    THEN
        insert (bk, attribute1, attribute2)
        values (s.bk, s.attribute1, s.attribute2);

Das ist schon eine deutliche Vereinfachung, aber nun kommt die Herausforderung der Slowly Changing Dimensionen vom Typ 2, also dem Historisieren von Attributen. Wie lässt sich so etwas nun mittels MERGE realisieren? Gar nicht! Wobei das so natürlich nicht stimmt, aber wir brauchen ein ganz klein wenig mehr als nur ein MERGE.

Aber zuerst mal kurz nachdenken, was wir bei einem SCD2 tatsächlich alles an möglichen Fällen haben.

• BK ist neu, damit auch die Zeile –> Insert
• BK ist bekannt, aber es wurden nur Typ 1 (also überschreiben) Attribute geändert –> Update
• BK ist bekannt und es haben sich Attribute vom Typ 2 geändert –> Update (von aktiv auf inaktiv) & Insert (also die neue aktive Zeile) !

Fangen wir einfach mal an.

MERGE DimTarget AS t
USING ELTSource AS s
ON t.bk = s.bk
WHEN MATCHED AND t.aktiv = 1 AND t.attribute2 != s.attribute2
    THEN -– BK ist gleich und Attribute hat sich geändert, daher auf inaktiv setzen
        UPDATE SET t.aktiv = 0, t.ende_datum = getdate(), t.attribute2 = s.attribute2
WHEN NOT MATCHED
    THEN -– BK ist unbekannt, daher neuer Satz
        INSERT (bk, attribute1, attribute2, aktiv, start_datum)
        VALUES (s.bk, s.attribute1, s.attribute2,1, getdate());

Nur wie bekommen wir jetzt die neuen Zeilen hinzugefügt, nachdem wir die “alten” auf inaktiv gesetzt haben? Dafür benötigen wir ein wenig Hilfe von einer Klausel, welche aber auch relativ unbekannt ist. Und zwar OUTPUT. by the way, UPDATE, INSERT & DELETE kennen die auch! Ist äußerst nützlich.

MERGE DimTarget AS t
USING ELTSource AS s
ON t.bk = s.bk
WHEN MATCHED AND t.aktiv = 1 AND t.attribute2 != s.attribute2
    THEN 
       UPDATE SET t.aktiv = 0, t.ende_datum = getdate()
WHEN NOT MATCHED
    THEN
        INSERT (bk, attribute1, attribute2, aktiv, start_datum)
        VALUES (s.bk, s.attribute1, s.attribute2,1, getdate())
OUTPUT $Action Befehl, s.bk, s.attribute1, s.attribute2;

Damit bekommen wir quasi als Resultat des MERGE Befehls eine Zusammenfassung der durchgeführten Aktionen. Uns interessieren wiederum nur die UPDATEs und nutzen den Output für ein INSERT als Input.

INSERT INTO DimTarget
SELECT bk, attribute1, attribute2, 1, GETDATE(), null
FROM
(
  MERGE DimTarget AS t
  USING ELTSource AS s
  ON t.bk = s.bk
    WHEN MATCHED AND t.aktiv = 1 AND t.attribute2 != s.attribute2
      THEN
         UPDATE SET t.aktiv = 0, t.ende_datum = getdate()
    WHEN NOT MATCHED
      THEN
         INSERT (bk, attribute1, attribute2, aktiv, start_datum)
         VALUES (s.bk, s.attribute1, s.attribute2,1, getdate())
    OUTPUT $Action Befehl, s.bk, s.attribute1, s.attribute2
) AS out
WHERE Befehl = 'UPDATE';

Und schon haben wir in nur einem Befehl Slowly Changing Dimensions Typ 2 abgebildet. Der Vorteil der Verwendung von MERGE ist, dass Quelle und Ziel nur einmal durchlaufen werden müssen für alle Operationen, da für alles nur ein Queryplan erstellt wird. Gerade bei größeren Datenmengen können ELT Architekturen damit deutlich vereinfacht und beschleunigt werden!