Fact-Based Modeling for Data Vault: AdventureWorks example

This article provides links to an example of developing a Fact-Based Model for the AdventureWorks database, using the Active Facts Data Vault composition to generate a Data Vault schema, and comparing the generated schema to published schemas from Dan Linstedt.

Links are provided below to source files in the Infinuendo GitHub repository.  All Infinuendo materials is copyright to Infinuendo Pty Ltd.  The original AdventureWorks files are subject to the Microsoft Public License (Ms-PL).

AdventureWorks Database

The Microsoft AdventureWorks OLTP database supports standard online transaction processing scenarios for a fictitious bicycle manufacturer (Adventure Works Cycles). Scenarios include Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources.

The sample database was first released with SQL Server 2008 and was updated with SQL Server 2012.  The database has since been used as a standard example in many presentations since then.

The original AdventureWorks files are published at http://msftdbprodsamples.codeplex.com/.  Entity-Relationship diagrams for the AdventureWorks OLTP database are available here on the Infinuendo github repository.

AdventureWorks Fact-Based Model

The AdventureWorks database has been reverse engineered to provide the corresponding fact-based model in textual and diagrammatic formats:

AdventureWorks Data Vault schema

The file AdventureWorks.dv.sql contains SQL for a data vault schema, generated from the CQL using the ActiveFacts schema_compositor.

Comparison to published Data Vault schemas

In 2010, Dan Linstedt published a blog post describing a Data Vault schema for AdventureWorks.  Source files for this schema were also published on SourceForge.

To centralise these materials, we have republished Dan’s blog post and source files and schema diagram on the Infinuendo github repository.

The original version was produced by a code generator that has some errors in the SQL Server version. These errors were corrected in a blog post by by Hans Geurtsen, and an English version is here courtesy of Google Translate.

 

Graeme PortFact-Based Modeling for Data Vault: AdventureWorks example