I works in a company that provide Dwh views as an API for external Customers (We use MS-SQL 2008 & 2012).

every once in a while we release new views and updates the existing views.

We have difficult to track and document all the updates and changes (currently it is documented in WORD document).

In addition we got complains from customers telling us that the documentation isn't detailed enough and they want to see Entity Relationship Diagram (ERD), primary and foreign keys - in addition to the descriptions of the views.

My first thought is to make it available online (like other APIs documentation I saw) rather then WORD or PDF document.

My question is, do you have any experience or any suggestion what is the best way to document Dwh views? Do you know any automation tools (free or not) that can help with this task?



There are basically two ways to do this in a maintainable way:

  1. Use a metadata framework to generate the data warehouse
  2. Use Extended properties (http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx)

Ad 1) The reason you are having this issue on the first place is that you are not creating metadata first and then using that metadata (with a simple code generator) to generate the tables and views in the database. There are several frameworks available that will allow you to have an almost pure meta driven data warehouse model. Check out TimeExtender (http://www.timextender.com/) and Vulcan (https://vulcan.codeplex.com/) for a few examples. With metadata driven modelling, documentation happens as a natural part of the modeling process

Ad 2) If you already have a model (which it sounds like you do) a great way to document it to end users, is to add Extended properties to the views as you develop/maintain them. These properties be added both to the view itself and to do the columns in the view. With a simple piece of code in your favourite programming languages - you can query the extended properties to auto generate documentation. A good way to expose this documentation to the end user is to render web pages, so they can navigate the data model from there. Another advantage of this model is that the documentation now lives inside the database (you can back it up as part of your routine backup procedure) and the BI developers have it available at their fingertips (nobody likes to document code in Word documents)

| improve this answer | |
  • Thank you for your answers, Can you please add links to [Vulcan] framework? (I saw that BIExternder is for Oracle so its less relevant). – Gil Peretz Dec 29 '14 at 9:46
  • I added the links. Sorry, I got the name wrong on one of them, it is TimeExtender, not BIExtender (wrote post before first coffee) – Thomas Kejser Dec 29 '14 at 9:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.