Umwandlung von Parent Child – zu ragged Hierarchien

Parent-Child-Hierarchien haben gegenüber regulären Hierarchien Nachteile im Bereich der Performance. Um diese zu umgehen können sie als sogenannte ragged PDF DownloadHierarchie modelliert werden. Die Überführung einer Parent-Child-Hierarchie kann je nach Komplexität und Tiefe in einer mühsamen Handarbeit ausarten. Für die Erstellung der ragged Hierarchie muss die maximale Tiefe der Parent-Child- Hierarchie bekannt sein, um für jede Ebene eine eigene Spalte zu erstellen. Zusätzlich müssen Elemente, die nicht in der tiefsten Ebene vorhanden sind, für jede Ebene durchgeschrieben werden. Um diesen Prozess zu vereinfachen, habe ich eine dynamische Prozedur geschrieben, die nach der Eingabe der wichtigen Eckdaten automatisch die View für eine ragged Hierarchie erstellt.

Einmal in flach, bitte.

Die Idee zu diesem Blog entstand bei einer gewöhnlichen Modellierungsaufgabe. Es sollten einfache Dimensionen erstellt und in DeltaMaster ETL angelegt werden. Eine schnell erledigte Übung, im Normalfall jedenfalls. Es zeigte sich, dass alle Importtabellen in der Parent-Child-Form vorlagen.

Es gibt Fälle, in denen die Verwendung einer Parent-Child-Hierarchie (PC-Dimension) in der Modellierung vom Vorteil sein kann. Dazu gehören die Ungewissheit über die zukünftige Tiefe der Dimension, elementweise Rechenregeln oder die Abbildung von hierarchischen Kennzahlen. Wenn diese Punkte jedoch nicht zutreffen, ist es ratsam die flache Tabelle in eine so genannte ragged Hierarchie (quasi symmetrische Hierarchie) zu überführen. Dies hängt vor allem mit der Funktionsweise des Analysis Service zusammen, der auf Ebenen optimiert ist. Diese linearen Ebenen sind aber per Definition nicht in PC-Dimensionen vorhanden.

Die Überführung einer PC- in eine ragged Hierarchie ist mühsam und kann schnell zu einem zeitintensiven Unterfangen werden, gerade wenn die PC-Dimension sehr tief ist oder einfach sehr viele PC- Hierarchie im Modell verarbeitet sind. Um den Faktor Zeitaufwand zu umgehen, habe ich mich dazu entschlossen, diesen Prozess mit einer Prozedur zu automatisieren.

Im Folgenden wird zunächst der manuelle Weg der Überführung einer asymmetrischen PC- Hierarchie zu einer quasi-symmetrischen Hierarchie erläutert, um danach die dynamische Lösung darzustellen. Für die Veranschaulichung habe ich eine kleine Beispieltabelle erstellt, welche eine relativ flache PC-Dimension beinhaltet.

ElementIDParentIDElementBEZ
1NULLEins
2NULLZwei
31Drei
42Vier
52Fünf
63Sechs
74Sieben
81Acht
9NULLNeun

Man erkennt das klassische Parent-Child-Schema mit der Parent-Spalte, der Element- oder Child-Spalte und einer zugehörigen, beschreibenden Spalte, in diesem Fall ElementBEZ benannt. Um eine PC- Hierarchie in eine ragged- Hierarchie umzuwandeln muss als erstes die maximale Tiefe der PC-Dimension bekannt sein. Bei diesem Beispiel könnte man die Tiefe durch einfaches Ablesen ermitteln. Nehmen wir aber an, dass sich in der Tabelle 10.000 weitere Zeilen befänden, dann wäre man mit händischen Ermittlung der Tiefe schnell am Ende seiner Geduld. Da wir uns mit SQL helfen können, lässt sich die Tiefe in so einem Fall mit einer Rekursion automatisch bestimmen.

Für die Erstellung der Rekursion ist es wichtig, dass die oberste Ebene der PC-Dimension eindeutig identifizierbar ist. Die Definition des obersten Elements ist in diesem kleinen Beispiel dadurch gegeben, dass die ParentID-Spalte eine NULL an der Stelle hat, wo die höchste Ebene der Dimension ist. In der Rekursion wird diese Ebene mit einer ‚1‘ festgelegt. Alle tieferen Ebenen haben gemeinsam, dass das Kind-Element mindestens einmal in der Element-Spalte und mindestens einmal in der Parent-Spalte auftauchen muss. Ist dies nicht der Fall, ist dieses Kind-Element in diesem Zweig die tiefste Ebene.

Die Rekursion wird mit einer Common Table Expression (CTE) gebaut, die mit sich selbst an der Stelle verbunden wird, an der die Parent-Spalte einer Ebene gleich dem Kind-Element der darüberliegenden Ebene ist. In Ebene eins ist das Kind-Element gleich dem Parent-Element der Ebene zwei (Die „2“ ist somit in Level 1 noch als ElementID aufgeführt wohingegen sie in Level 2 das Parent ist, Level2.Parent = Level1.Element). In Ebene zwei ist das Kind-Element gleich dem Parent-Element der Ebene drei usw. Ein möglicher Code für die rekursive Ermittlung der Tiefe kann wie folgt aussehen:

Hier das Ergebnis der CTE in einer Tabelle:

ElementIDParentIDLevel
1NULL1
2NULL1
9NULL1
422
522
743
312
812
633

Die PC- Hierarchie ist zwar ungeordnet, aber dafür sind alle Ebenen durchnummeriert. Mit einem einfachen SELECT MAX(Level) FROM CTE ermittelt wir eine Tiefe von Drei. Die Zieltabelle muss also mindestens drei Spalten für die Ebenen haben (ohne Bezeichnungen), um die PC- Hierarchie vollständig als ragged Hierarchie abbilden zu können. Nun gilt es die Dimensions-View zu erstellen.

Ich habe mich für die Verwendung von CROSS APPLYs entschieden, da diese eine repetitive Logik verfolgen die bei der automatisierten Codeerstellung für die dynamische Prozedur hilfreich ist. Der Code für unser Beispiel:

Dieses Beispiel ist lediglich auf die ID-Spalten beschränkt und die Bezeichnungen sind außen vorgelassen, um es lesbarer zu gestalten. Die Logik des Codes ist es, dass man die Tabelle „ein Level tiefer“ mit sich selber dort verbindet, wo eben (Level 1 und 2 Beispiel) L1.ElementID = L2.ParentID gilt. Jeder CROSS APPLY erstellt dabei die Spalte für die nächst-tiefere Ebene der Dimension. Hierbei wird beachtet, die Elemente über alle Ebenen durchgeschrieben werden, so dass am Ende auf der untersten Ebene alle Elemente stehen, die das Ende eines Astes sind. Das Ergebnis der Abfrage ist die fertig umgewandelte PC-Dimension.

Level_1_IDLevel_2_IDLevel_3_ID
136
188
247
255
999

Beim Code für die Hiearchie -View fallen sofort die wiederholenden Muster auf. Die Ebenen der Spaltenabfrage sind durchgezählt, genau so die LEFT JOINs. Auch die CROSS APPLYs verfolgen ein einheitliches Schema: ist die aktuelle Ebene NULL- also leer-, dann nehme das vorher ermittelte Element aus dem vorherigen CROSS APPLY. Um die View-Erstellung zu automatisieren, ist das Grundkonzept also gegeben. Das Code-Grundgerüst (welches wir eben erstellt haben), eine Zähl-Logik und was wir jetzt noch brauchen ist ein Weg, wie Code mit SQL geschrieben werden kann, ohne vorher zu wissen, wie viel Code eigentlich geschrieben werden muss. Dazu später mehr.

Der Aufbau der Prozedur

Die Prozedur ist in drei Teile aufgeteilt, die alle oben erklärten Schritte automatisiert abarbeiten. Zuerst muss die Tiefe der PC-Dimension ermittelt werden. Danach brauchen wir den Code für die Dimensions-View, um diesen im dritten Schritt schlussendlich auszuführen. Gehen wir die drei Parts Stück für Stück durch.

Part 1: Tiefe auslesen

Im ersten Teil wird die rekursive Tiefenausleselogik von weiter oben lediglich dynamisiert. Die Spaltennamen lassen sich leicht durch Variablen ersetzen. Die Herausforderung ist die Behandlung der verschiedenen First-Parent-Definitionen. Zu unterscheiden sind drei Aspekte:

1. Die Definition der obersten Ebene ist NULL
2. Die Definition der obersten Ebene ist ein variabler Inhalt (beliebiger Text oder Zahl)
3. Die oberste Ebene ist dadurch definiert, dass Parent = Element ist

Alle Definitionen erfordern ein leicht angepasstes SQL, welches der obere Block in eine Variable (@SQL_Code) schreibt, die, in den Code eingesetzt, die fehlerfreie Ausführung ermöglicht.

Die Tiefe muss in eine Variable (@Depth) übergeben werden, da diese im zweiten Block für die Erstellung des vollständigen Codes erforderlich ist. Mit sp_executesql und der Kombination von EXECUTE wird der in die Variable @Verify_The_Depth geschriebene Code ausgeführt und die Tiefe der PC-Dimension in die Variable @Depth geschrieben.

Damit ist die wichtige Information für die Codeerstellung der View schon vorhanden, nämlich die Anzahl der anzulegenden Ebenen.

Part 2: Code erstellen

Wie oben ersichtlich, zeigt der Code für die View der ragged Hierarchie ein klares Muster. Für jede, ausgenommen die oberste, Ebene muss ein CROSS APPLY die nächste Spalte and die View anfügen. Möchte man den Code also automatisiert schreiben, müssen für n Ebenen genau n Spalten und n-1 CROSS APPLYs vorhanden sein. Die Tiefe n wurde in Part 1 ermittelt.
Um eine variable Menge Text zu erzeugen, habe ich mich dazu entschlossen, diesen zunächst in zwei temporäre Tabellen zu schreiben. Dies hat den Grund, dass der SELECT – FROM Teil und der CROSS APPLYs zwei unterschiedliche Logiken haben. Eine Tabelle beinhaltet also den Code für die Spaltenabfrage und die andere Tabelle den Code für die CROSS APPLYs.
In diese beiden Tabellen wird nun pro Ebene der PC-Dimension jeweils ein Teil des SQL-Codes in eine Zelle geschrieben. Im Folgenden zeige ich das Ergebnis der Code-Generierung, da das dynamische SQL nur schwer zu lesen ist. Der gesamte Code befindet sich zum Nachvollziehen im Anhang.

Die Spaltennamen

Die oberste Ebene benötigt noch keinen CROSS APPLY, weswegen der Code der Spaltenabfrage hierfür gesondert in der Prozedur behandelt wird. Ab der zweiten Ebene bleibt das Muster der Spaltenabfrage bis zur letzten Ebene identisch, weswegen dieser Teil mit einer Schleife erzeugt werden kann. Als reines Textergebnis erhält man für den Select- Teil der Abfrage folgendes:

Die einzige Variable in diesem Code ist die Ebene, welche mit einer Variable hochgezählt wird. Was hierfür verwendet wird, ist ein WHILE -Schleife. Solange der Zähler noch kleiner als die Tiefe der PC-Dimension ist, füge SQL-Code in die temporäre Tabelle ein. Ist die maximale Tiefe erreicht, ist die Schleife beendet.

Die CROSS APPLYs

Die CROSS APPLYs von den ID-Spalten und den BEZ-Spalten sind unterschiedlich, da bei den Bezeichnungen einige Besonderheiten beachtet werden müssen. Der CROSS APPLY der ID besitzt eine einfache Logik, da es nur einen Fall zu beachten gilt: Wenn das aktuelle Element der Ebene NULL ist, also kein Element in dieser Ebene existiert, dann schreibe das Element der darüberliegenden Ebene fort.

Bei der Bezeichnung hingegen sind mehr Ausnahmen zu beachten. Im besten Fall ist die Bezeichnung der Ebene x vorhanden. Ist diese jedoch leer (beispielsweise wegen unvollständigen Stammdaten), dann soll die ID in die Bezeichnung eingefügt werden. Ist in der Ebene x aber kein Element vorhanden, die ID also auch leer, dann muss die Bezeichnung (falls vorhanden) aus der darunter liegenden Ebene x-1 verwendet werden. Ist die Bezeichnung aus Ebene x-1 auch leer oder nicht vorhanden, so schreibe die ID der Ebene x-1 fort. Der Code für die CROSS APPLYs der Bezeichnung sieht im Endergebnis exemplarisch wie folgt aus:

Erstellung Auch dieser Code wird mittels der Schleife so häufig in die temporäre Tabelle geschrieben, bis die Tiefe der PC-Dimension erreicht und somit die richtige Anzahl an CROSS APPLYs geschrieben wurde.

Part3 Erstellung der View

Die benötigten Textblöcke stehen nun in den temporären Tabellen und sind so noch nicht für die Erstellung einer View zu gebrauchen. Um die Textblöcke aus der Tabelle in einen Fließtext zu überführen, kann man FOR XML PATH mit einer Kombination aus REPLACE und STUFF verwenden.


Was passiert hier eigentlich? Nehmen wir den Code auseinander. Zunächst werden alle Zellen aus der Column_Title – Spalte (hier liegen in der Prozedur die Spaltendefinitionen ab) ausgelesen und mit einem Komma versehen, da jede Spalte bei einer Select-Abfrage mit einem Komma getrennt sein muss. Dies macht dieser Code-Ausschnitt.

FOR XML PATH (‚ ‚ ) setzt die ausgelesenen Zell-Inhalte in einen zusammenhängenden Fließtext. Da in der ersten Zeile des Select-Parts aber kein Komma stehen darf, wird mittels der STUFF(Text, Startposition, Anzahl der zu löschenden Zeichen) – Funktion das führende Komma entfernt. Diese Funktion „stopft“ einen Textteil in einen anderen. In diesem Falle also alle ausgelesenen Spaltenabfragen, startend bei Position 1, wobei das erste Zeichen nach Funktionsdefinition gelöscht wird, so dass das führende Komma verschwindet. Was bleibt, ist der gewünschte SQL-Code-Abschnitt, den wir für die Dimensions-View benötigen.

Die gleiche Herangehensweise liest den Cross Apply- Code aus der anderen temporären Tabelle aus. Diese Textbausteine werden in zwei Variablen geschrieben (@Column_Title, @Cross_Apply). Damit sind alle Code-Bausteine in zwei Variablen gespeichert und die View kann erstellt werden.

Um den SQL-Code ausführen zu können, schreiben wir alle Teile zusammen in eine Variable. Die eben generierten Code-Bausteine befinden sich in den beiden Variablen @Column_Title (alle Spalten) und @Cross_Apply (alle CROSS APPLYs), was den Code für die View sehr überschaubar erscheinen lässt.




Die Variable @SQL_CODE_WHERE behandelt die drei Fälle der verschiedenen First-Parent-Definitionen (siehe Part 1). Führt man also die finale Variable aus, erscheint als Endergebnis die flachgeklopfte PC-Dimension in der Tabellenform

Level_1_IDLevel_1_BEZLevel_2_IDLevel_2_BEZLevel_3_IDLevel_3_BEZ
1Eins3Drei6Sechs
1Eins8Acht8Acht
2Zwei4Vier7Sieben
2Zwei5Fünf5Fünf
9Neun9Neun9Neun

Fazit

Sich wiederholende Aufgaben können theoretisch immer automatisiert werden. Dies kostet im ersten Schritt viel Zeit, spart danach aber umso mehr. Noch ist die Prozedur im Beta-Stadium, wurde aber schon erfolgreich auf diversen Kundenprojekten eingesetzt. Für Ideen bei der Weiterentwicklung bin ich jederzeit offen. Ansonsten wünsche ich viel Spaß mit dem unkomplizierten Weg, Parent-Child- Hierarchie in eine ragged Hierarchie zu überführen.

Schreibe einen Kommentar