Tuesday, July 31, 2007

Creating SQL 2005 Data Warehouse Metadata

One of my favorite tools to use when designing a data warehouse is the Dimensional Modeling Spreadsheet created by Joy Mundy and Warren Thornthwaite of the Kimball Group in conjunction with their must-have book, The Microsoft Data Warehouse Toolkit.  Not only does it allow you to fully build out your fact/dimension design, ETL logic, source-to-target mapping and more, it also generates a script that will create the first iteration of your data model once you have everything in place.  I always try to fully flesh out this spreadsheet before I create the first SQL table, it really helps get things off to a good start.  As an added bonus, Joy and Warren have a whole site full of useful tools and utilities for building a Data Warehouse.

Mark Garner has just released Beta 1 of a tool called Metashare.  He appears to have based it on the Kimball Group spreadsheet I mentioned above, and pulls the same data back out of your data model for the purposes of documentation.  I will look forward to checking out Metashare with the next DW that I build.

2 comments:

Mark Garner said...

I'm excited to hear you might be interested in MetaShare. Let me know your thoughts.

albina N muro said...

Multiple Active Result Sets (MARS) in SQL Server 2005 .... Metadata is created by data modeling tools, databases, report writing tools, ... data into the data warehouse and the associated metadata into the metadata database. survival warehouse food emergency