Data Vault AdventureWorks Example

There are many articles on the internet about the Data Vault. It is a method/data model to design a data warehouse. What strikes me is that there are no good examples (yet) of the data model and the ETL. To understand the method better, it helps to have examples. In this article I use a small Data Vault based on AdventureWorks 2005 to show how a Data Vault is structured and what the ETL that fills it looks like. This example does not use all tables from AdventureWorks, only a few tables from the “Production” schema. This article does not discuss the benefits of data vault. It is purely a technical story. At the bottom of the article you will find a zip file with the scripts.

To generate the DDL (tables) and ETL (load processes) I used the (open-source) tool Quipu, which has been free to download since 1 July 2010. This tool takes a lot of work off your hands, because it automates the things that normally take a lot of time. Quipu generates the data model (data vault) and the SQL to load it (ETL). You can also let it generate views that show the content of the Data Vault at a given moment. The nice thing about Data Vault is that every ETL step consists of sub-steps that you can all start on their own. Quipu generates all SQL statements for you. This SQL is stored in the Quipu database. By default Quipu stores everything in its Jetty database. You can change this so it stores it in SQL Server. I did that myself, so I could look in the tables to see what happened. You do this by creating a local system variable that points to a properties file on disk. This file holds a reference to the SQL database (see the documentation). Once the ETL is generated, you have to be creative to make something of it. The ETL is really just SQL statements that move data. You can turn it into a stored procedure or an SSIS (SQL Server Integration Services) package. Generating those is not (yet) in the version I tested (1.03). Everything Quipu generates follows the Data Vault standard in ANSI92 SQL. This also works on SQL Server 2005+.

You can download all DDL and ETL at the bottom of this article.

Staging Area Adventureworks
Before the Data Vault model is loaded, the data has to be in a Staging Area. The tool above generates staging area tables for you, and the ETL too, which saves another pile of work. After I select the AdventureWorks schema, I set some options:

I set it to empty the staging area tables before the data is loaded, and to do a full load. The first adds extra truncate commands between the load statements. The second always loads all records from the source. If you tick Delta Load, only the changes in the source are loaded. Then I can generate the staging area DDL. First create a database, for example “Adventureworks_stg”. Watch the collation (it must be the same as the collation of the Adventureworks source database).

Now run this script: 1.0_Create_Staging_Area.sql

Note: first create the user defined data types and schemas. Adventureworks has them by default too. If you do not create them, you get errors.

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

Next we generate the ETL that fills the Staging Area.

Run this script: 2.0_Populate_Staging_Area.sql

Run the scripts one after the other. If all goes well, tables are created in AdventureWorks_stg and then filled by the ETL script. We now have a staging area and ETL.

Data Vault AdventureWorks We now want to generate a data model and load processes to load a Data Vault. I will not go into detail on the steps I took to get to the model. I let the tool above generate the model. I left most settings at their defaults. The advantage of AdventureWorks is that it is a fairly “nice source”. The relationships are there and the table names are clear. Normally that is not the case, of course. I also have some naming conventions for table names. The keys almost always have the same name as the table, with a suffix.

Hub: NaamTabel_h
Link: TableA_TableB_l <- the naming order matters, so the name of the link between productmodel and product is: Product_ProductModel_l
Satellite on hub: NaamTabel_s
Satellite on link: NaamTabel_l_s
Surrogate key hub: Hubnaam_hid
Surrogate key link: Linknaam_lid

I find that this reads well. According to the theory you may also add the suffix as a prefix, but I think this works better. Below is a screenshot of my settings on the “Production” schema. As you can see, I only use a few tables in this example.

Then I let the tool create the Data Vault model. I set the target schema to “dbo”. That way all tables are neatly named dbo.tablename. After that I rename the tables and field names to the standards above, so they read better.

Note:
The link between product and Bill of Materials appears twice:
BillOfMaterials_Product_Component_l
BillOfMaterials_Product_ProductAssembly_l

These are two separate links. The source model also has two relationships here. I do not know exactly how the Data Vault naming convention handles links between the same tables with a different role.

In Quipu my model now looks like this:

Now run this script: 3.0_Create_Data_Vault_Data_Model.sql

Then create the data types from AdventureWorks again.

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

Now run this script: 4.0_Populate_Data_Vault_Model_Script.sql

Note: I do not use constraints in the Data Vault.

Finally, Quipu can create some views that show the state of the Data Vault. These are the current views, the latest state and point in time. You will also find them with the scripts:

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

That was an example of building a small Data Vault on Microsoft’s AdventureWorks database. I hope this example makes clear how a Data Vault is structured and what the load processes can look like. If you have a remark or a question, post a comment.

Download the example files of the AdventureWorks Data Vault ETL here.

 Data_Vault_AdventureWorks_sqlblognl