Dieser Blogbeitrag leistet Hilfe bei der Aufnahme einer Kontenstruktur als Dimension in ein OLAP-Modell. Bei Kontenschemata handelt es sich häufig um eine Parent-Child-Struktur. Auf Grund dessen wird im Nachfolgenden ein SQL Skript präsentiert, welches aus einem gegebenen Kontenschema eine Parent-Child-Struktur erstellt, welche als Quell-Tabelle bzw. -View für das Modell genutzt werden kann. Zusätzlich folgt eine Anleitung zur Eingabe der PC-Dimension in DeltaMaster ETL.
SQL Skript für die Transformation des Kontenschemas
Aufbau der Ausgangsstruktur
Abbildung 1 zeigt einen gekürzten Auszug aus einem Beispiel für die Ausgangsstruktur eines Kontenschemas, welches im Folgenden zur Demonstration der Transformation in eine Parent-Child-Struktur genutzt wird. Gegeben ist das Konto, eine Bezeichnung dessen und eine Spalte Unterkonten, welche eine Aufzählung der Kind-Elemente beinhaltet. Wie in Abbildung 1 zu sehen, ist in vier Fällen die Nummer der Konten nicht eindeutig, A) und B) treten doppelt auf. Zudem sind diese Konten in der Spalte Unterkonten nicht als direkte Kinder ihrer übergeordneten Konten (IV. und V.) angegeben, sondern es wird hier eine Ebene übersprungen. In einem ersten Schritt gilt es diese Irregularitäten zu beheben.
Abbildung 1: Ausgangsstruktur
Erläuterung der Zielstruktur
Durch die Transformation soll folgende Struktur erzielt werden, welche in Abbildung 2 zu sehen ist. Eine Spalte beinhaltet die Child-Elemente, eine weitere die Information über den zugehörigen Parent und außerdem soll eine Spaltenbezeichnung den Namen des Childs widergeben.
Abbildung 2: Zielstruktur
Die einzelnen Schritte der Transformation der Kontenstruktur
Nachfolgendend werden die einzelnen Schritte der Umformung der Tabellenstruktur erläutert. Um die Eindeutigkeit der IDs für die Kinder zu erlangen, wird eine erste View erstellt, beschrieben in 1.3.1. Im zweiten Schritt wird die Aufzählung der Unterkonten in eine Von- und eine Bis-Spalte aufgeteilt. Im letzten Schritt werden die Kind-Elemente, die dazugehörige Bezeichnung und das entsprechende Elternelement jeweils in eine Datenzeile gebracht. Um den Blogbeitrag nicht unnötig aufzublähen, wird im Folgenden auf ein Skript, welches sich im selbigen Ordner befindet, verwiesen.
Schritt 1: Herstellen einer eindeutigen ID
Wie oben beschrieben und in Abbildung 1 zu sehen, gibt es in unserem Beispiel die Konten A) und B) doppelt. Um diesen den richtigen Parent zuzuordnen, werden zunächst ein Zeilenzähler (s. Abbildung 3) und eine Spalte, welche die Info über das jeweilige Level des Kontos beinhaltet, hinzugefügt (exakt wird hier das relative Level vergeben, da im gegebenen Beispiel eine KPI-Kennzahl an oberster Stelle steht; dies wird hier der Einfachheit halber jedoch außer Acht gelassen).
Abbildung 3: Schritt 1 a: Hinzufügen eines Zählers und des Levels der Konten
Um die Eindeutigkeit der IDs herzustellen, werden die Nummern der Konten für die besagten Fälle mit dem jeweiligen Parent konkateniert. Da außerdem die hier als Level 2 deklarierten Elemente (im Bsp. die Konten A und B) in der Spalte Unterkonten übersprungen wurden, wird noch eine Spalte Parent_L2 eingefügt, um diesen den richtigen Parent zuzuordnen. Um die Datenzeilen, welche die Eltern der gesondert zu bearbeitenden Level2-Kinder (hier IV und V) beinhalten, im weiteren Vorgehen zu identifizieren, wird noch die Spalte Count_Children_L2 hinzugefügt. Das Ergebnis des ersten Schrittes ist in Abbildung 4 zu sehen.
Abbildung 4: Schritt 1 b: Hinzufügen der Parent_L2- und Count_Children_L2-Spalte
Sofern bereits eine eindeutige Struktur in der Nummerierung der Konten vorliegt und in der Aufzählung der Kind-Elemente keine Ebene übersprungen wurde, kann sofort mit dem zweiten Schritt begonnen werden.
Schritt 2: Aufspalten der Aufzählung der Unterkonten in Von und Bis
In der zweiten View wird die Information aus der Spalte Unterkonten in eine Von- und Bis- Spalte aufgeteilt. Das Ergebnis ist in Abbildung 5 zu sehen. Da im gegebenen Beispiel die Aufzählungen der Unterkonten mit einem Pipe voneinander getrennt sind, wird hier in einem ersten Schritt die vom DeltaMaster ETL mitgelieferte Tabellenwertfunktion F_BC_Split zu Nutze gezogen. Das Ergebnis des Splits ist in der Spalte SepString zu sehen. Im Folgenden werden bei Vorliegen nur eines Kontos in der Spalte SepString beide Spalten, Von und Bis, mit diesem Wert gefüllt. Im zweiten Teil des Unions wird im abgelegten Skript der Sonderfall für die Konten IV. und V. behandelt. Da hier die Angabe der direkten Kinder fehlt, werden hier anhand eines CrossApplys und der Nutzung der Count_Children_L2-Spalte die Von- und Bis-Spalten mit den direkten Kindern aufgefüllt.
Abbildung 5: Schritt 2: Aufspalten der Aufzählung der Unterkonten in Von und Bis
Schritt 3: Aufbau der Zielstruktur
Zu Beginn werden in einer CTE (im Skript: Konten2) die jeweiligen Kinder der Elternelemente abgefragt. Die Spalte Bezeichnung enthält hier jedoch noch den Namen des Parents. Mit Hilfe der zweiten CTE (Konten_L1) werden im ersten Teil des Unions die obersten Elemente, welche keinen Parent besitzen, selektiert. Im zweiten Teil des Unions werden die weiteren Parent/Child-Kombinationen erzeugt. Das Ergebnis enthält nun zudem die Bezeichnung der Child-Elemente, somit wurde die Zielstruktur (s. Abbildung 2) erreicht.
Anlegen der PC-Dimension in DeltaMaster ETL
In diesem Punkt soll noch abschließend mit Hilfe von Screenshots das Einpflegen der PC-Struktur als Dimension in DeltaMaster ETL gezeigt werden.
Zunächst wird der neuen Dimension der Typ ‚ParentChild‘ zugeordnet (Abbildung 6). Die TopLevelCaption kann hier je nach Anwendungsfall wie auch bei regulären Dimensionen ausgefüllt oder frei gelassen werden.
Abbildung 6: Bericht Dimensions (DeltaMaster ETL)
Im Bericht Levels muss nur eine einzige Ebene angelegt werden (Abbildung 7), welche sich aus der Child-Spalte speist (Abbildung 8).
Abbildung 7: Bericht Levels (DeltaMaster ETL)
Abbildung 8: Bericht Level source columns (DeltaMaster ETL)
Des Weiteren muss der Parent als Attribut der Kind-Elemente angebunden werden (Abbildung 9-10). Hier ist essentiell, dass in der Spalte Create hierarchy ‚ParentChild‘ ausgewählt wird.
Abbildung 9: Bericht Level attributes (DeltaMaster ETL)
Abbildung 10: Bericht Level attribute source columns (DeltaMaster ETL)
Im Bericht Level attributes sind noch weitere Einstellungen für Parent-Child-Dimensionen machbar.
Im Dimensionsbrowser sieht das Ergebnis unserer Beispielstruktur wie folgt aus:
Abbildung 11: Ausschnitt aus der Dimension Kontenschema
