Parent-Child-Hierarchien haben gegenüber regulären Hierarchien Nachteile im Bereich der Performance. Um diese zu umgehen, können sie als ragged Hierarchie 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, haben wir 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 Beitrag 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 von Vorteil sein kann. Dazu gehören die Ungewissheit über die zukünftige Tiefe der Dimension, elementweise Rechenregeln (vergleiche hierzu den Blogbeitrag „Unäre Operatoren und Custom Rollup Formulas“) oder die Abbildung von hierarchischen Kennzahlen. Wenn diese Punkte jedoch nicht zutreffen, ist es ratsam, die flache Tabelle in eine sogenannte 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 viel 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.

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:

WITH CTE AS
(SELECT
	 P.ElementID		AS ElementID
	,P.ParentID		AS ParentID
	,1			AS Level
FROM [ParentChild] P

WHERE P.ParentID IS NULL  
UNION ALL
SELECT
	 P.ElementID		AS ElementID
	,P.ParentID		AS ParentID
	,C.Level + 1		AS Level
FROM	ParentChild P 

INNER JOIN CTE C
	ON P.ParentID like C.ElementID)

Hier das Ergebnis der CTE in einer Tabelle:

Die PC- Hierarchie ist zwar ungeordnet, aber dafür sind alle Ebenen durchnummeriert. Mit einem einfachen SELECT MAX(Level) FROM CTE ermitteln 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.

Wir haben uns 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:

SELECT 
	 L1.ElementID		AS Level_1_ID
	,xa_L2_ID.L2_ID 	AS Level_2_ID
	,xa_L3_ID.L3_ID 	AS Level_3_ID
FROM 
ParentChild L1 

LEFT JOIN ParentChild L2 
	ON L1.ElementID = L2.ParentID 
			
CROSS APPLY (SELECT ISNULL (L2.ElementID, L1.ElementID) AS L2_ID) xa_L2_ID  

LEFT JOIN Parentchild L3 
	ON L2.ElementID = L3.parentID 
			
CROSS APPLY (SELECT ISNULL (L3.ElementID, xa_L2_ID.L2_ID ) AS L3_ID) xa_L3_ID  
				 
WHERE (L1.ParentID IS NULL)

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ächsttiefere Ebene der Dimension. Hierbei wird beachtet, dass 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.

Beim Code für die Hiearchie-View fallen sofort die wiederholenden Muster auf. Die Ebenen der Spaltenabfrage sind durchgezählt, genauso 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.

IF (@FirstParent like N'NULL' OR @FirstParent IS NULL) 
	SELECT @SQL_Code = 'IS NULL'
ELSE IF @FirstParent = @Childcolumn 
	SELECT @SQL_Code = 'like L1.'+@FirstParent+ ''
ELSE IF (@FirstParent NOT LIKE N'NULL' OR @FirstParent IS NOT NULL)
	SELECT @SQL_Code = 'like '+@FirstParent+ ''

;SET @Verify_The_Depth =
		'WITH CTE AS
		(SELECT
			'+@ChildColumn+
			','+@ParentColumn+'
			 ,1			AS [Level]
		 FROM 
			'+@TableName+' L1
		 WHERE 
			L1.'+@ParentColumn+' '+@SQL_Code+'  
		 UNION ALL
		 SELECT
			 L1.'+@ChildColumn+'
			,L1.'+@ParentColumn+'
			,C.[Level] + 1	  AS [Level]
		 FROM
			'+@TableName+' L1

		INNER JOIN CTE C
			ON L1.'+@ParentColumn+' like C.'+@ChildColumn+'
			AND L1.'+@ParentColumn+' <> L1.'+@ChildColumn+')

		SELECT @Depth = Max(Level) FROM CTE'

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.

EXECUTE sp_executesql @Verify_The_Depth, N'@Depth int output', @Depth OUTPUT

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 Ebene, ausgenommen die oberste, muss ein CROSS APPLY die nächste Spalte an 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 die 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 wird das Ergebnis der Code-Generierung gezeigt, 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:

L1.ElementID					AS Level_1_ID
	,ISNULL(L1.ElementBEZ,L1.ElementID)	AS Level_1_BEZ
	,xa_L2_ID.L2_ID				AS Level_2_ID 
	,xa_L2_BEZ.L2_BEZ				AS Level_2_BEZ 
	,xa_L3_ID.L3_ID				AS Level_3_ID 
	,xa_L3_BEZ.L3_BEZ				AS Level_3_BEZ

Die einzige Variable in diesem Code ist die Ebene, welche mit einer Variable hochgezählt wird. Was hierfür verwendet wird, ist eine 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.

LEFT JOIN Parentchild L2 
	ON L1.ElementID = L2.ParentID

CROSS APPLY (SELECT ISNULL (L2.ElementID, L1.ElementID) 	AS L2_ID) xa_L2_ID

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:

CROSS APPLY (SELECT CASE
			WHEN L2.ElementBEZ IS NULL AND L2.ElementID IS NULL
THEN ISNULL(L1.ElementBEZ,CONVERT(varchar,L1.ElementID))
			WHEN L2.ElementBEZ IS NULL
			THEN CONVERT(varchar,L2.ElementID)
			ELSE L2.ElementBEZ
			END 						AS L2_BEZ) xa_L2_BEZ

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.

Part 3: 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.

SELECT @Column_Title 
= REPLACE(REPLACE(STUFF(
(SELECT 													',' + Column_Title
	FROM #ColumnTable 
	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,''),'[',''),']','')

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.

SELECT 													',' + Column_Title
	FROM #ColumnTable

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.

SET @Flattening = (
			'CREATE VIEW '+@ViewName+' AS
			 SELECT '
				+@Column_Title+' 
			 FROM 
				'+@TableName+' L1 
			'+@Cross_Apply+' 
			 WHERE 
'+@SQL_CODE_WHERE+								
			)

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:

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 sind wir jederzeit offen. Ansonsten wünschen wir viel Spaß mit dem unkomplizierten Weg, eine Parent-Child-Hierarchie in eine ragged Hierarchie zu überführen.