Using the MetaShare Platform to Update Metadata

(Continuation of Metadata 2.0 Series) 

Updating the Metadata Attributes

The pages created by MetaShare are separated into two major sections.  The top of the page is created and maintained by MetaShare.  The bottom is reserved for user-generated content.  If the extended properties metadata requires a change, there is a companion tool web application that allows for easy updating of the Kimball spreadsheet metadata attributes after the database is deployed and in production. It allows you to choose a database on a server and navigate to the metadata attributes for the tables and columns and update them accordingly.

User Generated Content

The bottom of every page that is generated by MetaShare is reserved for user content. This is where collaboration begins to come into play.  Users are encouraged to add information they know about an item or correct information they think is incorrect about that item.  Users should feel empowered to make changes to the pages for two reasons.  First of all, the collaboration platform retains document history.  Any documents changed by a user are saved before editing begins.  Comparisons between versions of a document become easy allowing for active management and moderating of changes by a metadata steward.

 In the next article, I’ll discuss the management process around metadata.

MetaShare and the Kimball Audit Dimension

(Continuation of the Metadata 2.0 series) 

Mr. Kimball’s group has architected a structure for tracking changes in the warehouse. This includes the execution of SSIS packages and how, when, and what data those packages changed. This powerful feature of a data warehouse gets special treatment in the output of MetaShare.

There are special metadata attributes allocated to describing whether a table or column is associated with the Audit Subsystem. These attributes and tables are moved to the bottom of a MetaShare generated page in a special location so that they are easy to find and well as easy to ignore. This keeps all of the business attributes all together at the top of the page.

To read more about the Audit Subsystem, take a look at the Microsoft Data Warehouse Toolkit book written by Warren and Joy.

More about how the MetaShare generated pages and user generated content in the next article.

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…

A great article about Collaborative Metadata

R. Todd Stephens wrote a great article that was published in DMReview a little while back that talks about the evolution toward Collaborative Metadata.  I just ran across it a little while ago and I thought it was a great read.  He uses the term Metadata 2.0 and I think that is a really good way to describe what collaborative metadata is and how we have progressed from the Web 1.0 world to the Web 2.0 world by integrating user based content and collaboration in many of the web apps that are seen today.

A Collaborative Platform for Metadata Management

Metadata by nature is collaborative. Knowledge is spread out across an organization. The business users have knowledge about what a data item means and technical architects know how a data item is transformed on its way into the data warehouse.

Users are learning more all the time. They are given tasks that teach them more about the business every day. This knowledge needs to be gathered for others to use. Users need to be empowered share this knowledge in the easiest way possible. By giving them instant access to a consolidated information base, they can learn and integrate their new knowledge into the enterprise.

Metadata is something that takes active management. Metadata collaboration is an active process where an organization must share, collaborate, and manage the metadata life cycle. If an organization doesn’t manage their metadata, it will get out of control.

Metadata can be presented in a fashion that enables collaboration. To enable this collaboration, we have to present the metadata in a fashion that allows users to update the metadata and bring it up to date. Wikipedia is a good example. Users submit articles, updates, and corrections to the Wikipedia online encyclopedia. Editors monitor the changes and moderate the changes to keep the quality high. 

In the next article in this series, I’ll begin talking about how to implement a collaborative metadata platform using some simple tools – a couple of them I wrote to help out.