Continue in 2 seconds

How to Build a Data Mart: Part 2

  • May 01 1998, 1:00am EDT

Last month we began a three-part examination of the data mart development process with a discussion of project planing. This month, we'll turn our attention to the prototyping phase of the project. Prototyping is a critical part of every data mart project. Using an iterative analysis, design and build approach, the prototyping process delivers a working data mart loaded with source data. Using real legacy data helps users verify that the prototype meets their requirements. This iterative cycle begins by creating a formal definition of requirements.

Data mart system requirements are defined by balancing user needs against the availability and quality of source data. Gather user requirements by interviewing managers and business analysts in the assigned subject area. If your project is on a tight schedule, don't try to interview users in a one-on-one fashion. Conduct facilitated requirements workshops. They are faster and more effective.

Document user needs by recording the business questions of greatest importance. For example, "What is the most profitable product we sell?" or "Show me the top five sales teams by geographic region." After documenting and prioritizing these questions, shift your attention to the source data.

Interview database administrators and anyone else who can provide insight into the data and its structures. Don't stop with these interviews! Examine the source data yourself.

Load several months' worth of sample data, as is, into a relational database. Run queries against this data and identify situations that could lead to loading problems. Highlight areas where data is missing and determine if the lack of data precludes the data mart from supporting the stated business rquirements.

The requirements definition process produces a high-level dimensional model of the subject business area. Using this model as a foundation, you are ready to move on and design the prototype.

Prototype design begins with the creation of a star schema. For the first iteration, don't bother tweaking the schema for optimum performance. Concentrate on getting the most important facts and dimensions right.

Develop transformation logic for mapping source data into the target tables. Design the end-user query environment. Be sure to include several of the required "canned" reports. The data mart's ability to produce standard reports is a good test of the prototype's quality. After completing these design tasks, you're ready to build the prototype.

Prototype construction begins by extracting data from the source system(s). Load this data into the target tables to determine its conformance with the design and requirements. Uncover any additional source data deficiencies by iteratively loading data and ensuring the correct results are achieved. Develop the end-user query environment and reports designed in the previous step. Record meta data about the source and target data elements, source-to-target mappings and data transformations.

After building the prototype, you're ready for the ultimate test--feedback. Present the prototype to business analysts, managers and DBAs. Using the query tool, show reports you believe answer the business questions previously documented. Determine how well the loaded data fits into the star schema. Identify areas where the design does not match user requirements and expectations (within subject area scope) and determine how to change the design for the next prototype iteration. If you are going to go through another iteration, reconfirm the scope and return to the requirements definition task.

If the project has been through several iterations and has fully matched the requirements with the source data, the project moves on to the formal end of the prototyping phase.

The end of prototyping means you have either fully met your goals or run out of time. The team must assess whether the prototyping phase has met its objectives and determine the next set of activities. This is easier than you think. If the users are begging you to "Please ship it!" chances are you're ready to move on.

If the project is ready to move forward, the project manager must evaluate the level of effort necessary for finalizing the prototype into a production data mart. Keep in mind that changing a prototype into a robust production application is no simple task. It requires hard work and discipline.

Next month, we'll conclude our series and describe in detail what it takes to move the prototype into production.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access