Auto Generate Data Vault using Biml – Part 1 - Webinar Content

gravatar

Peter Avenant

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 video for this webinar is available on YouTube at http://t.co/C9HFgzGKNM

published 12.04.14

last updated 12.08.14


Share

Tags

  • Data Vault

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.

Setup

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.

    You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

    Comments

    gravatar

    enders

    8:33am 12.10.14

    Really good stuff, can't wait for the next parts.

    gravatar

    Aaron6

    5:03pm 06.22.17

    I am struggling to update the examples to use WideWorldImporter on SQL2016 and BIDS Helper I have created an environment file (not included in the project files )

    and updated the 2.20.0-import-source-tables.biml file to use the new connections and databases

    <#@ import namespace="System.Data" #> <#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>

    <# var sourceConnection = (AstDbConnectionNode)RootNode.Connections["WWI"]; var importResult = sourceConnection.ImportDB("Sales", "", ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews); string[] exclude = {"rowguid", "ModifiedDate"}; #> <# foreach (var table in importResult.TableNodes) { #>

    When I run Check biml for errors with the Environemnt and 2.20.0-import-source-tables.biml selected I get the following error messages: C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Application.People' of type 'TableColumnTableReference'. 'TableName="WWI.Application.People"' is invalid. Provide valid scoped name. Property Table. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales' of type 'Table'. 'SchemaName="WWI.Sales"' is invalid. Provide valid scoped name. Property Schema. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales' of type 'Table'. 'SchemaName="WWI.Sales"' is invalid. Provide valid scoped name. Property Schema. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Application.People' of type 'TableColumnTableReference'. 'TableName="WWI.Application.People"' is invalid. Provide valid scoped name. Property Table. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales' of type 'Table'. 'SchemaName="WWI.Sales"' is invalid. Provide valid scoped name. Property Schema. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales' of type 'Table'. 'SchemaName="WWI.Sales"' is invalid. Provide valid scoped name. Property Schema. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales' of type 'Table'. 'SchemaName="WWI.Sales"' is invalid. Provide valid scoped name. Property Schema. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales.Customers' of type 'TableColumnTableReference'. 'TableName="WWI.Sales.Customers"' is invalid. Provide valid scoped name. Property Table. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales.CustomerCategories' of type 'TableColumnTableReference'. 'TableName="WWI.Sales.CustomerCategories"' is invalid. Provide valid scoped name. Property Table. C:\Users\local-admin\Documents\Visual Studio 2008\projects\DataVault2\DataVault2\2.00.0-import-source-tables.biml(-1,-1) : Error 20 : Could not resolve reference to 'WWI.Sales.BuyingGroups' of type 'TableColumnTableReference'. 'TableName="WWI.Sales.BuyingGroups"' is invalid. Provide valid scoped name. Property Table. . . . (Lots more!)

    What have I done wrong?

    Also posted in the forum. Sorry for the cross-post.

    Aaron