Sascha Lorenz

So, seit wenigen Tagen (nun ja, fast Stunden) ist die CTP 3 vom SQL Server Denali draußen. Und groß in der Ankündigung von Microsoft steht drin, dass auch das sagenumwogende "Project Crescent" dabei sein soll.

Und schon geht die Sucherei los. Wo ist Crescent? Wir wollen auch diese ultra-coolen Reports bauen!

Die Antwort dazu ist relativ einfach. Es ist dabei, aber. Crescent ist ein Bestandteil von PowerPivot für SharePoint! Und das wiederum bedeutet, dass Ihr einen SharePoint 2010 Enterprise mit Service Pack 1 für die Nutzung von Crescent benötigt.

Details findet Ihr hier: Deployment Checklist: Reporting Services, Project Crescent, and PowerPivot for SharePoint

Zu meinem Post über den MERGE Befehl des SQL Servers habe ich viel Feedback bekommen und das Interesse an mehr dazu scheint recht groß. Danke dafür!

Und tatsächlich ist der MERGE Befehl auch noch um einiges mächtiger als ich in dem Beispiel für einen einfachen ELT Ladeprozess gezeigt hatte. Und natürlich lässt sich damit mehr machen als nur einen ELT Prozess zu unterstützen.

Hier noch mal die grundlegende Syntax:

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

Nun, was geht jetzt noch? :-)

Eine interessante Möglichkeit ist, dass sowohl zwei MATCHED als auch mehrere NOT MATCHED Klauseln unterstützt werden. Für den Einsatz gibt es aber Bedingungen.

Wenn mit zwei MATCHED Klauseln gearbeitet werden soll, dann muss die erste Klausel auf jeden Fall eine erweiterte Bedingung mittels AND Klausel beinhalten. Des Weiteren kann nur einmal UPDATE und einmal DELETE genutzt werden! Also ein "wenn Du so, dann Update A und wenn Du so, dann Update B" geht nicht. Dafür muss dann die OUTPUT Klausel verwendet werden, um den Befehl mit einem weiteren zu verbinden.

Ein möglicher Anwendungsfall für zwei MATCHED ist zum Beispiel wenn ein Kennzeichen in der Quelle für das Löschen eines Datensatzes im Ziel mit übergeben wird.

Wie ist das mit der NOT MATCHED Klausel? Erst mal der Hinweis, dass T-SQL standardmäßig davon ausgeht, dass wir meinen, dass ein Satz aus der Quelle nicht im Ziel gefunden wird. Logisch, oder? Aber es gibt durchaus auch die Version WHEN NOT MATCHED BY SOURCE. Damit werden Aktionen ausgelöst, wenn ein Satz aus dem Ziel nicht "mehr" in der Quelle gefunden wurde. Für alle Mengenlehre Fans unter Euch, dass ist dann ein Right outer join. Überhaupt ist der MERGE Befehl weniger eine Mischung aus UPDATE, INSERT und DELETE als eher ein JOIN mit der Option auf Aktionen. Und WHEN NOT MATCHED BY SOURCE darf wiederum zweimal vorkommen und als Aktionen entweder ein UPDATE oder DELETE auslösen. Also ähnlich einem MATCHED.

So, und nun nicht die OUTPUT Klausel vergessen. Und nochmals der Hinweis, die gibt es auch für INSERT, DELETE und UPDATE !!!

Zwischenstand, was haben wir denn bisher:

MERGE <Ziel>
USING <Quelle>
ON <Bedingung>
WHEN MATCHED (AND .) THEN <Update oder Delete>
WHEN MATCHED (AND .) THEN <Update oder Delete>
WHEN NOT MATCHED (BY TARGET (AND)) THEN <Insert>
WHEN NOT MATCHED (BY SOURCE (AND)) THEN <Update oder Delete>
WHEN NOT MATCHED (BY SOURCE (AND)) THEN <Update oder Delete>
OUTPUT $ACTION (,.);

Des Weiteren ist wichtig zu verstehen, dass jede Zeile nur einmal evaluiert wird! Also eine ON Bedingung, welche eine 1:N Verknüpfung enthält, wird fehlschlagen, wenn diese nicht durch eine weitere Bedingung "ausgebremst" wird. Der SQL Server wirft sonst folgende Fehlermeldung:

"Die MERGE-Anweisung hat versucht, dieselbe Zeile mehr als einmal zu aktualisieren oder zu löschen. Dies passiert, wenn eine Zielzeile mehr als einer Quellzeile entspricht. Eine MERGE-Anweisung kann dieselbe Zeile der Zieltabelle nicht mehrfach aktualisieren/löschen. Optimieren Sie die ON-Klausel, um sicherzustellen, dass eine Zielzeile mindestens einer Quellzeile entspricht, oder verwenden Sie die GROUP BY-Klausel, um die Quellzeilen zu gruppieren."

Was kann nun alles damit realisiert werden?

Gerade WHEN NOT MATCHED (BY SOURCE (AND)) THEN ermöglicht schöne Sachen. Zum Beispiel eine Historisierung von Daten, wenn diese im Quellsystem nicht mehr vorhanden sind. Also wenn Satz im Ziel vorhanden ist, aber in der Quelle nicht mehr, dann können wir diesen Löschen (wie öde) oder mittels UPDATE ein inaktiv Kennzeichen setzen. Dieses inaktiv Kennzeichen sollte dann aber auch als zusätzliche Bedingung aufgenommen werden, weil wir ja sonst immer dieses UPDATE durchführen würde, was recht suboptimal wäre.

So, und zum Abschluss noch der Hinweis an Alle, dass ich MERGE häufig als Alternative oder auch als deutliche Optimierung zum klassischen Cursor verwende! Viele Logiken, welche auf der Basis einer zeilenweise Betrachtungsweise von Tabellen basieren, lassen sich mittels MERGE bzw. auch einem zusammengesetzten Statement mit der Verwendung von OUTPUT, wesentlich effizienter gestalten!

Gestern ist auf Channel 9 der erste Teil einer kleinen Community Webcast Reihe vom Peter Kirchner und mir zum Thema SQL Server speziell für ISVs online gegangen. Die weiteren Teile werden dann ebenfalls in den nächsten Wochen online gehen. Was machen wir in diesen Casts? Hier der "Klappentext":

"In diesem ersten Teil der Webcast-Reihe von Sascha Lorenz und Peter Kirchner zum SQL Server 2008 R2 erhalten Sie einen kurzen Überblick über die Inhalte dieser Webcast-Reihe sowie als Einstieg in das Thema einige Grundlagen, die jeder besitzen sollte, wer mit dem SQL Server arbeitet. Bereits diese Grundlagen können gängige Fehler in Implementierungen, die den SQL Server betreffen, vermeiden und sogar bereits positive Auswirkungen auf die Performance Ihrer Softwarelösungen haben. Insbesondere das Verständnis für Abfragen soll geschärft werden, beim SQL Server in Mengen anstatt in Datensätzen zu denken. Gerade dieses Thema dürfte für Entwickler, die mit Oracle vertraut sind und nun für den Microsoft SQL Server entwickeln, von besonderem Interesse sein."

Vielen Dank an Lori und Peter für Ihre Unterstützung für dieses kleine Community Projekt!

Vom 26. bis zum 30.09.2011 findet in Mainz wieder die BASTA! statt.

Was ist die BASTA! ?
"Die BASTA! ist die renommierteste unabhängige Konferenz für .NET-Technologien im deutschsprachigen Raum. Hier kommen die besten Experten aus ganz Europa zusammen, um Ihnen ihr praxiserprobtes Wissen und ihre Erfahrung weiter zu geben. Technisches Know-how auf höchstem Niveau für Entwickler, exzellentes Erfahrungswissen für Softwarearchitekten und Projektleiter - das ist es, was die BASTA! auszeichnet." Zitat von der Website

Die BASTA! setzt sich mittlerweile aus drei "Konferenzen" zusammen:

• BASTA! - Entwickler, Entwickler.
• SQLCON - SQL Server.
• ShareConnect - SharePoint Server.

Und als Teil der SQLCON darf ich dieses Jahr zwei Vorträge zu meinen Lieblingsthemen halten:

Die Entwicklersicht auf Microsofts Business Intelligence am 27.09.2011 | 17:15 - 18:30 Uhr
In dieser Session wird anhand von Beispielen gezeigt, wie durch die Nutzung der offenen und dokumentierten APIs des SQL Servers und seiner Dienste BI-Projekte deutlich effektiver geplant und durchgeführt werden können. Es werden sowohl Anregungen für den gestandenen BI-Berater zur Nutzung von .NET-Code gegeben als auch Beispiele für Entwickler für die Integration in ihren Lösungen gezeigt.

Entwicklung eigener Business Intelligence Clients am 28.09.2011 | 08:30 - 09:45 Uhr
In dieser Session wird gezeigt, wo der Nutzen in der Erstellung eigener Clients für operative und analytische BI liegt und wie diese mit dem SQL Server und C# entwickelt werden können. Dafür werden die Technologien Analysis Services, Integration Services, ADOMD.NET, AMO, WinForms, ASP.NET und Silverlight miteinander kombiniert vorgestellt.

Das verspricht ein cooles Event zu werden! Freue mich schon drauf. Vielleicht treffe ich ja auch wieder den einen oder anderen von Euch.

Weitere Details findet Ihr u.a. auf http://basta.net/2011/sessions/?l=de&id=5&tid=2015.

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!