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

2.4. Case Study: Creating the Adventure Works Cycles Orders Dimensional Model

This fourth major section of this chapter is meant to impart a sense for how the modeling process might work in the real world. We draw from the Adventure Works Cycles business requirements from Chapter 1 and source system data in the AdventureWorks transaction database for examples of each of the deliverables. The initial design session starts with the knowledge that the modeling team is designing a dimensional model for the Orders business process. According to the four-step process for creating a high-level dimensional model, step two is to choose the dimensions.

2.4.1. Choosing the Dimensions

In order to give you a richer context for the process of choosing dimensions, we'll go all the way back to step (1) in Figure 2.9: the high-level dimensional model design session. Using the dimensional model shown in Figure 2.12 as our guide, we'll summarize the Adventure Works Cycles DW/BI team's discussion and conclusions for each dimension, starting with the Date dimension. The dimensional model shown in Figure 2.12 is based on the initial bus matrix we developed as part of the requirements definition process in Chapter 1. The model in Figure 2.12 provides a rough starting point for the high-level dimensional model discussion. At the end of this section, we show the resulting updated high level Orders dimensional model.

Date

There are two important dates in the orders business process: order date and due ...