Chapter 2: Designing the Business Process Dimensional Model

To arrive at the simple is difficultRashid Elisha

Overview

This chapter is about the basic concepts of dimensional modeling and the process of designing a business process dimensional model. Designing the dimensional model falls within the central section of the Lifecycle, as shown in Figure 2.1. This middle row of the Lifecycles central section focuses on data, hence the clever name : the data track . The main objective of the data track is to make sure users get the data they need to meet ongoing business requirements. The key word in this objective is ongoing : Your goal in this step is to create a usable, flexible, extensible data model. This model needs to support the full range of analyses, both now and for the foreseeable future.

Figure 2.1: The dimensional modeling step in the Lifecycle context

Because most of the work in building a DW/BI system happens in the data track, and the track builds on the dimensional model, it should be well designed. The first part of this chapter is a primer on dimensional modeling, including an overview of facts, dimensions, the data warehouse bus matrix, and other core concepts. The second major section of the chapter delves into more detail on several important design techniques, such as slowly changing dimensions, hierarchies, and bridge tables. Once the basic concepts are in place, the third section presents a process for building dimensional models. You should begin with a bit of preparation that involves reviewing the business requirements and determining naming conventions, setting up the modeling environment and data research tools. The modeling process itself starts with a design session which results in an initial high level model, an attributes list, and an issues list. This is followed by a detailed model development phase and a review and validation phase.

The fourth part of the chapter applies the modeling concepts and process to the Adventure Works Cycles example to get a real-world feel for what it takes to create a dimensional model. Creating the Adventure Works Cycles model gives us an excellent opportunity to explore several common dimensional modeling issues and their solutions.

Reference 

This chapter describes what a dimensional model is, why its a useful design technique for a DW/BI system, and how to go about designing a strong data foundation. You cannot possibly learn everything you need to know about dimensional modeling in a single chaptereven a long one like this. For additional detailed guidance on the techniques, including industry case studies, we refer you to The Data Warehouse Toolkit , Second Edition , Ralph Kimball and Margy Ross (Wiley, 2002).