Aufgabe eines jeden Data Warehouse (DWH) ist das Integrieren und Historisieren von Daten (Inmon, 1996, p. 33). Das gilt auch (und insbesondere) für die Data Vault Modellierung, da eine der Hauptanforderungen hier die Auditierbarkeit der gespeicherten Daten ist. Die goldene (Jedi-) Regel lautet: Das Modell ist dann valide, wenn sich daraus jederzeit der Zustand der Quelldaten wiederherstellen lässt. (Linstedt, Data Vault Basics, 2017). Historisiert werden im grundlegenden Data Vault Modell jedoch ausschließlich die deskriptiven Daten, die in den Satelliten gespeichert sind. Die Historie, der – in Links gespeicherten – Beziehungen geht im Regelfall verloren. Abhilfe schaffen die sogenannten Effektivitätssatelliten, die im Data Vault Standardwerk aber eher stiefmütterlich über etwas mehr als eine Seite abgehandelt werden (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, p. 145f).
Hier möchte ich das Problem zeitlicher Historisierung der Beziehungen im Data Vault darstellen, einen Lösungsweg aufzeigen und am Ende mögliche Beladungsmuster und die automatisierte Generierung von Effektivitätssatelliten und deren Lademustern diskutieren.
Darstellung des Problems
Zunächst geht es um die Grundlegende Funktion von Effektivitätssatelliten. Anhand eines einfachen Szenarios zeige ich wann sie zum Einsatz kommen (müssen). Das Beispiel dreht sich um eine einfache Quelltabelle, die lediglich einem (Kunden-) Betreuer eine Kostenstelle zuordnet:
Tabelle 1: Aufbau und Inhalt der Quelltabelle in der Stage
| BETR. | KOSTENST. | ERSTELLT | GEAENDERT | SEQ | LDT |
| A | 1 | 01.01.17 | 1 | 30.03.17 | |
| B | 2 | 01.01.17 | 2 | 30.03.17 | |
| A | 3 | 01.01.17 | 05.01.17 | 1 | 31.03.17 |
| B | 2 | 01.01.17 | 2 | 31.03.17 | |
| A | 1 | 01.01.17 | 06.01.17 | 1 | 01.04.17 |
| B | 2 | 01.01.17 | 2 | 01.04.17 |
Die Quelltabelle enthält außerdem Systemzeitstempel, die den Zeitpunkt der Erstellung, der letzten Veränderung oder der Löschung des Eintrages dokumentieren. Zu beachten ist, dass die Abbildung der Stagetabelle bereits die Daten aus drei Beladevorgängen enthält (die drei Ladevorgänge sind jeweils durch die Ladezeitstempel (LDT) zu unterscheiden).
Alle für diesen Beitrag verwendeten SQL-Skripte habe ich auf Github zur Verfügung gestellt (Cirkel, 2017).
Um die Sinnhaftigkeit von Effektivitätssatelliten zu erkennen kann man die Daten in der Stage nun in den Raw Vault laden. Dazu wird im Raw Vault das folgende Modell aufgebaut. Dabei werden die deutschen Bezeichnungen der Quelle ins Englische übersetzt:
Modell des Raw Vault, ohne Effektivitätssatelliten
Nach der Beladung ergibt sich folgendes Bild: Für die Betreuer A und B wurde jeweils eine Zeile im advisor-Hub angelegt. Ähnliches gilt für die Kostenstellen 1, 2 und 3. Die Kontextinformationen landen im Satelliten advisor_data, da sich die Informationen in der Quelltabelle direkt auf den Betreuer beziehen. Die Fremdschlüsselbeziehung zur Kostenstelle wird im Link advisor_cost_centre erfasst. Hierin befinden sich nach der Beladung drei Zeilen: Die Zuordnung von Betreuer A zur Kostenstelle 1, die Zuordnung von Betreuer B zur Kostenstelle 2 sowie die Zuordnung von Betreuer A zur Kostenstelle 3.
Tabelle 2: Inhalt des Links nach der Beladung
| HK_ADVISOR_CST_CNTR_L | LDT | HK_ADVISOR_H | HK_COST_CENTRE_H |
| 13E0CA04EA (A3) | 31.03.17 | 6DCD4CE23D (A) | 77DE68DAEC (3) |
| 01AE58AE2D (B2) | 30.03.17 | AE4F281DF5 (B) | DA4B9237BA (2) |
| 657B297DA8 (A1) | 30.03.17 | 6DCD4CE23D (A) | 356A192B79 (1) |
Und hier zeigt sich direkt das eingangs erwähnte Problem: Der während der letzten Beladung vollzogene Wechsel des Betreuers A zurück zur Kostenstelle 1 wird in unserem einfachen Modell nicht erfasst. Denn im Link befindet sich diese Beziehung ja bereits und wird dort kein zweites Mal eingefügt. Nachträglich lassen sich die Quelldaten mit den erfassten Daten im Raw Vault nun nicht wiederherstellen. Das Modell ist damit streng genommen invalide.
Um die fachliche Gültigkeit richtig abzubilden bräuchte man einen Satelliten mit den Gültigkeitsdaten der Beziehung am Link. Diese sogenannten Effektivitätssatelliten sollen nach Linstedt & Olschimke aber nur dann zum Einsatz kommen, wenn die Quelle eine fachliche Gültigkeit der Beziehung ausdrücklich definiert (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, p. 499ff). In unserem Beispiel ist dies eigentlich nicht der Fall. Man könnte aber die Kombination der technischen Erstellt- und Geändert-Zeitstempel dazu nutzen um daraus eine fachliche Gültigkeit zu konstruieren. Wie das funktioniert, wird nachfolgend beschrieben.
Abbilden fachlicher Gültigkeiten
In Ermangelung eines fachseitig definierten Gültigkeitszeitraums für die Beziehung kann auf die technischen Zeitstempel der Quelle zurückgegriffen werden. Das kann durchaus sinnvoll sein: Wenn ein Datensatz in der Quelle eingefügt wird und hier kein extra Attribut dessen fachliche Gültigkeit definiert, wird dieser Eintrag sicher auch im Produktivsystem direkt gültig.
Um die fachliche Gültigkeit der Beziehung zu erfassen, wird dem Modell ein Effektivitätssatellit hinzugefügt, der direkt am Link hängt:
Modell des Raw Vault mit Effektivitätssatelliten
Zur Beladung des Satelliten, wird nun von einem Driving-Key-Szenario ausgegangen. Im Beispiel ist advisor der Driving-Key (in Abbildung 2 zu erkennen an den dickeren Pfeilspitzen zum Link (Olschimke & Linstedt, 2017, p. 9f)). Wenn diesem Schlüssel eine neue Kostenstelle zugeordnet wird, soll das zu einer Anpassung der Gültigkeit im Satelliten führen (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, p. 119ff). Für das Beispiel werden die Erstellt- und Geändert-Zeitstempel der Quelle genutzt um daraus eine fachliche Gültigkeit zu interpretieren. Dabei wird der Geändert-Zeitstempel als Gültigkeitsbeginn der Beziehung verwendet. Ist dieser nicht gesetzt, wird der Erstellt-Zeitstempel genutzt.
Die oben durchgeführte Beladung sollte zu folgendem Zustand im Effektivitätssatelliten advisor_cst_cntr_bes führen:
Tabelle 3: Inhalt des Effektivitätssatelliten nach der Beladung
| HK_ADVISOR_CST_CNTR_L | LDT | LEDT | VALIDFROM | VALIDTO |
| 01AE58AE2D (B2) | 30.03.17 | 01.01.99 | 01.01.17 | 01.01.99 |
| 657B297DA8 (A1) | 30.03.17 | 31.03.17 | 01.01.17 | 01.01.99 |
| 657B297DA8 (A1) | 31.03.17 | 01.01.99 | 01.01.17 | 04.01.17 |
| 13E0CA04EA (A3) | 31.03.17 | 01.04.17 | 05.01.17 | 01.01.99 |
| 13E0CA04EA (A3) | 01.04.17 | 01.01.99 | 05.01.17 | 05.01.17 |
| 657B297DA8 (A1) | 01.04.17 | 01.01.99 | 06.01.17 | 01.01.99 |
Da sich die Beziehung des Betreuers B zur Kostenstelle 2 im Laufe der Beladung nicht ändert gibt es nur einen Eintrag im Satelliten. Als Gültigkeitsbeginn (VALIDFROM-Spalte) wird der Erstellt-Zeitstempel eingetragen. Die Gültigkeit ist unbegrenzt. So beginnt auch die Beziehung des Betreuers A zur Kostenstelle 1. Jedoch wird hier bei der nächsten Beladung eine Änderung der Zuordnung (auf die Kostenstelle 3) durchgeführt. Das resultiert in einem neuen Effektivitätseintrag mit Gültigkeitsbeginn am 05.01. Dieser Eintrag allein hätte aber eine Gültigkeitsüberschneidung zur Folge: Eine Auflistung aller gültigen Beziehung am 05.01. würde nun alle drei Einträge des Links (A1, A3 und B2) zurückgeben. Deshalb müssen nun die bestehenden Gültigkeiten für den Driving-Key (Betreuer) eingeschränkt werden. Es wird eine weitere Zeile im Link erzeugt, welche die Gültigkeit der Beziehung A1 auf den 04.01. (um 23:59:59 Uhr – hier sollte eine möglichst kleine Zeiteinheit von der Nachfolgergültigkeit abgezogen werden (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, p. 121)) beschränkt. Die dritte Beladung stellt dann wieder die Verhältnisse der ersten Beladung her. Im Satelliten führt das zunächst zu einem neuen Eintrag für die Beziehung A1, mit unbegrenzter Gültigkeit ab dem 06. Januar. Zusätzlich muss wieder die Gültigkeit der Vorgängerbeziehung (A3) beschränkt werden. Virtualisiert man die LoadEndDate-Spalte (LEDT) kommt die Beladung des Satelliten auf diese Weise mit „Insert-Only“-Operationen aus (Linstedt, Updates in Data Vault are DEAD! Finally, 2016).
Mithilfe dieses Satelliten ist es nun möglich die Historie der Quelle korrekt wiederherzustellen. Puristen werden dem entgegensetzen, dass hier bereits eine nicht unerhebliche Deutung der Quelldaten vorgenommen wird und der Effektivitätssatellit damit im Business Vault anzusiedeln ist. Dem würde entgegenstehen, dass der Business Vault per Definition jederzeit komplett löschbar und durch die Daten im Raw Vault wiederherstellbar sein soll. Das wäre in unserem Szenario aber nicht möglich. Roelant Vos beschreibt in seinem Blog einen Lösungsansatz, der es ermöglicht auf die Anwendung von „Soft Rules“ während der Beladung des Raw Vault zu verzichten und dennoch die Historie richtig zu erfassen (Vos , Virtualising your Data Vault – regular and driving key Link Satellites, 2014).
Erfassen von Beziehungsänderungen und Löschvorgängen mithilfe von Driving-Key LSAT
Roelants Ansatz kommt ohne die Auswertung zusätzlicher Datumsattribute aus. Es wird lediglich erfasst, ob eine Beziehung zum Ladezeitpunkt gültig war oder nicht. Der Satellit kommt deshalb mit einem einzigen Attribut aus, nämlich einem Boolean-Attribut mit genau dieser Information. Auch eignet sich diese Form der Effektivitätserfassung besser um physikalische Löschungen in den Quellsystemen zu erfassen (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, p. 501f).
Auch hier kommt wieder das Driving-Key-Szenario zur Anwendung. Der Unterschied ist, dass (außer des Beziehungszustandes in der Quelle) keine Attribute des Quellsystems ausgewertet werden. Diese Art Satellit ähnelt dem Aufbau und der Funktionsweise von Record Tracking Satellites (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, pp. 146-149). Nachfolgende Abbildung zeigt den Aufbau des Driving-Key LSATs:
Modell des Raw Vault mit Driving-Key LSAT
Die Beladung funktioniert sehr ähnlich wie die Beladung der Effektivitätssatelliten. Zunächst werden die Beziehungen A1 und B2 als aktiv markiert. Mit der zweiten Beladung ändert sich der Zustand der Beziehung A1 auf inaktiv und es wird die neue Beziehung A3 aktiv gesetzt. Mit der letzten Beladung ändert sich dieser Zustand wieder auf inaktiv und der Zustand für A1 auf aktiv. B2 verbleibt die komplette Zeit über im aktiven Zustand, hier sind keine weiteren Einträge nötig.
Tabelle 4: Inhalt des Driving-Key LSAT nach der Beladung
| HK_ADVISOR_CST_CNTR_L | LDT | LEDT | ACTIVEYN |
| 01AE58AE2D (B2) | 30.03.17 | 01.01.99 | Y |
| 657B297DA8 (A1) | 30.03.17 | 31.03.17 | Y |
| 657B297DA8 (A1) | 31.03.17 | 01.04.17 | N |
| 13E0CA04EA (A3) | 31.03.17 | 01.04.17 | Y |
| 657B297DA8 (A1) | 01.04.17 | 01.01.99 | Y |
| 13E0CA04EA (A3) | 01.04.17 | 01.01.99 | N |
Die komplette Beladung (wie oben erwähnt: Voraussetzung ist ein virtualisiertes LEDT) kann wieder ausschließlich mit INSERT-Operationen abgewickelt werden.
Problematisch ist dieser Ansatz bei historischen Auswertungen von Zeiträumen vor der ersten Beladung des Data Vaults. Da die Gültigkeiten hier noch nicht erfasst wurden. Eine Kombination beider Ansätze kann hier zielführend sein, da mithilfe der Driving-Key-Linksatelliten nun der Aufbau der Effektivitätssatelliten im Business Vault möglich wird. Wie das funktioniert, wird nachfolgend beschrieben.
Kombinieren der Driving-Key LSAT mit Kontextinformationen aus Satelliten zu einem Business Effektivitätssatelliten
Um Data Vault Puristen zufrieden stellen und gleichzeitig eine volle Historie auch vor der Erstbeladung des DWH anbieten zu können, bietet sich eine Kombination beider Satellitenformen an. Denn die fachlichen Gültigkeitsdaten der Beziehung (also im vorliegenden Fall die Erstellt- und Geändert-Zeitstempel der Quelle) speichert man natürlich trotzdem im Raw Vault, auch wenn sie nicht für die Erstellung der Driving-Key-LSATs genutzt werden. Im hier aufgebauten Beispiel werden diese Daten als Kontextinformationen zum Betreuer in den advisor_data Satelliten geschrieben. Diese Informationen und die Gültigkeitsangaben aus den Driving-Key-LSATs können nun in einem Computed Satellite im Business Vault miteinander kombiniert und dort wie gewöhnliche Effektivitätssatelliten abgefragt werden (Vos, Driving Keys and relationship history, one or more tables?, 2014). Das Datenmodell ändert sich dabei wie nachfolgend in Abbildung 4 gezeigt:
Modell des Raw Vault mit Driving-Key LSAT und Business Effektivitätssatellit
Der Business-Effektivitätssatellit kann vollständig virtualisiert sein. In Aufbau und Beladungsverhalten ähnelt er exakt dem oben beschriebenen Effektivitätssatelliten im Raw Vault.
Aufbau und Abfrage der Tabellen
Letztendlich sind Effektivitätssatelliten natürlich auch nur Entitäten, die im Data Vault angelegt und befüllt werden. Die meisten Data Warehouses sind mit RDBMS-Systemen realisiert und ein Effektivitätssatellit ist dann eine von vielen Tabellen einer Datenbank. In diesem Abschnitt möchte ich kurz auf den physikalischen Aufbau dieser Tabellen zu sprechen kommen, bevor es im nächsten Abschnitt an die Beschreibung der Lademuster geht.
Effektivitätssatelliten können sowohl an Links als auch Hubs anhängen. Der Aufbau entspricht dabei der von Dan Linstedt spezifizierten Struktur: Der Primärschlüssel besteht aus dem Schlüssel der übergeordneten Entität (Link oder Hub) und dem Ladezeitstempel (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, p. 112). Bei Effektivitätssatelliten zum Abbilden der fachlichen Gültigkeit ist außerdem das Gültigkeitsanfangsdatum Teil des Primärschlüssels. Der Satellit ist also multiaktiv (Linstedt & Olschimke, Building a scalable Data Warehouse with Data Vault 2.0, 2016, p. 141). Die Hashdiff-Spalte, die im Standardaufbau von Satelliten beschrieben ist, wird bei den Effektivitätssatelliten aber nur selten einen wirklichen Vorteil liefern. Die für diese Spalte aufgewendete Rechenzeit und den Speicherplatz kann hier also eingespart werden.
Entsprechend dem Modell kann die Abfrage der Effektivitätssatelliten stets nach gleichem Muster erfolgen:
SELECT h1.businesskey1 AS LeadingBKey
,h2.businesskey2 AS DependentBKey
FROM edw_data.geschaeftsobjekt1_h h1
JOIN edw_data.beziehung_l lnk
ON lnk.hk_geschaeftsobjekt1_h = h1.hk_geschaeftsobjekt1_h
JOIN edw_data.v_beziehung_es es
ON es.hk_beziehung_l = lnk.hk_beziehung_l
AND &technical_date BETWEEN es.ldt AND es.ledt
-- In most cases the line above can also be written as
-- AND es.ledt = DATE '9999-01-01'
AND &functional_date BETWEEN es.gueltigab AND es.gueltigbis
JOIN edw_data.geschaeftsobjekt2_h h2
ON h2.hk_geschaeftsobjekt2_h = lnk.hk_geschaeftsobjekt2_h;
Die Abfrage des Driving-Key LSAT unterscheidet sich kaum:
SELECT h1.businesskey1 AS LeadingBKey
,h2.businesskey2 AS DependentBKey
FROM edw_data.geschaeftsobjekt1_h h1
JOIN edw_data.beziehung_l lnk
ON lnk.hk_geschaeftsobjekt1_h = h1.hk_geschaeftsobjekt1_h
JOIN edw_data.v_beziehung_dks dks
ON dks.hk_beziehung_l = lnk.hk_beziehung_l
AND &technical_date BETWEEN dks.ldt AND dks.ledt
-- In most cases the line above can also be written as
-- AND dks.ledt = DATE '9999-01-01'
AND dks.istaktiv = 'Y'
JOIN edw_data.geschaeftsobjekt2_h h2
ON h2.hk_geschaeftsobjekt2_h = lnk.hk_geschaeftsobjekt2_h;
Damit ist der grundsätzliche Aufbau und das Abfragemuster der Effektivitätssatelliten beschrieben und die Beladung kann beginnen. Der nächste Abschnitt stellt ein mögliches Belademuster vor um die hier erstellten Tabellen mit Daten aus den Quellsystemen zu befüllen.
Beladungsmuster
Während der Aufbau der Effektivitätssatellitentabellen noch vollständig den Data Vault Standardmustern entspricht, weicht die Beladelogik leider von der Standardlogik für Satelliten ab. Nachfolgend stelle ich ein mögliches Belademuster für die Entitäten im Raw Vault vor. Eingangs möchte ich aber kurz die Anforderungen an Beladungsmuster aufzählen und kurz die Vor- und Nachteile von inkrementeller gegenüber vollständiger Beladung diskutieren.
Anforderung an das Beladungsmuster
Da ein Data Vault durchaus mehrere Effektivitätssatelliten enthalten kann, die zu jeder Beladung ihres zugehörigen Hubs und Links ebenfalls beladen werden müssen, lohnt es sich vorab über die Anforderungen an das Belademuster nachzudenken. Eine möglichst performante Beladung sollte selbstverständlich immer das Ziel sein. Auch die von Dan Linstedt präferierte (Linstedt, Updates in Data Vault are DEAD! Finally, 2016) und oben bereits diskutierte Praktik, auf UPDATE und DELETE Statements für die Beladung zu verzichten stellt eine mögliche Anforderung an das Beladungsmuster dar.
Einen Data Vault ohne Unterstützung von Automatisierungstools aufzubauen ist keine gute Idee. Die vielen Tabellen und deren Beladungsstatements werden schnell unübersichtlich und provozieren Fehler. Der Markt hat einige Automatisierungstools im Angebot und auch die eigenständige Entwicklung eines DWH-Generators stellt für mittelmäßig begabte Programmierer keine große Herausforderung dar (Cramer, Data Warehouse Automation nimmt so langsam Fahrt auf!, 2015). Die Beladungsmuster der Effektivitätssatelliten sollten deshalb so aufgebaut sein, dass sie möglichst einfach generierbar sind. Außerdem soll die Beladung des Satelliten möglichst in einem Schritt erfolgen. Das macht es möglichst einfach, den Zustand der Tabelle vor der Beladung wiederherzustellen (Rollback), wenn mal ein Fehler beim Beladungsvorgang auftritt. Beim Abbilden der fachlichen Gültigkeit, kann es unter Umständen erforderlich sein, überlappende Gültigkeitszeiträume von Beziehungen bei der Beladung des Effektivitätssatelliten aufzulösen. Dies ist von daher heikel, da hier (wie bereits oben diskutiert) bereits eine Deutung der Quelldaten vorgenommen wird, die so eigentlich erst im Business Vault vorgenommen werden sollte. Die folgende Aufzählung fasst nochmal alle genannten Anforderungen an das Beladungsmuster zusammen:
- Möglichst performante Ausführung
- Insert-Only Statements
- Beladungsstatements sollen möglichst generierbar sein.
- Beladung in einem einzigen Schritt
- Überlappende Gültigkeitszeiträume in der Quelle sollen aufgelöst werden
- Beladung soll sowohl mit vollständigen als auch Inkrementen der Quelldaten funktionieren
- Physikalische Löschung von Beziehungen im Quellsystem sollen erkannt werden.
- Dies ist bei einer inkrementellen Beladung allerdings nicht möglich!
Die beiden letztgenannten Punkte nehmen eine Sonderstellung ein, da es kaum möglich ist physikalisch aus der Quelldatenbank gelöschte Daten in einer inkrementellen Beladung zu erfassen. Hier muss also zwischen Datenqualität und Beladungsperformance abgewogen werden. Sollte Letztere das ausschlaggebende Kriterium sein (wofür es sehr gute Gründe geben kann), sei an dieser Stelle dringend empfohlen wenigstens einmal im Monat den kompletten Datenbestand der Quellsysteme mit den Daten im Data Vault abzugleichen um ein Mindestmaß an Datenqualität sicherstellen zu können.
Ablauf der Beladung
Wie eingangs erwähnt kann die Beladung der Effektivitätssatelliten leider nicht strikt nach dem Standardschema für Satelliten ablaufen. Dies liegt hauptsächlich daran, dass das Driving-Key-Szenario während der Beladung aufgelöst werden muss. Für die Beladung hat das die Konsequenz, dass sie streng genommen nicht mehr unabhängig von (und damit parallel zur) der Beladung des Links durchgeführt werden kann. Denn um die im Vault vorhandenen Daten mit den Quelldaten in der Stage zu vergleichen, muss der Driving-Key über den Link ermittelt werden. Idealerweise sollte daher die Beladung des Links vor dessen zugeordneten Effektivitätssatelliten erfolgen. Da die Beladungsstände von Links und Satelliten aber im Normalfall zeitlich nicht weit auseinanderliegen (zwischen der Beladung eines Links und dem zugehörigen Satelliten werden unter normalen Umständen (außerhalb von Realtime-Szenarien) nur selten weitere Beladungsvorgänge durchgeführt), muss diesem Umstand keine große Aufmerksamkeit zukommen.
Die folgende Abbildung zeigt einen möglichen Ablaufplan für die Beladung von Effektivitätssatelliten. Interessanterweise könnte das Schreiben neuer Effektivitäten parallel zum Schreiben der Abgrenzungssätze bestehender Effektivitäten erfolgen. Die unten gezeigten SQL-Statements machen von dieser Möglichkeit jedoch keinen Gebrauch.
Ablaufdiagramm zur Beladung von Effektivitätssatelliten
Das hier gezeigte Ablaufdiagramm lässt noch viel Spielraum für die Erstellung von darauf aufbauenden SQL-Statements. Nachfolgend werden Testfälle spezifiziert um der Entwicklung einen engeren Rahmen zu setzen.
Testfälle
Heutzutage (und besonders im agilen Umfeld) werden in Softwareprojekten häufig Testfälle noch vor der Entwicklung definiert. Der eigene Code wird dann ständig gegen diese getestet und es wird versucht die Testfälle sukzessive zu erfüllen (Wikimedia, 2017).
Testfälle, die sich im Rahmen von Effektivitätssatelliten anbieten sind unter anderem:
- Doppelte Zuordnung in der Quelle
- Zweimal die gleiche Zuordnung mit unterschiedlichem Gültigkeitsbeginn
- Zweimal oder öfter die gleiche Zuordnung mit gleichem Gültigkeitsbeginn aber unterschiedlichem Gültigkeitsende (inkl. NULL)
- NULL-Values als Gültigkeitsbeginn und/oder -Ende
- Unterschiedliche Zuordnung des Driving-Keys mit überschneidenden Gültigkeitszeiträumen
- Unterschiedliche Zuordnung des Driving-Keys mit beinhaltenden Gültigkeitszeiträumen
- Skript muss zweimal auf den gleichen Daten ausführbar sein und darf dabei keine doppelten Sätze erzeugen
- Gleiche Linkkey/ValidFrom-Kombination in Quelltabelle und Effektivitätssatellit darf nicht zu Unique-Constraint bei der Beladung führen
Die hier genannten Testfälle werden durch die im nächsten Kapitel beispielhaft angeführten SQL-Statements erfüllt.
Beispielstatements
Um die theoretische Betrachtung der Beladung von Effektivitätssatelliten zum Abschluss zu bringen werden in diesem Abschnitt beispielhaft konkrete Implementierungen in (Oracle) SQL gezeigt. Die Skripte orientieren sich an dem oben gezeigten Ablaufplan und erfüllen die im vorherigen Abschnitt genannten Testfälle.
Zunächst zur Beladung der Effektivitätssatelliten für fachliche Abhängigkeiten. Um die Statements einfacher generierbar zu machen werden die Spaltennamen der Quelltabelle und des Satelliten mithilfe von Common Table Expressions (With-Clauses) in eine generische Form gebracht und dann die Daten gemäß dem Ablaufplan ausgewertet:
INSERT INTO edw_data.advisor_cst_cntr_bes
(hk_advisor_cst_cntr_l
,ldt
,rsrc
,validfrom
,validto)
WITH srcdata1 AS
(SELECT a.hk_advisor_cst_cntr_l AS linkkey
,a.hk_advisor_h AS leadingkey
,a.hk_cost_centre_h AS deptkey
,coalesce(a.geaendert_zst, a.erstellt_zst) AS validfrom
,least(coalesce(MIN(coalesce(a.geaendert_zst, a.erstellt_zst)) over(PARTITION BY a.hk_advisor_h ORDER BY coalesce(a.geaendert_zst, a.erstellt_zst) DESC rows BETWEEN unbounded preceding AND 1 preceding) - INTERVAL '0.001' SECOND
,DATE '9999-01-01')
,coalesce(a.geloescht_zst, DATE '9999-01-01')) AS validto
,a.rsrc AS rsrc
FROM stage.v_betreuer a),
srcdata AS
(SELECT a.linkkey
,a.leadingkey
,a.deptkey
,a.validfrom
,a.validto
,a.rsrc
FROM srcdata1 a
WHERE NOT EXISTS (SELECT NULL
FROM edw_data.v_advisor_cst_cntr_bes y
WHERE y.hk_advisor_cst_cntr_l = a.linkkey
AND y.validfrom = a.validfrom
AND (y.validto = a.validto OR a.validto = DATE '9999-01-01')
AND y.ledt = to_date('01.01.9999', 'DD.MM.YYYY'))),
effdata AS
(SELECT hextoraw(l.hk_advisor_cst_cntr_l) AS linkkey
,hextoraw(l.hk_advisor_h) AS leadingkey
,hextoraw(l.hk_cost_centre_h) AS deptkey
,es.validfrom
,es.validto
,es.rsrc
FROM edw_data.v_advisor_cst_cntr_bes es
INNER JOIN edw_data.advisor_cst_cntr_l l
ON l.hk_advisor_cst_cntr_l = es.hk_advisor_cst_cntr_l
WHERE es.ledt = DATE '9999-01-01'
AND es.validfrom < es.validto
-- This can be added to limit the amount of data in an incremental load scenario:
-- AND l.hk_advisor_h IN (SELECT DISTINCT /*+ result_cache */ a.hk_advisor_h FROM stage.v_betreuer a)
),
newvalidto AS
(SELECT e.linkkey
,e.leadingkey
,e.deptkey
,e.validfrom
,MIN(s.validfrom) - INTERVAL '0.001' SECOND AS validto
,e.rsrc
FROM effdata e
INNER JOIN srcdata s
ON s.leadingkey = e.leadingkey
WHERE NOT (s.deptkey = e.deptkey AND s.validfrom = e.validfrom)
AND s.validfrom < e.validto
GROUP BY e.linkkey
,e.leadingkey
,e.deptkey
,e.validfrom
,e.rsrc),
newrows AS
(SELECT * FROM srcdata UNION SELECT * FROM newvalidto)
SELECT linkkey AS hk_advisor_cst_cntr_l
,SYSDATE AS ldt
,rsrc
,validfrom
,validto
FROM newrows a;
Das Statement zur Beladung der Driving-Key LSATs orientiert sich ebenfalls an dem Ablaufplan:
INSERT INTO edw_data.advisor_cst_cntr_dks
(hk_advisor_cst_cntr_l
,ldt
,rsrc
,activeyn)
WITH srcdata AS
(SELECT a.hk_advisor_cst_cntr_l AS linkkey
,a.hk_advisor_h AS leadingkey
,a.hk_cost_centre_h AS deptkey
,'Y' AS activeyn
,a.rsrc AS rsrc
FROM stage.v_betreuer a),
effdata AS
(SELECT hextoraw(l.hk_advisor_cst_cntr_l) AS linkkey
,hextoraw(l.hk_advisor_h) AS leadingkey
,hextoraw(l.hk_cost_centre_h) AS deptkey
,es.activeyn
,es.rsrc
FROM edw_data.v_advisor_cst_cntr_dks es
INNER JOIN edw_data.advisor_cst_cntr_l l
ON l.hk_advisor_cst_cntr_l = es.hk_advisor_cst_cntr_l
WHERE es.ledt = DATE '9999-01-01'
-- This can be added to limit the amount of data in an incremental load scenario:
-- AND l.hk_advisor_h IN (SELECT DISTINCT /*+ result_cache */ a.hk_advisor_h FROM stage.v_betreuer a)
),
newvalidto AS
(SELECT e.linkkey
,MAX(e.leadingkey)
,MAX(e.deptkey)
,'N' AS activeyn
,MAX(e.rsrc)
FROM effdata e
LEFT OUTER JOIN srcdata s
ON s.leadingkey = e.leadingkey
WHERE e.activeyn = 'Y'
AND (NOT (s.deptkey = e.deptkey)
OR s.deptkey IS NULL)
GROUP BY e.linkkey),
newrows AS
(SELECT * FROM srcdata UNION ALL SELECT * FROM newvalidto)
SELECT linkkey AS hk_advisor_cst_cntr_l
,SYSDATE AS ldt
,rsrc
,activeyn
FROM newrows a
WHERE NOT EXISTS (SELECT NULL
FROM edw_data.v_advisor_cst_cntr_dks y
WHERE y.hk_advisor_cst_cntr_l = a.linkkey
AND y.activeyn = a.activeyn
AND y.ledt = DATE '9999-01-01');
Sofern die Effektivitätsdaten in Driving-Key LSATs gespeichert sind, kann man per SQL die Effektivitätsdaten im Business Vault (wie im Abschnitt „Kombinieren der Driving-Key LSAT mit Raw Vault-Daten zu einem Business-Effektivitätssatelliten“ beschrieben) bereitstellen. Diese Abfrage kann (sofern es die Größe der beteiligten Raw Vault-Tabellen zulassen) virtualisiert als View zur Verfügung gestellt werden. Das dafür notwendige SQL werde ich in einem Folgeartikel ausführlicher beschreiben (im Klartext: Ich arbeite noch daran die letzten Bugs zu beseitigen ;)).
Zum Abschluss dieser Ausführungen soll noch kurz das Thema Automatisierung behandelt und aufgezeigt werden, welche Metadaten nötig sind um die hier gezeigten Tabellen- und Abfragestrukturen mit einem Automatisierungstool erstellen zu können.
Gedanken zur Automatisierung
Mithilfe der Effektivitätssatelliten können alle im Data Vault erfassten Beziehungen historisiert gespeichert und auch wieder ausgewertet werden. Da sich üblicherweise im Laufe der Zeit eine große Menge unterschiedlicher Beziehungen im Data Vault ansammeln und dementsprechend viele Links und Effektivitätssatelliten angelegt werden, drängt sich die Nutzung von Automatisierungswerkzeugen regelrecht auf (Cramer, Brauchen wir noch ein ETL Werkzeug bei der Data Warehouse Automation?, 2015).
Nachfolgende Abbildung zeigt welche Metadaten mindestens für die Erstellung der hier vorgestellten Tabellen- und Abfragestrukturen vorliegen müssen.
ERM mit zum Aufbau von Effektivitätssatelliten nötigen Metadaten
Leider gibt es meines Wissens nach zur Zeit kein Tool auf dem Markt, in dem man diese Metadaten erfassen, geschweige denn daraus SQL-Code für Effektivitätssatelliten erzeugen könnte. Am nächsten kommt dieser Anforderung das von Roelant Vos gratis zur Verfügung gestellte, aber leider auf SQL-Server und virtuelle Data Vault-Strukturen beschränkte „Virtual EDW“-Automatisierungstool (Vos, Data Warehouse Virtualisation Software, kein Datum).
Verweise
Cirkel, T. (30. Juli 2017). timo2o1o/dvexamples. Von Github: https://github.com/timo2o1o/dvexamples abgerufen
Cramer, O. (08. August 2015). Brauchen wir noch ein ETL Werkzeug bei der Data Warehouse Automation? Von Data Warehouse Automation Data Vault DWH42: http://www.dwh42.de/2015/05/ abgerufen
Cramer, O. (13. März 2015). Data Warehouse Automation nimmt so langsam Fahrt auf! Von Data Warehouse Automation Data Vault DWH42: http://www.dwh42.de/2015/03/data_warehouse_automation_nimmt_so_langsam_fahrt_auf/ abgerufen
Inmon, W. H. (1996). Building the Data Warehouse. John Wiley & Sons.
Linstedt, D. (2016, August 8). Updates in Data Vault are DEAD! Finally. Retrieved from Accelerated Business Intelligence: https://danlinstedt.com/allposts/datavaultcat/end_of_updates/
Linstedt, D. (2017, July 30). Data Vault Basics. Retrieved from Accelerated Business Intelligence: http://danlinstedt.com/solutions-2/data-vault-basics/
Linstedt, D., & Olschimke, M. (2016). Building a scalable Data Warehouse with Data Vault 2.0. Waltham: Morgan Kaufmann.
Olschimke, M., & Linstedt, D. (2017, April 27). Visual Data Vault [Modeling Language] – Version 1.1. Retrieved from Visual Data Vault: http://www.visualdatavault.com/
Vos , R. (2014, June 10). Virtualising your Data Vault – regular and driving key Link Satellites. Retrieved from An expert view on agile Data Warehousing: http://roelantvos.com/blog/?p=1162
Vos, R. (2014, December 7). Driving Keys and relationship history, one or more tables? Retrieved from An expert view on agile Data Warehousing: http://roelantvos.com/blog/?p=1253
Vos, R. (n.d.). Data Warehouse Virtualisation Software. Retrieved from An expert view on agile Data Warehousing: http://roelantvos.com/blog/?page_id=1509
Wikimedia. (25. Juli 2017). Testgetriebene Entwicklung. Von Wikipedia: https://de.wikipedia.org/wiki/Testgetriebene_Entwicklung abgerufen
T-Scripting 