Wer hätte gedacht dass man heutzutage noch über die Vermächtnisse einer 20 Jahre alten Software stolpern kann – und das in den Integration Services!

Aber beginnen wir von vorne: immer wenn die Bearbeitung mehrere gleichartiger Dateien mittels SSIS gefragt ist, kommt früher oder später der ForEach-Loop-Container zum Einsatz. Damit lässt sich z.B. recht komfortabel über Dateien mit einer bestimmten Namensstruktur innerhalb eines Ordners iterieren. So könnte ein mögliches Importszenario vorsehen, dass mehrere Exceldateien in einem Ordner ausgelesen werden sollen – den ForEach-Loop-Container könnte man dann wie folgt konfigurieren:

Soweit so gut, der Rest des Paketes könnte dann wie folgt aussehen. Innerhalb des Loops wird eine Variable mit dem aktuellen Dateipfad ausgelesen um alle gefundenen Dateien nach Beendigung des Containers in einer MessageBox anzuzeigen:

Ich habe bisher den Inhalt des betroffenen Ordners unterschlagen, hier ist er:

Und siehe da: unser Paket findet zwei Exceldateien – wunderbar! Oder doch nicht? Störend an dieser Stelle könnte die Excel-Datei sein, die im neuen 2007er Format gespeichert wurde. Diese hat die Endung .xlsx! Eigentlich ganz praktisch, denn die beiden Exceltypen unterscheiden sich grundlegend und erfordern ggf. eine differenzierte Behandlung innerhalb der SSIS. Weitere prüfende Blicke in die Konfiguration des ForEach-Loops lassen letztlich nur einen Schluss zu: egal wie man es dreht und wendet, der Ausdruck im “Files”-Feld des Editors lässt sich nicht dahingehend ändern dass er nur .xls Dateien erwischt (was man eigentlich erwarten würde) – der Ausdruck verhält sich also wie *.xls*!

An dieser Stelle wirken die Books Online einmal mehr erhellend und decken auf, dass es sich quasi um erwünschtes Verhalten handelt – der Ausdruck verhält sich nämlich wie beim dir-Befehl in Windows (siehe: http://msdn.microsoft.com/en-us/library/ms187670.aspx). Dieser wiederum ist aus Kompatibilitätsgründen so gestrickt, dass er nur drei Zeichen der Dateinamenerweiterung betrachtet – die gute alte 8.3-Dateinamenbeschränkung aus MS-DOS Zeiten schlägt hier also wieder einmal zu!

Eine Differenzierung nach Dateierweiterung muss also später geschehen, die könnte dann z.B. so aussehen: innerhalb des ForEach-Loop-Containers fügen wir eine Rangfolgeeinschränkung (zu deutsch: einen Pfeil) ein und binden daran eine SSIS-Expression. Diese prüft, ob die letzten drei Buchstaben des ausgelesenen Dateinamens auch tatsächlich x, l und s sind:

So modifiziert, verrichtet das Paket ordnungsgemäß seinen Dienst und findet tatsächlich nur eine Excel-Datei:

Da kann man sich natürlich schon fragen ob es sich um ein Bug oder ein Feature handelt, aber genau wegen diesen Ecken und Kanten lieben wir die Integration Services so sehr – man entdeckt eben immer wieder etwas Neues!

Divide et impera – das von Machiavelli vor gut 500 Jahren geprägte Prinzip des Teilen und Herrschens ist heutzutage eine allseits beliebte Technik zur Problemlösung. Auch in den Integration Services finden sich eine Menge Möglichkeiten, um große Aufgaben in kleinere, übersichtliche Schritte zu unterteilen. Beispiele sind unterschiedliche Datenflüsse für unterschiedliche Aufgaben, die Strukturierung mittels Sequenzcontainern und nicht zuletzt die Möglichkeit, aus einem SSIS Paket andere SSIS Pakete aufzurufen.

Doch mit steigender Komplexität der Pakete (Verbindungsmanager, Paketvariablen) und ausgiebiger Nutzung der Paketkonfiguration gelangt man mit den SSIS-Bordmitteln einmal mehr an die Grenzen des Wartbaren. Die Integration Services bieten von Haus aus die Möglichkeit, andere SSIS Pakete aufzurufen, doch die Übergabe von Variablen und Verbindungsmanagern an das “Kindpaket” gestaltet sich sehr aufwändig (viel Expression-Hacking). Die Möglichkeit, Rückgabewerte vom Kindpaket zu empfangen fehlt gar gänzlich.

In diese Nische stürzt sich ein Codeplex-Projekt, welches ich im Folgenden kurz vorstellen möchte: der “Enhanced SSIS Execute Package Task”. Unter http://ssisexec.codeplex.com/ ist das noch recht unbekannte Projekt zu finden (derzeit für SSIS 2005 und SSIS 2008 R2 – siehe “Downloads”). Hilfreich bei der Installation (es wird eine dll und ein schmales readme geliefert) ist folgender Eintrag in den Books Online: http://msdn.microsoft.com/en-us/library/ms403356.aspx Hat man die Komponente erfolgreich installiert lässt sie sich über einen Rechtsklick in die Toolbox -> Choose Items in die Liste der Kontrollfluss Tasks aufnehmen.

Die Komponente bietet eine grafische Oberfläche zum Mappen der Variablen bzw. Verbindungsmanagern zwischen den Paketen. Diese können entweder im Dateisystem oder in der MSDB serverseitig gespeichert sein. Somit lassen sich recht schnell und komfortabel Konfigurationen zwischen Paketen übergeben. Wie das aussehen könnte sieht man in folgendem screenshot: Hier wird von einem Steuerpaket, welches den Pfad zu einer Exceldatei via Paketkonfiguration erhält, ein Kindpaket aufgerufen wobei der Verbindungsmanager zwischen den Paketen übergeben wird.

Hinweis: Das Projekt hat derzeit noch den Alpha-Status – vom Einsatz in einer Produktivumgebung rate ich also dringend ab! Laut Aussage des Authors scheint es aber stabil zu laufen und dieser freut sich mit Sicherheit über ausgiebiges feedback in Form von Kommentaren oder Anmerkungen. In diesem Sinne: happy testing!

Wer kennt sie nicht: die Problematik der führenden Nullen in Excel. Um beispielsweise Postleitzahlen mit führenden Nullen korrekt darzustellen, bedarf es in aller Regel Einiges an Formatierungsaufwand, da Excel Zahlen gerne als numerischen Wert interpretiert. Befüllt man nun mit den SSIS ein Excel-Ziel, gehen führende Nullen beim ersten Öffnen der Excel-Mappe verloren, ganz gleich ob man z.B. eine Spalte PLZ als Text oder Zahl durchleitet.

Abhilfe schafft hier ein kleiner Trick, der in den screenshots unten zu sehen ist. Mittels des Tasks “Abgeleitete Spalte” erstellt man eine neue Textspalte, deren Inhalt die Spalte mit den führenden Nullen eingebettet in Hochkommata und ein vorangestelltes Gleichheitszeichen ist (Escape-Sequenz beachten!).

Als Resultat interpretiert Excel fortan den Wert in der Spalte als Formel und die führende Null wird korrekt dargestellt.

In den vorangegangenen beiden Teilen dieses Artikels (Teil I, Teil II) haben wir uns mit den Möglichkeiten des Range-Lookups beschäftigt, die ohne weitere Programmierung oder zusätzliche Komponenten mit reinen SSIS-Boardmitteln realisiert werden können. Nun wollen wir uns mit einer dritten Variante beschäftigen, um in unserem Beispielszenario die Kunden nach Ihrem Einkommen in die bereits bekannten Einkommensgruppen einzuteilen:

Lösung mittels Script Task – Wunderwaffe “Binary Search”

Wenn einen die mitgelieferten Standardkomponenten der Integration Services mal wieder nicht so richtig ans Ziel bringen, dann gibt es ja zum Glück immer noch den Script Task, den wir auch dazu nutzen können, um einen eigenen Lookup zu realisieren. Und genau das werden wir jetzt tun. Script Transformation in unseren Datenfluss gezogen und schon kann es mit der Definition der Eingabespalten beginnen. In unserem Fall reicht das YearlyIncome aus:

Danach müssen wir dann noch die zugehörige Ausgabespalte definieren, die dann später unsere Gruppen-ID aufnehmen soll:

Da ich die möglichen Gruppen direkt im Script-Task aus der Datenbank lesen möchte (es gibt auch andere, durchaus sauberere Wege), benötige ich auch noch einen Verbindungsmanager (der Einfachheit halber mit dem .NET Provider für SQL Server):

Nun kann ich mir noch die Programmiersprache auswählen (ich nehm mal C#) und endlich mit der eigentlichen Arbeit beginnen.

Als erstes benötige ich einen gefüllten Cache, der die möglichen Ausprägungen meiner Gehaltsgruppen aufnehmen kann. Diesen definiere ich als globale generische Liste einer eigens dafür gedachten Klasse Namens “NumericRangeItem”:

Im PreExecute übernehme ich dann den Freigegebenen Verbindungsmanager und fülle einmalig den besagten Cache:

Im FillCache() werden dann die oben gezeigten Zeilen aus der Datenbank gelesen und für jede Zeile ein neues NumericRangeItem in den Cache übernommen. Das NumericRangeItem ist dabei im wesentlichen ein Key-Value-Pair, wobei IncomeFrom als Key und die zugehörige IncomeGroupID als Value übernommen wird :

Besonderheit ist, dass es die Schnittstelle IComparable implementiert. Dazu muss die Klasse also eine Methode namens CompareTo enthalten, die aber auch ziemlich simpel ist.

Damit wird lediglich festgelegt, dass beim Vergleich zweier NumericRangeItems die Schlüssel (also die Einkommensgrenze) miteinander verglichen werden.

Ergebnis ist, dass ich nach dem PreExecute alle Einkommensgrenzen als generische Liste vergleichbarer Elemente im Speicher habe. Belohnt werde ich für diesen Aufwand dann damit, dass eine solche Liste eine Methode namens BinarySearch() zur Verfügung stellt, die wir dann für den eigentlichen Lookup nutzen können:

Für jede Zeile die ein Einkommen enthält wird die Funktion GetID aufgerufen, die einen numerischen Wert übergeben bekommt und das dazu passende NumericRangeItem zurückgibt. Der zu suchende Wert wird dabei selbst in einem NumericRangeItem verpackt, damit der Vergleich funktioniert. Und dann kann BinarySearch() die eigentliche Arbeit übernehmen. Dabei wird immer in die Mitte der Wertmenge geschaut und überprüft ob der dort vorhandene Wert größer oder kleiner als der Vergleichswert ist und dann mit der jeweils passenden Hälfte weitergearbeitet. Dadurch kann man mit BinarySerach auch in großen Datenmengen sehr schnell suchen (im schlimmsten Fall werden log₂(N) + 1 Iterationen benötigt => zum Durchsuchen von 1 Mio Datensätzen werden höchstens 20 Versuche gebraucht). Was das Ganze für unser Szenario aber erst nutzbar macht, ist, dass auch für Werte, die nicht gefunden werden können ein Ergebnis geliefert wird.

Mathematisch ist dieser negative Rückgabewert das Komplement des nächstgrößeren Index. Da wir über die Untergrenzen suchen (IncomeFrom) erhalten wir also mit if (iScore < 0) iScore = (short)(~iScore – 1); genau das Ergebnis, dass wir für unseren RangeLookup brauchen.

Das Ergebnis sieht dann wie zu hoffen war folgendermaßen aus:

Dieses Bild haben wir so ähnlich bereits im Teil I gesehen, nur dass das Ergebnis diesmal in nicht mal einem Sechstel der Zeit berechnet war. Bei größeren Datenmengen verstärkt sich dieser Effekt sogar noch weiter. In unserem kleinen Beispiel benötigt der Script Task noch fast die Hälfte für das PreExecute, in dem ja auch der Cache gefüllt werden muss. Wenn wir aber nicht mehr 18k sondern ein paar Millionen Datensätze durch die Pipeline schicken, fällt dieser Overhead nicht mehr ins Gewicht.

Mit der Wunderwaffe BinarySearch brauchen wir also in Zukunft keine Angst mehr vor Range-Lookups bei großen Datenmengen zu haben.

Wie im Teil I versprochen wollen wir uns nun mit einer weiteren Möglichkeit für Range-Lookups mit den Integration Services auseinandersetzen.

Dazu zur Erinnerung noch einmal unsere Quelltabelle, aus der wir die zugehörigen IDs ermitteln wollen:

Lösung mittels Lookup – Mit Caching

Wie wir festgestellt haben, verliert der Lookup bei ausgeschaltetem Caching enorm an Geschwindigkeit. Alternativ könnte man also versuchen, den Range-Lookup mit aktiviertem Caching zu realisieren. Da der Lookup dann aber nur genaue Übereinstimmungen als Treffer wertet, muss für jeden möglichen Wert eine Zeile in der Lookup Tabelle existieren. Angenommen die Einkommen sind im beschriebenen Fall als volle Eurobeträge gespeichert, ließe sich das mit folgender rekursiven Common Table Expression (CTE) realisieren:

WITH IncomeGroups(IncomeGroupID,Income, IncomeTo)

AS

(

SELECT    [IncomeGroupID],[IncomeFrom] as Income, [IncomeTo]

FROM    dbo._MATCH_IncomeGroup

WHERE    IncomeGroupID < 7

UNION
ALL

SELECT [IncomeGroupID], Income + 1, [IncomeTo]

FROM IncomeGroups

WHERE Income + 1 <= IncomeTo

)

SELECT Income, IncomeGroupID

FROM IncomeGroups

ORDER
BY 1,2

OPTION (MAXRECURSION 0)

Dieses Statement liefert im Management das folgende Ergebnis:

Dies könnte als Quelle für den Lookup Task verwendet werden der dann wie jeder andere gewöhnliche Lookup konfiguriert werden kann.

Anschließend müsste dann die oberste Grenze, die in der Quelle bewusst ausgelassen wurde (denn 999.999.999 Datensätze wollte ich nun wirklich nicht erzeugen) per Abgeleitete Spalte (derived column) gesetzt werden.

Für Situationen in denen sehr viele Datensätze verarbeitet werden müssen, könnte dies eine gute Alternative zu der Variante ohne Caching sein. Es wird aber auch schnell klar, dass auch dieses Verfahren seine Grenzen hat. Für eine Aufteilung in Altersklassen von Kunden, für die es nur sehr wenige Ausprägungen in den Quelldaten gibt, ist dies wahrscheinlich die beste Lösung. Wenn es aber darum geht Firmenumsätze, die in die Milliarden gehen, in Gruppen einzuteilen, wird sowohl die Abfragezeit der Rekursion, als auch der benötigte Speicherplatz den Rahmen sprengen. Spätestens aber wenn die Grenzen nicht mehr mit ganzen Zahlen definierbar sind, ist dann wirklich Schluss.

Um auch für diese Situationen gerüstet zu sein, wird sich der nächste und vermutlich letzte Teil dieses Artikels mit der dritten Alternative, dem Range-Lookup mittels Script Task auseinandersetzen.

Bekanntlich führen bei den Integration Services viele Wege zum Ziel. So kann selbst die Lösung einfachster Aufgaben bei unterschiedlichen Entwicklungsstilen beliebig komplex ausfallen. Heute möchte ich Ihnen meine vier beliebtesten Lösungen für die typische Aufgabe “Wie benenne ich eine Spalte im SSIS-Datenfluss um?” vorstellen. Die Aufgabe: zwei Spalten namens “given_name” und “surname” sollen in “FirstName” bzw. “LastName” umbenannt werden. Die Lösung: Sehen Sie selbst! Und wetten, Sie kennen mindestens einen der Wege noch nicht?

Weg #1: Abgeleitete Spalte

Der “Standardweg” zeichnet sich durch Übersichtlichkeit und Einfachheit aus. Mit dem Toolbox-Element “Abgeleitete Spalte” lassen sich bekanntlich neue Spalten erstellen. So können wir ganz bequem zwei neue Spalten mit den gewünschten Namen erstellen, deren Inhalt einfach aus den alten Spalten abgeleitet wird:

Der große Nachteil hierbei ist die Tatsache, dass unser Datenfluss von nun an vier Spalten enthält, wobei wir nur zwei benötigen. Ich halte meine SSIS-Pakete gerne übersichtlich, und spätestens wenn es an das Wegschreiben der Daten geht hilft es, wenn die Spalten in Bezeichnung und Anzahl übereinstimmen. Abhilfe schafft hier das “Union All” Element, welches das Löschen überflüssiger Spalten ermöglicht. Hinter dem Union-Element erscheinen dann nur noch die gewünschten Spalten:

Weg #2: Der Erweiterte Editor

Bei vielen Toolbox-Elementen lassen sich Ausgangsspalten auch direkt umbenennen, so z.B. bei dem Element “Suche”. Für alle gewünschten Suchspalten lässt sich ein Alias definieren:

Manchmal hilft auch der Blick in den “erweiterten Editor”, der für viele Toolbox-Elemente dieselbe Funktionalität bietet, nur eben etwas versteckter.

Weg #3: Das Union-Element

“In der Kürze liegt die Würze” – so oder so ähnlich könnte das Tooltip für das Union-Element lauten, denn es erlaubt die Umbenennung der Ausgangsspalten nach Belieben:

Aber Vorsicht! Diese Lösung erscheint solange praktikabel bis sich die Metadaten der betroffenen Spalten irgendwo überhalb des Union-Elements ändern. Dann erwartet Sie (je nach Art der Metadatenänderung) eine regelrechte “Klickorgie”, da die betroffenen Spalten aus dem Union-Element entfernt, neu eingefügt und umbenannt werden müssen. Das alles natürlich erst nachdem SSIS Sie freundlich mit einem Pop-Up auf die Metadaten-Änderung hingewiesen hat. Im schlimmsten Fall haben Sie beim Troubleshooting vergessen, wie die eigentliche Umbenennung lautete…

Weg #4: Abgeleitete Spalte für Fortgeschrittene

Einen sehr eigenwilligen Weg habe ich erst kürzlich entdeckt. Bei der Einarbeitung in ein bestehendes SSIS-Paket verfolgte ich den Verlauf einer Spalte, wobei mir auffiel, dass die Spaltenbezeichner vor und nach einem Element “Abgeleitete Spalte” komplett unterschiedlich waren und weder ersichtlich wurde an welcher Stelle eine Umbenennung stattgefunden hatte, noch welche Ausgangsspalte den Inhalt welcher Eingangsspalte trug. Nach langem Probieren war ich in der Lage folgenden “hack” zu rekonstruieren:

Zunächst nutzt man das Element “Abgeleitete Spalte” um den Inhalt der beiden Spalten mit deren Inhalt zu ersetzen – intuitiv, nicht wahr?

Anschließend folgt die Umbenennung, indem man in der linken Spalte den gewünschten Bezeichner einträgt und [Enter] drückt. Der screenshot zeigt, dass die erste Spalte (given_name) bereits umbenannt wurde und die zweite Spalte kurz vor der Umbenennung steht.

Von nun an sind die Spalten in diesem Element nur noch unter dem neuen Namen zu finden – keine Spur mehr von den alten Bezeichnern! Dies bezeugen auch die die Metadaten des folgenden Pfades:

Diese “gespenstische” Lösung realisiert die Spaltenumbenennung im Verborgenen und ist alles andere als transparent für den Nutzer. Viel schlimmer ist allerdings noch, dass die Zuordnung der Eingangs- zu den Ausgangsspalten überhaupt nicht mehr ersichtlich wird und man damit auf die Semantik der Bezeichner angewiesen ist. Semantik – igitt.

Im “daily business” bevorzuge ich übrigens wann immer es geht Weg #2 und dort wo mir nichts anderes übrig bleibt Weg #1. Ein wichtiges Kriterium beim Entwickeln von ETL-Paketen ist aus meiner Sicht die Lesbarkeit und Verständlichkeit, denn eher früher als später wird sich ein anderer Entwickler oder gar der Kunde mit Ihrem Paket auseinandersetzen müssen/wollen – erst dann zeigt sich wie lesbar Sie entwickelt haben!

Und? Alles kalter Kaffee für Sie? Oder war doch etwas Neues dabei? Haben Sie noch weitere Lösungen? Wie handhaben Sie die Spaltenumbenennung in Ihrem “daily business”? Ich freue mich auf Ihr feedback!

In den von uns gebauten Integration Services Paketen ist der Lookup der wohl am meisten genutzte Task und im Grunde macht er seine Arbeit auch meistens gut und schnell. Spätestens wenn man während des ETLs jedoch fortlaufende Werte wie z.B. das Einkommen eines Kunden in Gruppen übersetzen will, stößt man mit dem Lookup an seine Grenzen. Diese Artikel-Reihe beschreibt zwei Workarounds, mit denen sich dieses Problem mit dem Lookup trotzdem bewältigen lässt und schlägt zum Schluss eine wesentlich performantere Lösung mittels Script Task vor.

Zur besseren Nachvollziehbarkeit bezieht sich das folgende Szenario auf die Microsoft Demo-Datenbank AdventureWorksDW2008, die unter http://msftdbprodsamples.codeplex.com/ heruntergeladen werden kann. Alle Screenshots basieren auf dem SQL Server 2008, die beschriebenen Vorgehensweisen lassen sich aber auch mit dem SQL Server 2005 realisieren.

Ausgangssituation

In den Quelldaten für die Kunden gibt es eine Spalte, die das jährliche Einkommen der jeweiligen Kunden enthält (wie ein Fahrradhändler an solche Informationen kommt ist nicht nur Ihnen ein Rätsel). Um nach dieser Information später im Cube bzw. den Berichten besser auswerten zu können, sollen mehrere Einkommensgruppen entstehen, in die die einzelnen Kunden einsortiert werden müssen. Die dafür nötige Zuordnungstabelle hat folgenden Aufbau:

Um den “normalen” ETL-Aufwand für dieses Beispiel möglichst gering zu halten, nutzen wir als Quelldaten für die Kunden bereits die Kundendimension des DWHs, in der alle relevanten Kundeninformationen bereits zusammen vorliegen. Diese sollen nun um die IncomeGroupID aus unserer Zuordnungstabelle ergänzt werden.

Lösung mittels Lookup – Ohne Caching

Die hohe Geschwindigkeit des Lookups resultiert vor allem daraus, dass alle dem Lookup zu Grunde liegenden Daten bereits vor der eigentlichen Ausführung in den Hauptspeicher geladen werden und das Nachschlagen der Einzelwerte dann direkt im RAM erfolgen kann. In diesem Modus kann man aber leider nur exakt gleiche Werte wiederfinden. Eine Zuordnung zu einem Bereich, wie er in diesem Fall nötig ist, ist mit eingeschaltetem Caching nicht möglich. Da aber der Weg zum Ziel auch bei ausgeschaltetem Caching alles andere als intuitiv ist, wollen wir uns das doch mal genauer anschauen.

Die Quelldaten werden 1:1 aus der Tabelle DimCustomer gelesen, mit dem Lookup um die zusätzliche Spalte ergänzt und ins Ziel (hier ins Nirvana) geschrieben. Der Lookup ist dabei folgendermaßen konfiguriert:

Der Cache muss also wie gesagt deaktiviert werden.

Als Quelle werden die untere und obere Grenze und die daraus resultierende GruppenID benötigt.

Die Verknüpfung wird erst einmal so eingerichtet, als ob ein Standard-Lookup über eine Spalte eingerichtet wird, damit wir die Ergebnisspalte auswählen können.

Erst unter dem Punkt “Erweitert” kommt dann die eigentliche Besonderheit ins Spiel. Durch das deaktivieren des Caches, können wir eine benutzerdefinierte Abfrage angeben, die für JEDE! Zeile des Datenflusses ausgeführt wird. Diese kann dann wie gezeigt parametrisiert werden. Resultat ist also, dass nicht mehr nach einem speziellen Wert gesucht wird, sondern der Ausdruck “YearlyIncome BETWEEN IncFrom AND IncTo” ausgewertet und die gewünschte GruppenID zurückgegeben wird.

Um das Ergebnis begutachten zu können, hängen wir hinter den Lookup einen Datenviewer mit den wichtigsten Spalten und lassen das ganze laufen.

Wie man sieht erhalten wir das gewünschte Ergebnis. Allerdings deutet sich bereits bei dieser sehr kleinen Datenmenge an, dass die Geschwindigkeit nicht gerade Rekordverdächtig ist. Für kleine bis mittlere Datenmengen ist diese Lösung aber durchaus brauchbar.

Demnächst werden wir uns dann noch mit zwei weiteren Alternativen beschäftigen, die je nach Szenario eine bessere Performance erreichen können.

In einem guten DWH-System existiert immer ein Staging-Area. Gekoppelt an die Staging-Area ist dann das DWH selbst. Zumeist werden dann noch zusätzlich die Daten vom DWH in DataMarts überführt oder aber direkt von der Staging-Area in DataMarts. Die direkte Überführung der Daten von der Staging-Area in DataMarts ist eine relativ junge Technologie, da der Trend weg von den Mega-DWHs geht, welche in der Vergangenheit oft mit viel Mühe und Know-How geschaffen wurden, dann aber wenig erfolgreich waren.

Gründe für einen DataMart sind beispielsweise vom Gesamtprozess nur bestimmte inhaltliche Bereiche abzubilden oder eine Datenstruktur für multidimensionale Datenbanken (Cubes) bereitzustellen. Weitere Gründe für DataMarts sind  beispielsweise eine höhere Performance zu erzielen, da die Rechnerleistung auf einen anderen Rechner verlagert werden kann oder den Zugriffsschutz besser zu organisieren. In einem dedizierten DataMart könnten beispielsweise nur die Kennzahlen beherbergt werden, welche die Manager, die Geschäftsführer, kurz: die Entscheider, interessieren. Das sind Kennzahlen wie Wachstum, Umsatz oder Verkauf. Auf diesem DataMart könnte man dann auf einfache Art den  Zugriffsschutz für die entsprechende Anwendergruppe einstellen. Ein weiterer DataMart könnte dann Kennzahlen wie Lagerbestand oder Bestellmengen enthalten. Hier wären die Anwender Mitarbeiter aus dem Einkauf, die Bestellungen planen und ausführen. Auch hier kann der Zugriffschutz für diesen DataMart und diese Anwendergruppe auf einfache Art konfiguriert werden. Würde man ein großes DWH verwenden, das sämtliche Kennzahlen beherbergt, müsste man die Autorisierung im DWH auf Tabellenebene lösen. Bei einem DataMart löst man ihn  einfach auf Datenbankebene.

In einem DWH-System werden die Daten zumeist nachts aus den Datenquellen geladen. Dieses Verfahren ist üblich und wird meist deshalb angewendet, um den Produktionsprozess nicht zu belasten. Das Abfragen der Daten nimmt bei großen Systemen meist eine geraume Zeit in Anspruch. Zunächst wandern die Daten in die Staging-Area. Sie werden aus den Quellsystemen unbearbeitet, meist 1:1 übernommen. An dieser stellt sich die Frage, warum man die Daten erst in eine Staging-Area überführt und danach erst in das DWH aberführt. Warum nicht sofort in das DWH? Der Grund hierfür ist, dass der zeitaufwendigste Prozess meist das Laden der Daten in das DWH ist. Man würde so den zeitaufwendigsten Prozess direkt auf das Quellsystem koppeln. Die Devise lautet aber: Zuerst die Daten aus den Quellsystemen extrahieren und in die eigene Staging-Area ablegen. Dies sind nämlich in den meisten Fällen nur simple SELECT-Statements ohne aufwendige Verarbeitung. Also erst die Daten schnell aus dem Produktivsystem holen, in der Staging-Area ablegen und dann sich Zeit lassen zum Aufbereiten im DWH. Somit ist man schnell vom Quellsystem unabhängig.

Ein weiterer Grund, ein Staging-Area einzuführen, ist die Wartbarkeit. Wenn es viele unterschiedliche Datenquellen gibt, welche alle unterschiedliche Abfragesprachen voraussetzen, müsste der ETL-Prozess zum Laden ins DWH auf jeden Datenquellentyp angepasst werden und man käme mit simplen SELECT-Statements auf die Datenquellen nicht mehr aus. Unter Umständen müsste man hier für jeden Quellentyp erweiterte SQL-Syntax anwenden. Es ist aber viel einfacher zuerst alle Daten mit einem einfachen SELECT-Statement, das syntaxmäßig weitgehend einheitlich ist, in die Staging-Area zu holen und anschließend den aufwendigeren ETL-Prozess zum Laden der Daten in das DWH auf der für den Programmierer vertrauten Datenbank und Programmierplattform durchzuführen.

Ralph Kimball, ein führender Visionär in der Data-Warehouse-Industrie, beschreibt in seinem Buch „The Data Warehouse Toolkit“  sehr beeindruckend das DWH. Er vergleicht hier zum Beispiel die Staging-Area mit der Küche eines Restaurants, in dem rohe Produkte eingehen und dort in delikate Speisen verwandelt werden. Im DWH-System gehen auch rohe Daten in die Staging-Area ein, werden dort aufwendig bearbeitet und stehen am Ende als Resultat im DWH in aufbereiteter Form da, fit für Benutzer-Abfragen und anderem Konsum. In der Restaurantküche ist der Zutritt für die Gäste verboten und nur für das Küchenpersonal erlaubt, ebenso ist in einem DWH-System der Zugriff auf die Staging-Area nur für die Skilled-Professionals, also für die Programmierer, erlaubt. Im Restaurant wird normalerweise auch nicht in der Küche gegessen, ebenso werden die Analysen der Geschäftsführer auch nicht auf Basis der Staging-Area gemacht, sondern auf Basis des DWH.

Der Import der Daten in das Staging-Area funktioniert für jede zu importierende Tabelle in den meisten Fällen immer nach einem bestimmten Schema. Es werden die Daten aus der Quelle selektiert, evtl. transformiert und sogleich in die Staging-Area geladen.

So ist das Erstellen des Staging-Paketes oft eine reine Fleißarbeit. Der ETL-Programmierer steckt einmal Intelligenz in das Paket und überlegt sich, nach welchem Schema er die Daten importiert. Er erstellt für eine Tabelle exemplarisch einen Datenfluss. Anschließend erstellt er die 99 Datenflüsse für die 99 restlichen Tabellen im System auf gleichem Wege. Ändert sich etwas am Schema, dann macht der ETL-Programmierer die Änderungen an einem Datenfluss und natürlich wieder an den 99 restlichen Datenflüsssen.

Da der Mensch aber leider von Natur aus nicht für monotone, sich wiederholende Arbeit geschaffen ist, er dabei manchmal Fehler macht und langsam ist, kann hier ein Computerprogramm sein idealer Helfer werden, das ihm ein Staging-Paket automatisch generiert. Wie das funktionieren kann, wird nun beschrieben:

Meine Idee hierbei ist, dass der ETL-Programmierer zuerst ein Template-Paket erstellt, in dem ein Template-Datenflusstask liegt. Nach Erstellen des Template-Paketes legt er die Metadaten (Quelltabellennamen, Zieltabellennamen, Feldnamen, etc.) der restlichen zu importierenden Daten fest. Der letzte Schritt ist die automatische Generierung des Staging-Paketes. Das Programm hierfür öffnet das Staging-Paket, fragt die Metadaten für die restlichen Tabellen vom ETL-Programmierer ab und generiert anschließend die Datenflüsse für die restlichen definierten Import-Tabellen.

So gliedert sich der Arbeitsablauf also in drei Schritte:

1. Anlegen des Template-Paketes
2. Definieren der Metadaten für alle Import-Tabellen
3. Ausführung der automatischen Generierung

Das Template-Paket soll hier in einem Beispiel ganz einfach gehalten werden. Es hat eine einen Template-Datefluss mit einer OLE-DB-Quelle und einem OLE-DB-Ziel. Hier werden die Daten in der AdventureWorks-Datenbank von der Tabelle [Person].[Address] in die Tabelle [Person].[Address_Staging] transferiert. Die Staging-Tabelle wurde vorher mit dem CREATE-TABLE-Befehl angelegt und hat nahezu dieselben Metadaten wie die Orginaltabelle. 

Nach dem das Template-Paket angelegt wurde und dort der Template-Datenfluss eingefügt wurde,  kann nun mit dem Definieren der Metadaten und der automatischen Generierung  begonnen werden. Die Definition der Metadaten könnte der Programmierer auf verschiedene Weise bewerkstelligen. Er könnte die Metadaten beispielsweise als XML-Dokument an das Generierungsprogramm übergeben oder in einer Maske die Metadaten dort abfragen. Darum geht es aber in erster Linie nicht, sondern es geht um das Verfahren zur Kopie eines ETL-Datenflusstask. Hierzu muss das Generierungsprogramm drei wesentliche Dinge vollführen:

1.       Es muss das Template-Paket öffnen und den Template-Datenflusstask in seinen Hauptspeicher einlesen.

2.       Es muss die Metadaten für die zu importierenden Tabellen kennen.

3.       Es muss automatisert den Template-Datenfluss kopieren und in den Kopien die Metadaten des Template-Datenflusstasks mit den Metadaten der jeweils zu importierenden Tabellen ersetzen.

Für die Generierung von ETL-Paketen wird der Namespace Microsoft.SqlServer.Dts angeboten. Hier findet der Programmierer sämtliche erforderliche Komonenten zur Generierung eines ETL-Paketes. Eine Prozedur zum Öffnen des Template-Paketes könnte folgendermaßen aussehen:

Hierzu gibt es die Routine LoadPackage, welche das Paket vom Dateisystem in den Hauptspeicher des Programms lädt. Anschliessend muß der Template-Datenflusstask im Template-Paket lokalisiert werden. In meinem Beispiel hat der Template-Datenflusstask den Namen df_test.In der Instanz Package der Klasse Package werden in der Kollektion Executables sämtliche ETL‑Elemente geliefert, welche in der Ablaufsteuerung vorhanden sind. Die Lokalisierung des Template-Datenflusstasks folgt in diesem Beispiel auf triviale Weise, indem der Name des Datenflusstasks abgefragt wird: if (th.Name == “df_test”). Anschliessend wird die Routine CopyDataFlow aufgerufen, an die der Template-Datenflusstask, das Paket, sowie der Name des kopierten Datenflusstask übergeben wird. 
In der Routine CopyDataFlow werden dann sämtliche ETL-Komponenten in dem Template-Datenfluss durchgeschliffen und die Kopierroutinen für jede ETL-Komponente aufgerufen.  Hierzu beherbergt die Kollektion ComponentMetaDataCollection alle ETL-Komponenten (Quelle, Transformationen, Ziele) des Template-Datenflusstasks. Beim Durchschleifen dieser Kollektion muss dann abhängig vom Objekttyp eine jeweilige Kopierroutine aufgerufen werden:   Nachdem die ETL-Komponenten in den neuen Datenfluss kopiert wurden, müssen ebenso die Datenflussverbindungen (Pfade) in den neuen Datenfluss kopiert werden:

Hier ist ein Ausschnitt aus der Routine CopyComponentOleDB zu sehen:

Hier werden alle Metadaten sowie Eigenschaften der Template-OleDB-Kompnente in die neue OleDB-Komponente kopiert. Sollen diese überschrieben werden, muss man dies an entsprechender Stelle tun. Alle CustomProperties werden in der Kollektion CustomPropertyCollection durchgeschliffen und in den neuen Datenflusstask kopiert.

Möchte man nun im neuen Datenfluss Metadaten ändern (zum Beispiel Quelltabellennamen oder Zieltabellennamen)  muss man es genau hier tun:

Ebenso kann man hier über die Kollektionen OutputCollection und InputCollection die Feldnamen konfigurieren.

Nachdem der neue Datenfluss angelegt ist und die Metadaten eingestellt sind, wird das Paket unter neuem Namen abgespeichert:

Hierzu benutzen wir die Routine SaveToXml und nun steht uns im Dateisystem ein um den neuen Datenflusstask erweitertes ETL-Paket zur Verfügung:

Wer schon einmal ein bestehendes DWH inkrementell befüllt hat, weiß um die Problematik der langsam veränderlichen Dimension (kurz: SCD). Dabei handelt es sich um meist kleine Veränderungen, die Datensätze einer Dimension betreffen. Dabei lassen sich viele Spezialfälle unterscheiden und in der Regel erfordert der Umgang mit einer SCD viel Fingerspitzengefühl.

Die SQL Server Integration Services bieten dem Benutzer daher einen SCD-Wizard, in dem er schnell und unkompliziert die veränderliche Dimension bearbeiten kann. Wer ihn schon einmal benutzt hat, kennt aber die beiden Hauptärgernisse:

1. Änderungen an der Komponente zerstören schnell die mühsam erstellten Beziehungen zu folgenden Komponenten.
2. Größere Dimensionen werden äußerst unperformant behandelt.

So wird die SCD Komponente oft von workarounds abgelöst und fristet ihr Schattendasein zwischen Skript-Task und SQL-Befehlen.

In den vergangenen Monaten wurde auf Microsofts Community-Plattform Codeplex ein Projekt namens KimballsMethodSCD entwickelt, welches der Slowly Changing Dimension in den SSIS zu neuem Glanz verhelfen soll. Autor der Komponente ist Todd McDermid; als Pate für den Projektnamen stand Ralph Kimball ein, dessen SCD-Best Practices aus seinem Buch The Data Warehouse Toolkit signifikanten Einfluss auf die Entwicklung der Komponente hatten. Aber nun zum Addon: die Installation gestaltet sich dank msi-Installer problemlos; die KimballMethodSCD Komponente ist dann als Datenfluss-Komponente verfügbar. Eine erschöpfende Beschreibung der Fähigkeiten der Komponente würde genügend Stoff für zehn Blog-Einträge liefern, daher seien an dieser Stelle die „Perlen“ erwähnt:

- Umsetzung der Best Practices nach Kimball: unknown member Unterstützung; Angabe eines Änderungsgrundes als Spalte für geänderte Spalten; einfaches Row Auditing für Einfüge- und Updateoperationen
- die existierende Dimension wird aus einem Datenfluss heraus gelesen statt aus einem Connection Manager; dies ermöglicht mehr Flexibilität und besseres Cacheverhalten
- Änderungen wirken sich nicht zerstörerisch auf darunterliegende Komponenten aus
- flexible Spaltenvergleiche sind möglich: z.B. können Spalten Case-(un)sensitiv und Space (un)sensitiv verglichen werden
- eine Performancesteigerung gegenüber der Standard Komponente um den Faktor 100 (!) kann bei großen Dimensionen ohne weiteres erreicht werden

Während die ersten Punkte im Wesentlichen Designhilfen für den Entwickler darstellen, ist die Performancesteigerung ein netter Nebeneffekt der guten Implementierung des Addons: durch die Nutzung multipler Threads und einer Sortieroptimierung sind Ausführungszeiten möglich, die mit dem Standard Wizard undenkbar sind. Dieser nutzt einen langsamen Row-by-Row Lookup auf die Dimensionstabelle, während der KimballlMethod SCD die gesamte Tabelle in einem Stream einliest.

Obiges Beispiel, welches neben dem Addon auch als SSIS-Solution zum Download angeboten wird, zeigt den imposanten Performancegewinn: Eine Dimensionstabelle wurde mit 120.000 Datensätzen geladen. Anschließend wurden die Quelldatensätze dahingehend manipuliert, dass 33 SCD1 Änderungen und 42 SCD2 Änderungen entstanden, wodurch ein winziger Bruchteil der Dimensionsdatensätze geändert werden mussten. Bei den Ausführungszeiten zeigte sich, dass der SSIS Standard Wizard 29 Minuten brauchte, der KimballMethod SCD hingegen nur 15 Sekunden (!). Zwar musste das ausführende System einen 10fach gestiegenen Hauptspeicherbedarf kompensieren, der Anwender wurde aber mit einer 118fachen Leistungssteigerung belohnt.

Die Ausführungszeiten, die nach Ralph Kimballs Best Practices erweiterte Funktionalität und letztlich auch die Einfachheit der KimballMethod SCD machen diese zu einer echten Alternative zum Standard Wizard und verhelfen der etwas eingestaubten Slowly Changing Dimension in den SSIS zu neuem Glanz.

Bei der Integration verschiedenster Quellen mit Hilfe der Integration Services des SQL Server 2005/2008 kommt es durchaus vor, dass man es mit ODBC-Quellen zu tun hat, die man nur mit Hilfe einer DataReader-Verbindung auslesen kann. Möchte man seine Abfragen an die Quelle parametrisieren, stößt man allerdings schnell an die Grenzen des DataReaders: während Anfragen an OLE DB-Quellen ohne weiteres parametrisiert werden können, gibt es hier keinen offensichtlichen Weg.
Folgendes Beispiel illustriert das Problem: die Einschränkung auf das SellStartYear soll hier über einen externen Parameter gesteuert werden.

Der Editor bietet weder die direkte Parametrisierung der Anfrage, noch eine indirekte durch Übergabe eines Strings als SQL-Kommando.
Abhilfe schafft hier nur folgender Trick:
Der Datenfluss, in dem die Quelle enthalten ist bietet in seinen Eigenschaften die Konfiguration verschiedener Werte über Expressions.

 
Hier lassen sich unter anderem die SQL-Kommandos aller enthaltenen Quellen über Expressions steuern. In unserem Beispiel wäre das die Eigenschaft [Data Reader Source].[SQLCommand]. Zu beachten ist hierbei nur, dass die Name-Eigenschaft der Quelle (in diesem Fall „Data Reader Source“) statisch sein muss. Nun kann man die SQL-Abfrage über den Expression-Editor beliebig parametrisieren.
 

In unserem Fall haben wir das Jahr in eine String-Variable ausgelagert, die wir beliebig dynamisch halten können.
Damit man nicht den Überblick verliert, bietet sich die Expression Highlight Funktion des BIDS-Helper an. Dieser markiert den Datenfluss nach erfolgreicher Konfiguration mit einem magentafarbenen Dreieck. So sieht man sofort, hinter welchen SSIS-Objekten sich Expressions verbergen.

 
Derselbe workaround funkioniert auch für die ADO.NET-Quellen in den Integration Services des SQL Server 2008. Hier wurde zwar der Editor optisch auf das Level der OLE DB-Quellen gebracht, an der fehlenden Funktionalität bezüglich der Parametrisierung hat sich aber leider nichts geändert.