Imagine this scenario: You're the DBA for your company's transactional production database, and you're preparing to extract data from the database so that you can create a report for one of your company's project managers. However, you can't decide whether you should use the data from table PMBPMSRC or from table PMBPMSTRT because they contain the same two fields: OBJT and STRN. When you ask the manager who requisitioned the report which table to use, she doesn't know, either. But in the end, knowing how the data is stored in the database isn't her job—it's yours. And you realize that for assignments like this, you need a business metadata repository that contains information such as definitions and descriptions of each table and column in your database.
A metadata repository is like a Webster's Dictionary for your database. But instead of buying it at the bookstore, you have to build it yourself as you create and populate the tables in your database. If a proper business metadata repository were in place, determining the difference between the PMBPMSRC and PMBPMSTRT tables would be simple and you would be spared long hours working on your report.
Above and Beyond the Call of Data
Metadata is a familiar commodity; you've probably heard it described as "data about data." SQL Server Books Online (BOL) defines metadata as "information about the properties of data, such as the type of data in a column (e.g., numeric, text) or the length of a column. It can also be information about the structure of data or information that specifies the design of objects such as cubes or dimensions."
The BOL definition addresses technical metadata, which doesn't help you determine the difference between PMBPMSRC and PMBPMSTRT. You need a business metadata repository, which, as far as I know, isn't part of the SQL Server package.
Your business metadata repository should be a full description of the data in your database. It should describe where the data came from (its source or lineage), how it entered the database, what processes captured and stored the data, what the data represents, how and by whom the data will be used, and the relationships between data items in the database.
Figure 1, page 47, shows SQL Server's Meta Data Services SQL Repository, an object-oriented repository technology that you can integrate with enterprise information systems or with applications that process metadata. The SQL Repository contains information about data types and lengths, primary keys, foreign key relationships, and object properties and methods, but it isn't a business metadata repository. When you look at the Bin field (child object of the CELLAR table) in the properties window that Figure 1 shows, you can see that this level of technical information is valuable to a programmer. However, this type of metadata isn't useful for a nontechnical business user. The data is accessible only through Enterprise Manager or through a similar custom solution that your programming staff creates. And even if the data were readily accessible, the object terminology and cryptic nature of the hierarchical layout would test the patience of even the most organized business user.
Endless Possibilities
Let's look at an alternative to using the Meta Data Services SQL Repository for business metadata. This solution uses a new feature of SQL Server 2000 called extended properties. By creating a set of extended properties for each table and for each column within a table, you can document criteria such as where, when, and how data was captured; where the data came from and whether any processing was done to it before it was put into storage in the database; and whether a data item is still active or another data item has replaced it. You can also add field captions in multiple languages for forms and reports.
If you use extended properties, the metadata possibilities are endless. For more information about extended properties, see Bill Vaughn's "Managing Extended Properties," July 2001, InstantDoc ID 20886, and Brian Moran's "Tip: Managing Data Dictionaries Based on SQL Server 2000 Extended Properties," November 2001, InstantDoc ID 22399. Moran's tip includes code written by SQL Server MVP Bob Pfeiff that creates extended-property fields for each table or column object in the database so that developers can fill in the extended-property field values as they need them. For this article's solution, I altered Pfeiff's code by adding extra extended properties.
Prev. page
[1]
2
next page 
