The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

Chapter 2. Designing the Business Process Dimensional Model

To arrive at the simple is difficult—Rashid Elisha

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 Lifecycle's 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.

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 must 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 ...