AdventureWorks Data Vault DDL

i’ve decided to release the version of the adventureworks data vault ddl. if you have questions, or would like support on the ddl or about the model, _please_ sign up for coaching.  this is provided for free as a convenience to you.  please be aware of the following notes about the model:

issues with the model (source and dv):

  1. it is not 100% accurate. i’ve done my best (as have others) to provide you with a half-way decent working model. again, if you want support or have questions, please sign up for coaching. i will be happy to help you improve it.
  2. the source model makes it very tough to find or use business keys. the source model is not clean, therefore the dv model is not clean.
  3. there were / are no business users to check assumptions with
  4. there is no documentation for the source model, so checking datatypes is a moot point
  5. there is no real oltp application with edit rules, checks and balances, so checking business keys becomes near impossible.
  6. there is a limited amount of “sample” data in the adventureworks database, because of this, profiling doesn’t turn up any spectacular unknown results.
  7. microsoft database technologists built this model, not business users, so it uses surrogate keys as it’s primary business key basis, however, every time microsoft “generates different data” for the source database, surrogates get re-assigned to new data sets.

ok, that said, this is *unsupported*, and provided as a simple working example.

so, if these are all the issues with the model, what are the benefits?

  1. it has hubs, links, satellites, and hierarchical links
  2. generating test data to the model, and putting it under load will help you see query performance, join performance, possible areas to “tune a datavault”
  3. it will also help you begin to create “test etl” mappings to load the structures, and see how updates work, inserts, and delta checking works.
  4. building point in time and bridge tables for queries will give you a chance to test out sql query performance
  5. putting it under massive load (10tb or more) may help you “see” a live model in action with your hardware as a proof of concept.

do not make the mistake of assuming that this model doubles as a production model, it does not.  it is merely a reference point, and a “sand-box play toy”.  note: these are just the ddl’s for the models (they have not been tuned for the specific database)  also note: these models are designed by hand, so they may contain a number of “mistakes” as i did not have a lot of time to spend with them.

other than that, have fun!

i apologize, but due to many rebuilds of my web-site, the downloads have been lost to time.

 

Tags: , , ,

5 Responses to “AdventureWorks Data Vault DDL”

  1. Hennie de Nooijer 2010/10/22 at 3:17 am #

    Hi Dan,
    Some confusion over here. You write about AdventureWorks and Northwind DV in your post and the link “AdventureWorks SQLServer 2005/2008 Data Vault” points to the Northwind database.
    Gr,
    Hennie

  2. dlinstedt 2010/10/22 at 3:37 am #

    Hi Hennie,

    I checked the DDL against the model, it’s pointing to AdventureWorks DDL Data Vault. I had one “statement” in the post about Northwind, but these files do not contain the northwind Data Vault. Is it the Northwind Data Vault you are interested in? And what leads you to believe this is a Northwind Data Vault (just curious, because I need to check in to this). Please be aware, that Northwind turned in to AdventureWorks in 2005 and then was “added to”.

    Thanks,
    Dan L

  3. Hans Geurtsen 2011/11/07 at 10:28 am #

    Dan, thanks for this! I did run into a lot of errors when executing the SQL Server script. There were basically two problems: constraint names were not always unique (there is a quite a number of these duplicate constraint names) and there were a few field names using a reserved keyword that should be in brackets. I published a revised version of the script in which I quick-and-dirty solved these two problems. Quic-and-dirty as in I changed all the constraint names for which I got an error into ‘PK_’. That solved it. If you’re interested, you can find this revised version of the script at http://blogs.infosupport.com/wp-content/uploads/2011/11/ADVDV_SQLServer_V4.1.zip.

    Thanks again!
    Hans.

  4. dlinstedt 2011/11/07 at 11:29 am #

    Hi Hans,

    Thanks for the feedback. I’ll check in to the errors and try to fix them. I am interested, and will take the script you changed, and put it with the package I offer for downloads.

    Dan L

Trackbacks/Pingbacks

  1. Tweets that mention AdventureWorks Data Vault DDL -- Topsy.com - 2010/10/21

    […] This post was mentioned on Twitter by elsbeth peters, Daniel Linstedt. Daniel Linstedt said: AdventureWorks Data Vault DDL: Microsoft AdventureWorks Database Model converted to a number… http://goo.gl/fb/QvFV2 […]

Leave a Reply

*