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.