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):
- 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.
- 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.
- there were / are no business users to check assumptions with
- there is no documentation for the source model, so checking datatypes is a moot point
- there is no real oltp application with edit rules, checks and balances, so checking business keys becomes near impossible.
- there is a limited amount of “sample” data in the adventureworks database, because of this, profiling doesn’t turn up any spectacular unknown results.
- 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?
- it has hubs, links, satellites, and hierarchical links
- 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”
- 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.
- building point in time and bridge tables for queries will give you a chance to test out sql query performance
- 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.