The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition

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

The dimensional model is the true heart of the DW/BI system. It is the target for the ETL system, the structure of the database, and the model behind the user query and reporting experience. Clearly, the model must be well designed. The first part of this chapter is a brief 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.

The fourth ...