FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Publisher reserves the right to serve qualified requesters only.

Data Dictionary, Are You Out There?

Design Challenge

A great way to sharpen your analysis and modeling skills is to continuously address real-world scenarios. A global manufacturing company needs a data dictionary to assist them with maintaining and publishing definitions of business concepts and data elements such as Customer and Gross Sales Amount. At a minimum, this data dictionary needs to:

  • Be compatible with packages such as SAP;
  • Incur minimal maintenance cost;
  • Allow classifying of terms, such as by geography or line of business; and
  • Make the definitions easily accessible for browsing through the Web.

The Challenge

What advice would you recommend to this company?

The Response

As a data modeler, I document, use and review definitions. As a documenter of definitions, I capture sentences from business experts usually within a data modeling tool. As a user of definitions, I seek to understand the existing environment through definitions usually written in requirements documents. As a reviewer of definitions, I prefer a spreadsheet where I can add a column for comments.

I have very specific requirements from the tool or tools that store the definitions (i.e., the data dictionary). Business users might have different requirements as well as other members of the project team. So one challenge will be the varying and sometimes conflicting data dictionary requirements. Another challenge will be accessing and consolidating existing definitions from different places, such as modeling tools and spreadsheets. Also, the terms themselves have a defined scope. Terms such as Customer and Gross Sales Amount have an enterprise-wide scope, yet a term such as Customer Shoe Size might only be relevant within a specific department or application.

I would first recommend this organization fully understand each of these challenges and then agree on priorities. A good approach to understanding the challenges for a data dictionary project is to learn the expectations and requirements of the data dictionary stakeholders. Create an online survey and have anyone with a vested interest in the dictionary complete this survey. Survey questions should address the desired medium for the dictionary as well as definition components, usages and scope. Go through the results with the project sponsor, who is someone ideally from the business side willing to fund the project who has also completed the survey. You might find that many of the perceived data dictionary challenges that kept you awake at night are not within the scope of your data dictionary project. You can also work with the sponsor to break up the requirements within the scope of the dictionary into manageable chunks that can be delivered on a periodic basis, such as quarterly.

You might find your requirements resulting from this survey match what Frank Palmeri, database programmer/analyst, recommends. "A good data dictionary should be dynamic, not static; open, not proprietary; cost-effective, not expensive; easy to implement, not difficult; easy to understand, not complicated; and, above all else, a joy to use. When you find one that meets all these requirements, please let me know what it is!"

Once the data dictionary requirements are understood and prioritized, you can match requirements against available tools. There were only a few tools mentioned by our challengers. One senior consultant is currently customizing a tool from QualiWare to capture extensive business metadata (data dictionary) about entities/attributes in multiple fields. Another challenger suggested Microsoft's Data Dude (DD) product, which is short for "Visual Studio 2005 Team Edition for Database Professionals."

This company, however, might find it cheaper and quicker to deliver the first few phases of the dictionary by connecting existing sources of definitions instead of building a new application (i.e., a federated approach). Look for automated ways to transfer definitions from existing applications such as SAP to Web pages or spreadsheets. Most applications, for example, support the Common Warehouse Metadata (CWM), which will allow XML import and export of metadata, including definitions.

Johnny Gay, data analyst, has a similar approach in his organization. "We apply our definitions through our data modeling tool using a VBscript to pull them from an Excel spreadsheet. The definitions are published on the Web in a data model report. Our customers cut and paste these to other tools (usually Excel). They add columns to their spreadsheets (e.g., geography, line of business, etc.) to classify the definitions as they need."

If you would like to become a design challenger and have the opportunity to submit modeling solutions, please add your email address at www.stevehoberman.com. If you have a challenge you would like our group to tackle, please email me a description at me@stevehoberman.com.


Steve Hoberman has worked as a business intelligence and data management practitioner and trainer since 1990. He is a Certified Business Intelligence Professional (CBIP), having achieved mastery level certification in data analysis and design. He is a popular and frequent presenter at industry conferences, both nationally and internationally.  Hoberman is a columnist and frequent contributor to industry publications, as well as the author of  Data Modeler's Workbench and Data Modeling Made Simple (available for purchase through the DM Review bookstore). He is the founder of the Design Challenges group, inventor of the Data Model Scorecard and a recognized innovator and thought leader in the field of data modeling. He can be reached at me@stevehoberman.com.

Graeme Simsion's latest book is out! Data Modeling Theory and Practice. Here's a link where you can read more about the book and purchase it at a discounted price.

For more information on related topics, visit the following channels:



Industry Vendors