Chapter 13: Metadata Plan

The Bermuda Triangle of data warehousing

Overview

Metadata is a vast, relatively uncharted region of the DW/BI system. Some teams sail into it full speed ahead, never to be heard from again. Most teams try to avoid the problem by sailing around it. Unfortunately, the metadata region is smack in the middle of your path to the great new world of business value, and you need to figure out how to navigate it successfully.

One of the first metadata challenges you often face is just trying to figure out what metadata is. To that end, we begin this chapter with a brief definition and description of the three major categories of metadata typically found in a DW/BI system: business, technical, and process metadata. Once you have a sense for what metadata is, your next big challenge is figuring out where to put it. Although SQL Server 2005 has its own ideas on where to keep its metadata, we include a description of the industry standard for storing and exchanging DW/BI metadata: the Common Warehouse Metamodel.

With a common terminology in place, your next challenge will be figuring out exactly what metadata you have and where it comes from. To that end, we explore the various sources and uses of metadata across the SQL Server 2005 toolset. As it turns out, every major component of the toolset is metadata driven. The problem is the metadata is kept in different locations and different formats, so finding and managing the metadata is a bit of a hardship. Finally, we describe a basic, practical approach for dealing with the most important, or at least the most broadly used metadata elements.

Metadata creation and management can be an extremely tangled topic. What we present here is a starting point. Many of you, and certainly those of you in larger organizations, will need to expand on our recommendations and customize them to your environments.