Leveraging the Kimball Spreadsheet & MetaShare

(Continuation of Metadata 2.0 Series) 

Ralph Kimball is one of the founders of data warehouse architecture. Two of his colleagues, Warren Thornthwaite and Joy Mundy (and probably others) have collaborated to create a technique for gathering requirements for a data warehouse and using those requirements to build DDL scripts to play into SQL Server to begin the process of physicalizing the data warehouse.

This worksheet is extensive and when used to its fullest includes all kinds of metadata about a data warehouse. The spreadsheet is set up to create DDL scripts for tables, views, relationships and other physical structures.  It is also set up to leverage extended properties in the database to hold important pieces of metadata.  After the spreadsheet is completed, the CREATE scripts are played into the database server.  How can we expose this rich set of metadata to users and technical architects?

There is a tool called MetaShare that takes a SQL Server 2005 database and extracts not only the metadata out of the SQL Server catalog that describes such things as tables in databases, columns in tables, and column data types, but also the metadata stored in the extended properties that were created by the CREATE scripts generated from the Kimball Spreadsheet.  Here is the list of extended properties that are supported by the Kimball spreadsheet and MetaShare out of the box:

Table Metadata:

• Description
• Display Name
• Is Audit Subsystem
• TableType
• Used In Schemas
• View Name

Column Metadata

• Description
• Display Name
• Example Values
• Is Audit Subsystem
• Is Key
• Table Type
• Used in Schemas
• View Name

MetaShare is currently in Alpha 2 release and can be downloaded and tested from SourceForge.net.  I’m looking for help finishing and testing MetaShare, so if anyone is interested, let me know. 

More specifics on MetaShare in the next article…