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

2.3. The Dimensional Modeling Process

With a basic understanding of dimensional modeling and the core techniques under your belt, this section shifts focus to describe the process of building a dimensional model. Creating a dimensional model is a highly iterative and dynamic process. After a few preparation steps, the design process begins with an initial graphical model pulled from the bus matrix and presented at the entity level. This model is critically scrutinized in a high-level design session that also yields an initial list of attributes for each table and a list of issues requiring additional investigation. Once the high-level model is in place, the detailed modeling process takes the model table by table and drills down into the definitions, sources, relationships, data quality problems, and transformations required to populate the model. The last phase of the modeling process involves reviewing and validating the model with several interested parties. The primary goals of this process are to create a model that meets the business requirements, provides the ETL team with a solid starting point and clear direction, and verifies that the data is available to fill out the model.

Designing a dimensional model is a series of successive approximations, where you create more detailed and robust models based on your growing understanding of the source systems, the business needs, and the associated transformations. This series of iterations usually stops once the model clearly ...