According to Dan Linstedt, the creator of the Data Vault Method, “The Data Vault Model is a detail oriented, historical tracking and uniquely linked set of normalized tables that support one or more functional areas of business. It is a hybrid approach encompassing the best of breed between 3rd normal form (3NF) and star schema. The design is flexible, scalable, consistent and adaptable to the needs of the enterprise”
Hans Hultgren also gives a good review of the Agile Data Warehouse in “Modeling the Agile Data Warehouse with Data Vault.” Hans introduces Data Vault in this way, “Some of the main benefits of applying data vault modeling techniques to your data warehouse program include agility, auditability, full historization, realistic enterprise data integration and a lower total cost of ownership.” Not to mention that it is massively scalable and future proof.
Some of the hurdles to adopting Data Vault modeling techniques for the Enterprise Data Warehouse is the sheer volume of tables that are required. Building these by hand make the above statement of low total cost of ownership questionable. Another issue is the lack of quality content that is freely available to see examples of how to accomplish a data vault.
We have decided to put together a series of webinars to demonstrate just how easy it is to achieve. We will show here how to completely generate a data vault by simply pointing at a 3NF database and utilizing Biml to do the heavy lifting. The algorithm we employed was found here, Data warehouse generation algorithm explained . We will firstly demonstrate a solution that match the example line by line using AdventureWorksLT with some minor changes to accommodate all the scenarios. In subsequent webinars we will apply our recommended best practices and apply Data Vault 2.0 patterns.
The content attached is a Mist project containing all the code presented and can be used free of any royalty constraints. Please watch the video that will be uploaded and linked to this article to follow along.
Restore AdventureWorksLT as AWLT3NF Run the 0.01-add-one-one.sql in the folder "Other" or the Miscellaneous logical folder. Create an empty database called AWLTDV If this is all created on your localhost you are good to go.
I have also included the "documentation" folder to give you a taste of some of the documentation options available using Mist.