Das folgende Szenario bezieht sich auf die Microsoft Demo-Datenbank AdventureWorks 2008. Diese kann auf http://msftdbprodsamples.codeplex.com/ heruntergeladen werden. Die Umsetzung dieses Beispiels erfolgte mit Visual Studio 2008 und SQL Server 2008.

 

Ich möchte in diesem Blogbeitrag erklären, wie man Surrogate-Keys für Parent-Child-Hierarchien und die jeweilige Hierarchiestufe des Mitarbeiters anlegen kann. Dabei hilft uns die Angestellten Tabelle der AdventureWorks Datenbank. Diese findet man unter dem Tabellennamen Employee im Schema HumanResources. Der Mitarbeiter wird über die Spalte EmployeeID identifiziert und der Vorgesetzte über die Spalte ManagerID. Angestellte die keinen Vorgesetzten haben, sind mit entsprechender ManagerID auf NULL gesetzt. Dementsprechend ist der/sind die Mitarbeiter der/die keinen Vorgesetzten hat/haben, die höchste Stufe in unserer Organisation (in unserem Fall also der Chef).

 

 

Wir müssen in unserer Tabelle in der später unsere Daten landen sollen zunächst zwei zusätzliche Spalten einfügen. Das wäre zum einen die Spalte EmployeeBK und ManagerBK. Die Erklärung dazu folgt dann später im Text.

 

Außerdem benötigen wir in unserer Ausgangstabelle (Employee) eine Hilfsspalte (ich habe mich i.F. für istImportiert entschieden (NULL ist zulässig)) mit beliebigen Namen und dem Datentyp bit.

 

 

Als nächstes müssen wir dafür sorgen, dass alle Werte in dieser neuen Spalte den Wert 0 enthalten. Damit das Ganze gleich in SSIS passiert, ziehen wir uns einen SQL-Task auf unsere Arbeitsfläche und fügen den Befehl update [HumanResources].[Employee] set istImportiert = 0 ein.

 

 

Für den nächsten Schritt benötigen wir eine Laufvariable mit dem Datentyp Integer und dem Standardwert 0. Danach legen wir einen for-Schleifencontainer an. Nachdem Ihr die Eigenschaften festgelegt habt, legen wir einen Datenflusstask in den Container.

 

 

Vorweg schon mal die Ansicht, wie es später in unserem Datenflusstask aussehen wird:

 

 

 

Schritt 1

Wie üblich brauchen wir zunächst eine Quelle. Entscheidend ist hier natürlich, dass wir neben der ManagerID und der EmployeeID die Abfrage darauf einschränken, dass nur Daten geladen werden, wo der Wert für istImportiert = 0 ist.

 

 

Schritt 2

In der Komponente für bedingtes Teilen erfolgt die Überprüfung, welche Daten unsere Wurzelknoten (also unsere Manager mit dem Eintrag null) sind. Alle anderen Mitarbeiter werden als Knoten ausgegeben.

 

 

Schritt 3

Wir kümmern uns zunächst um die Mitarbeiter, die unseren Wurzelknoten repräsentieren. Dazu legen wir eine Abgeleitete Spalte mit dem folgenden Ausdruck an:

 

 

Im zweiten Teil von Schritt 3 müssen die anderen Hierarchiestufen überprüft werden. Hierfür legen wir eine Lookup Komponente an und verbinden diese mit der Ausgabe für Knoten aus der Conditional-Split Komponente. Damit wir es später nicht vergessen, ändern wir unter Allgemein die Einstellung wie nicht getroffene Einträge behandelt werden (auf Fehler ignorieren stellen). Unter Verbindung fügen wir ein SQL-Statement ein, das unsere Tabelle abfragt, in der letztendlich unsere Daten landen sollen. Jetzt kommt eine der erwähnten Spalten aus der Einleitung ins Spiel. EmployeeBK enthält später die Mitarbeiternummer welche aus EmployeeID kommt.

 

 

Wir verknüpfen die ManagerID mit EmployeeBK und lassen uns die EmployeeID (im SQL-Statement umbenannt zu EmployeeParentID) ausgeben. Überall wo wir keinen Treffer haben, wird NULL ausgegeben.

 

 

Schritt 4

Mit dem nächsten Conditional Split überprüfen wir, ob EmployeeParentID einen Wert erhalten hat oder nicht. Alle Zeilen für die es einen Treffer gibt, gehen in diesem Beispiel an die Ausgabe „Vater gefunden”.

 

 

Schritt 5

Mit Hilfe einer Union All Komponente verknüpfen wir die eingehenden Zeilen.

 

 

Schritt 6

Über die Multicast Komponente lassen wir uns Kopien des Datasets erzeugen.

 

Schritt 7

In der Komponente OLE DB Befehl fügen wir für unsere Quelltabelle Employee ein Update Statement ein.

 

 

Außerdem muss in den Spaltenzuordnungen EmployeeID mit Param_0 verknüpft werden.

 

 

Schritt 8

Als Erstes betrachten wir die Zuordnungen für unser Ziel (OLE_DST EmployeeNeu). Wie bereits erwähnt, stellt die EmployeeID in unserer Zieltabelle den Surrogate-Key dar. Außerdem muss darauf geachtet werden, dass unsere EmployeeParentID mit der ManagerID verknüpft wird.

 

 

Da wir uns immer noch im Schleifencontainer befinden, müssen wir im letzten Schritt dafür sorgen, dass kein Durchlauf mehr erfolgt, wenn unsere Laufvariable einen Wert ungleich 0 hat. Wir fügen eine Komponente „SQL ausführen” (SQL UPDATE Employee) ein (siehe Bild)

 

 

und legen folgende Einstellungen fest:

 

 

Wenn noch nicht geschehen, muss im ResultSet unsere Laufvariable ausgegeben werden.

 

 

Hierarchiestufe

Da ich abschließend wissen möchte, wie viele Vorgesetzte ein Mitarbeiter über sich hat, benötige ich eine zusätzliche Variable, die für jeden Durchlauf des Containers hochgezählt und dann in unsere Zieltabelle weggeschrieben wird. Hierfür lege ich zunächst eine Variable (iHierarchiestufe) mit dem Datentyp integer an (Startwert steht auf 1) und ergänze meine Zieltabelle um die Spalte Hierarchiestufe. Den Wert der Variablen füge ich mit Hilfe der Komponente „abgeleitete Spalte” in eine neue Spalte ein.

 

Außerdem muss ich noch das Mapping wie in Schritt 8 beschrieben ergänzen falls nicht schon geschehen.

 

Natürlich muss dem Schleifencontainer noch mitgeteilt werden, dass unsere Variable pro Durchlauf hochgezählt wird (AssignExpression). Die Einstellung dafür bitte dem Bild For-Schleifen-Editor entnehmen.

 

Abschließend schauen wir uns noch das Ergebnis für unsere Zieltabelle „EmployeeNeu” an. Im ersten Durchlauf finden wir genau einen Mitarbeiter der keinen Vorgesetzten hat. Er steht dementsprechend an erster Stelle in der Hierarchie.

 

Erster Durchlauf:

 

 

Im zweiten Durchlauf (zweite Ebene in der Hierarchie) haben wir schon mehr Mitarbeiter getroffen. Die ehemalige EmployeeID aus der Quelltabelle befindet sich nun in der Spalte EmployeeBK. Unter ManagerBK finden wir die zugehörige Kennzeichnung des Vorgesetzten (ManagerID aus der Quelltabelle). Unter ManagerID befindet sich der neu erzeugte Surrogate Key.

 

Zweiter Durchlauf:

 

 

Da alle weiteren Hierarchiestufen nach dem gleichem Schema gefüllt werden, spare ich mir an dieser Stelle die Fotos für die nächsten Durchläufe. Viel Spaß beim Ausprobieren.

 

Wenn ich euch mit dieser Anleitung helfen konnte, ihr weitere Fragen, Anmerkungen, Verbesserungsvorschläge oder Lösungen habt, freue ich mich auf Kommentare!