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

2.5. Summary

Designing dimensional models for business intelligence is no simple trick. The first part of this chapter concentrated on defining and describing the basic concepts of dimensional modeling: facts, dimensions, the bus matrix, and conformed dimensions. The next section expanded the description of dimensional modeling with key concepts such as surrogate keys and tracking changes with Slowly Changing Dimensions. We described several techniques to model a broad range of common (and uncommon) business processes and relationships like many-to-many relationships, hierarchies, heterogeneous products, and junk dimensions.

The third part of this chapter covered the process of dimensional modeling. Begin with a preparation step to identify the team, set up the modeling environment, and determine naming conventions. Begin the modeling process by using our four-step approach to create a high-level business dimensional model, along with attributes and issues lists. The next step is to develop the detailed model, table by table and column by column, filling in all the needed information and addressing all the issues. The last step in the process of creating the dimensional model involved reviewing the proposed model with several interested parties, including other IT people and core business users.

The last part of the chapter applied the dimensional modeling concepts and process to the Adventure Works Cycles case study, resulting in a dimensional model for the Orders business process. ...