Dimensional Modeling Basics

Why Dimensional Modeling?
Dimensional modeling supports our two main data warehouse and BI design principles: usability and performance. (For a quick explanation of why we use dimensional modeling, see the sidebar "Modeling Myths," page 38.) The denormalization of the dimension tables improves usability by pulling all the related attributes together into one table, dramatically decreasing the total number of tables a user has to deal with. A business process that might require hundreds of tables in the OLTP system can be reduced to a dozen or so tables in its dimensional form.

From a performance perspective, dimensional models can help in several ways. Denormalizing the dimension tables during the load process reduces the number of joins the query engine must do at query time. Beyond this, the relational optimizer understands that the dimensional model is a large fact table with simple joins to several relatively small dimension tables in which the majority of constraints reside; the optimizer can formulate a query strategy that takes advantage of this structure. This strategy, known as a star join, can greatly improve the speed of most BI queries. Awareness of star-join optimization is built into the SQL Server 2005 relational engine. One of Analysis Services' main purposes is to improve performance by pre-aggregating across dimensions and up hierarchies within dimensions. Analysis Services can do this because it's dimensionally based.

Dimensional modeling is a powerful approach to building a robust, flexible data foundation for data warehousing and BI, and SQL Server 2005 wholeheartedly embraces the dimensional approach. This article only scratches the surface of dimensional modeling. To read more, consult the references listed in the Related Reading box.