Data Vault AdventureWorks Voorbeeld

Op internet zijn veel artikele te lezen over de  Data Vault. Dit is een manier/datamodel om een data warehouse te onwerpen.  Wat mij opvalt is dat er (nog) geen goede voorbeelden te vinden zijn van het Datamodel en de ETL.  Om de methode beter te begrijpen is het handig om voorbeelden te hebben. In dit artikel tracht ik door middel van een kleine Data Vault gebaseerd op AdventureWorks 2005 duidelijk te maken hoe een Data Vault qua structuur is opgebouwd en hoe de ETL eruit ziet om de Data Vault te vullen. Dit voorbeeld gebruikt niet alle tabellen uit AdventureWorks maar enkel een paar tabellen uit het schema “Production” . Dit artikel is niet bedoeld om de voordelen van data vault te bespreken maar is puur een technisch verhaal. Onderaan het artikel vind je een zip file met de scripts.

Voor het genereren van de DDL (tabellen) en ETL (laadprocessen) heb ik gebruik gemaakt van de (open-source) tool Quipu welke sinds 1 juli 2010 gratis te downloaden is. Deze tool neemt veel werk uit handen door de zaken die normaal veel tijd kosten te automatiseren. Quipu genereert het datamodel voor je (data vault) en de sql om te laden (ETL). Ook kun je views laten genereren die de inhoud van de Data Vault laten zien op een bepaald moment. Het mooie van Data Vault is dat alle ETL stappen uit substappen bestaan die allemaal los zijn op te starten. Quipu genereert alle SQL-statements voor je. Deze SQL wordt opgeslagen in de database van Quipu. Standaard slaat Quipu alles op in zijn Jetty Database. Je kunt dit veranderen zodat hij het in SQL Server opslaat. Dit heb ik zelf ook gedaan zodat ik in de tabellen kon kijken wat er gebeurde. Dit kun je voor elkaar krijgen door een lokale systeemvariabele aan te maken en deze te laten verwijzen naar een properties bestand op de schijf. In deze file staat een verwijzing naar de SQL database (zie documentatie). Zodra de ETL is gegenereerd moet je zelf creatief zijn om hier iets van te bakken. De ETL zijn eigenlijk gewoon SQL-statements die data verplaatsen. Je kunt hier een stored procedure van maken of een SSIS (SQL Server Integration Services) package. Het genereren van deze zaken zit (nog) niet in de versie die ik getest heb (1.03). Alles wat Quipu genereert is volgens de Datavault standaard in ANSI92 SQL. Dit werkt ook onder Sql Server 2005+.

Alle DDL en ETL kun je downloaden onderaan dit artikel.

Staging Area Adventureworks
Alvorens het Data Vault model geladen wordt dient de data in een Staging Area aanwezig te zijn. Bovenstaande tool genereert staging area tabellen voor je en ook de ETL, dit scheelt weer een berg werk. Nadat ik het schema van AdventureWorks heb geselecteerd stel ik wat opties in:

Ik heb ingesteld dat ik de staging area tabellen wil leeggooien voordat de data wordt geladen en dat ik een full load wil. De eerste zorgt voor extra truncate commando’s tussen de laadstatements in. De tweede laadt altijd alle records vanuit de bron. Als Delta Load wordt aangevinkt worden alleen wijzigingen in de bron geladen. Vervolgens kan ik de staging area DDL genereren. Maak eerst een database aan, bijvoorbeeld “Adventureworks_stg”. Let op de collation (de collation moet hetzelfde zijn als de brondatabase van Adventureworks).

Voer nu dit script uit: 1.0_Create_Staging_Area.sql

Let op: maak eerst de user defined datatypen en schema’s aan, deze zitten ook standaard in Adventureworks. Als je deze niet aanmaakt krijg je foutmeldingen.

CREATE TYPE [dbo].[NAME] FROM [nvarchar](50) NULL
CREATE TYPE [dbo].[FLAG] FROM [bit] NOT NULL
CREATE SCHEMA Production

Vervolgens genereren we de ETL om de Staging Area op te vullen.

Voer dit script uit: 2.0_Populate_Staging_Area.sql

Voer de scripts om de beurt uit. Als het goed is worden er tabellen aangemaakt in AdventureWorks_stg en daarna gevuld met het ETL script. We hebben nu een staging area en ETL.

Data Vault AdventureWorks
We willen nu een datamodel genereren en laadprocessen om een Data Vault te laden. Ik ga niet teveel in op de stappen die ik heb doorlopen om tot het model te komen. Ik heb het model laten genereren door bovengenoemde tool. De meeste settings heb ik standaard gelaten. Het voordeel van AdventureWorks is dat het een vrij “mooie bron” is. Relaties zijn aanwezig, tabelnamen zijn duidelijk. Normaal is dit natuurlijk niet zo. Daarnaast heb ik wat naming conventions mbt tabelnamen. De sleutels heten bijna altijd hetzelfde als de tabel met een suffix.

Hub: NaamTabel_h
Link: TableA_TableB_l  <- volgorde van naamgeving belangrijk, zo wordt de naam van de link tussen productmodel en product: Product_ProductModel_l
Satelite op hub: NaamTabel_s
Satelite op link: NaamTabel_l_s
Surrogatkey hub:Hubnaam_hid
Surrogatkey link: Linknaam_lid

Ik heb gemerkt dat dit prettig leest. Je mag volgens de theorie ook de suffix als prefix toevoegen, alleen vind ik dit beter werken. Hieronder zie je een screenshot van mijn instellingen op het schema “Production”. Zoals je ziet gebruik ik maar een paar tabellen in dit voorbeeld.

Vervolgens laat ik de tool het Data Vault model aanmaken. Als target schema stel ik in “dbo”. Zo worden alle tabellen netjes dbo.tabelnaam genoemd. Daarna rename ik de tabellen en veldnamen naar de standaarden die hierboven staan voor betere leesbaarheid.

Notitie:
De link tussen product en Bill of Materials komt twee keer voor:
BillOfMaterials_Product_Component_l
BillOfMaterials_Product_ProductAssembly_l

Dit zijn twee aparte links. In het bronmodel zie je ook dat dit twee relaties zijn. Ik weet niet precies hoe Data Vault naming convention toepast bij links tussen dezelfde tabellen maar met een nadere rol.

In Quipu ziet mijn model er nu zo uit:

Voer nu dit script uit: 3.0_Create_Data_Vault_Data_Model.sql

Maak vervolgens de datatypen weer aan die ook in AdventureWorks worden gebruikt.

CREATE TYPE [dbo].[NAME] FROM [nvarchar](50) NULL
CREATE TYPE [dbo].[FLAG] FROM [bit] NOT NULL

 Voer nu dit script uit: 4.0_Populate_Data_Vault_Model_Script.sql

Let op, ik gebruik geen constraints in de Data Vault.

Tot slot kun je met Quipu nog wat views aanmaken die de stand van de Data Vault weergeven. Dit zijn de actuele views, laatste stand en point in time. Deze vind je ook bij de scripts:

5.0_Views_Last.sql
6.0_Views_Actual.sql
7.0_Views_Point_In_Time.sql

Dat was een voorbeeld van het maken van een kleine Data Vault op AdventureWorks Database van Microsoft. Ik hoop dat dit voorbeeld duidelijk maakt hoe een Data Vault is opgebouwd qua structuur en hoe de laadprocessen eruit kunnen zien. Als je een opmerking of vraag hebt, post een bericht.

Download hier de voorbeeldbestanden van de AdventuresWorks Data Vault ETL.

 Data_Vault_AdventureWorks_sqlblognl