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

2.1. Dimensional Modeling Concepts and Terminology

There is broad agreement in data warehousing and business intelligence that the dimensional model is the preferred structure for presenting information to users. The dimensional model is the best way to meet our primary design goals:

• To present the needed information to users as simply as possible

• To return query results to the users as quickly as possible

• To provide relevant information that accurately tracks the underlying business processes

Albert Einstein captured the main reason we use the dimensional model when he said, "Make everything as simple as possible, but not simpler." As it turns out, simplicity is relative. The dimensional model is much easier for users to understand than the typical source system normalized model even though a dimensional model typically contains exactly the same content as a normalized model. It has far fewer tables, and information is grouped into coherent business categories that make sense to users. These categories help users navigate the model because entire categories can be disregarded if they aren't relevant to a particular analysis.

Unfortunately, as simple as possible doesn't mean the model is necessarily simple. The model must reflect the business, and businesses are typically complex. If you simplify too much, typically by presenting only aggregated data, the model loses information that's critical to understanding the business. No matter how you model data, the intrinsic complexity ...