In einem Kundenprojekt hatte ein Kunde seine weltweite Vertriebsstruktur in einer mehrstufigen Hierarchie organisiert. Die aus den Vorsystemen gelieferte Hierarchie reichte dem Kunden allerdings nicht aus, da diese erstens nicht genügend Zwischenebenen hatte und zweitens die Vertriebsstruktur stetig erweitert und zum Teil modifiziert werden musste. Letzteres wurde in den Vorsystemen oft erst nach einiger Zeit vorgenommen.
Aus diesem Grund hatte der Kunde eine Tabelle angelegt, aus der er sich die Hierarchie aufgebaut hat. Die Hierarchie verfügt über eine zweistellige Anzahl an Ebenen und die Pflege erfolgt in einer Tabelle über das SQL Server Management Studio (SSMS). Genau dieser Umstand hat jedoch sehr häufig dazu geführt, dass die Befüllung der Tabellen in dem Snowflake-Schema für die OLAP Datenbank durch Fremdschlüssel-Verletzungen fehlgeschlagen ist.
Wie kam es dazu? Der erfahrene BI-Consultant ahnt es bereits und kann direkt über Los gehen und bei Abschnitt 2 weiterlesen. Für alle anderen wird die Problemstellung zunächst noch einmal am Beispiel des Demo-Modells der Chair AG von Bissantz & Company erläutert.
Detaillierung der Problemstellung
Abbildung 1 zeigt einen Ausschnitt der Datenquellensicht aus dem Demo-Modell der Chair AG. In diesem Ausschnitt sind eine gelbe und vier blaue Tabellen dargestellt. Die gelbe Tabelle ist die Faktentabelle, die blauen Tabellen bilden die Hierarchie einer Dimension ab. In allen Tabellen ist ein Schlüssel-Symbol zu sehen, das den Primärschlüssel der jeweiligen Tabelle angibt. Die Pfeile zeigen an, welche Tabelle von welcher Tabelle abhängt. Zwischen der Faktentabelle und der Tabelle T_DIM_05_04_Kunde besteht über das Feld KundeID eine Primär-Fremdschlüssel-Beziehung. Man spricht bei einem solchen Schema von einem Snowflake-Schema, das normalisiert ist.
Abbildung 1: Ausschnitt der Datenquellensicht der Chair AG
Werden alle vier Tabellen der Dimension in einer flachen, nicht normalisierten Tabelle gepflegt und wird aus dieser Tabelle das Snowflake-Schema aus Abbildung 1 befüllt, kann es sehr leicht zu Fehlern kommen. So auch in dem Beispiel aus Abbildung 2. Die PLZID 22043 kann aufgrund des Primärschlüssels in der Tabelle T_DIM_05_03_PLZ nur einmal angelegt werden, es gibt jedoch zwei Verweise, einen auf Hamburg und einen auf Homburg. Der Kunde würde nun möglicherweise den Schlüsseldienst rufen, der ihm seinen Tippfehler in der Pflege aufzeigt.
Abbildung 2: Tabelle in 2. Normalform in einem inkonsistenten Zustand
Lösung
Erstellen einer neuen Struktur
Die Lösung für das Problem ist einfach und naheliegend: Es muss eine bereits normalisierte Tabellen-Struktur erstellt werden, bei der jede Ebene gepflegt werden kann. Für das Snowflake-Schema wurde durch den DeltaMaster Modeler bereits eine solche Struktur erzeugt. Diese Tabellen sind eine sehr gute Vorlage, bei der per Skript nur noch die Tabellennamen geändert werden müssen.
Ein solches Skript kann einfach mit dem SSMS über die Funktion „Skriptgenerierung“ erstellt werden. Die folgenden drei Abbildungen zeigen die einzelnen Schritte.
Schritt 1: Generierung eines Skripts im SSMS über das Kontextmenü einer Datenbank sowie anschließende Auswahl der gewünschten Datenbankobjekte
Abbildung 3: Kontextmenü im SSMS
Abbildung 4: Objekte auswählen
Schritt 2: Auswahl des Speicherorts und Optionen (Erweitert)
Abbildung 5: Festlegung der Skripterstellungsoptionen
Über den „Erweitert“-Button (Abbildung 5) können viele sinnvolle Einstellungen vorgenommen werden, u. a. ob nur das Schema oder auch Daten „gescriptet“ werden sollen, ob die Existenz von Objekten vorher abgefragt werden soll oder ob abhängige Datenbank-Objekte mit erstellt werden sollen.
In dem generierten Skript muss anschließend lediglich per „suchen & ersetzen“ T_DIM durch T_S ersetzt werden und anschließend kann es in die Datenbank eingespielt werden.
Automatischer Schlüsseldienst
Darüber hinaus sollte eine Möglichkeit geschaffen werden, neben den Bezeichnungen auch die IDs umbenennen zu können. So werden beispielsweise bestimmte Vertriebs-Standorte für Planungen angelegt, ohne dass ihre spätere ID bekannt ist. Selbstverständlich möchte der Kunde Ist und Plan vergleichen können, wofür eine Änderung der IDs in der Dimension und in den Faktendaten notwendig ist.
Zum Glück haben auch hier die Entwickler des SQL Server mitgedacht und das Problem lässt sich sozusagen „out-of-the-box“ lösen. Das Schreiben von Update-Triggern ist nicht notwendig. Im Entwurfsmodus einer Tabelle – erreichbar über den Aufruf „Entwerfen“ im Kontextmenü einer Tabelle – kann das Kontextmenü aus Abbildung 6 aufgerufen werden. Nach einem Klick auf „Beziehungen“ öffnet sich das Fenster mit den Fremdschlüsselbeziehungen (siehe Abbildung 7).
Abbildung 6: Kontextmenü im Entwurfsmodus einer Tabelle
Auf der linken Seite des Fensters sind alle Fremdschlüssel der Tabelle aufgelistet. Auf der rechten Seite befinden sich unten die INSERT- und UPDATE-Spezifikation. Dort kann festgelegt werden, was nach einer Aktualisierung oder einer Löschung des zugehörigen Primärschlüssels passieren soll. In diesem Fall wird bei „Regel aktualisieren“ „Weitergabe“ gewählt, damit auch die Fremdschlüssel aktualisiert werden. Bei „Regel löschen“ wird in diesem Fall nichts weiter eingestellt, da die Primärschlüssel nicht gelöscht werden sollen, wenn eine Abhängigkeit besteht.
Abbildung 7: Fremdschlüsselbeziehungen einer Tabelle: INSERT- und UPDATE-Spezifikation
Das folgende SQL Statement wird durch diese Aktion generiert:
ALTER TABLE dbo.T_DIM_05_02_Gebiet ADD CONSTRAINT
FK_T_DIM_05_02_Gebiet FOREIGN KEY
(
RegionID
) REFERENCES dbo.T_DIM_05_01_Region
(
RegionID
) ON UPDATE CASCADE
ON DELETE NO ACTION
GO
DeltaMaster Pflegeanwendung
Zusätzlich wurde eine einfache Möglichkeit, die Daten pflegen zu können, gewünscht, da nicht jeder Mitarbeiter mit dem SSMS vertraut war. Dazu wurde eine DeltaMaster-Anwendung erstellt, mit der komfortabel die Hierarchie editiert werden kann.
Für eine solche Anwendung sind zwei Dinge erforderlich:
- Eine relationale DeltaMaster-Eingabeanwendung mit Eingabeberichten je Hierarchieebene
- INSERT, UPDATE und DELETE Prozeduren, die aus der Anwendung aufgerufen werden
Für die Erstellung einer relationalen Eingabeanwendung gibt es ein sehr ausführliches Dokument „Relationale Eingabe mit DeltaMaster“ in der Hilfe von DeltaMaster.
Für die Erstellung der Prozeduren haben alle, die den DeltaMaster Modeler einsetzen, eine geniale Hilfe. Der DeltaMaster Modeler liefert die Prozedur P_BC_Generate_DeltaMasterTableProc, mit der im Handumdrehen alle Prozeduren erstellt werden können. Der Aufruf sieht folgendermaßen aus:
EXEC P_BC_Generate_DeltaMasterTableProc 'View', 'Table'
Schnelle Lösung
Für alle, bei denen die Zeit ohnehin schon knapp ist, hat unser DeltaMaster-Modeler-Team noch ein weiteres Highlight eingebaut. Mit nur zwei Klicks lassen sich die Tabellen und Prozeduren für die DeltaMaster Pflegeanwendung erstellen. Einzig die Eingabeberichte müssen noch manuell eingefügt werden.
In der DeltaMaster-Modeler-Sitzung muss dafür im Bericht „Levels“ in der Spalte „Create rel. maint.“ eingetragen werden, ob für eine Ebene einer Hierarchie Objekte zur Bearbeitung in einer Pflegeanwendung erstellt werden sollen. Der Eintrag heißt „Record entry obj.“. Im Bericht „Parameter“ kann dann unter dem Eintrag „Relational maintenance objects creation level“ festgelegt werden, ob
- nur die T_S-Tabellen
- auch die V_S-Views
- oder auch gleich die entsprechenden Prozeduren
mit erzeugt werden sollen. Dabei muss jedoch beachtet werden, dass Views und Prozeduren bei jedem „Create Snowflake“ neu erstellt werden.
