Professional SQL Server™ Analysis Services 2005 with MDX

Chapter 5. Dimension Design

Prior to the advent of cable, when you brought home a new television, the first order of business was to manually tune in one of the few existing local channels. To accomplish this you manipulated the dials, rabbit-ear antennae positioning, and other controls to eventually obtain an optimal picture, audio, and vertical hold configuration. The process of designing a data warehouse using Analysis Services 2005 is similar to this. Analysis Services 2005 provides you with various wizards that help you build the initial framework, just like the rotary tuner on the television got you close to the desired channel. With the basic infrastructure in place, some fine-tuning can optimize the initial framework to your needs. In fact, you saw this approach in the previous chapter when you learned about creating data sources and DSVs. Likewise, here you learn creating dimensions using the Dimension Wizard and then using the Dimension Designer to fine-tune the dimension based on your business needs.

Cubes are made of dimensions and measures where the measures are aggregated along each dimension. Without an understanding of dimensions and how measures are aggregated along dimensions, you can't create and exploit the power of cubes, so let's jump right in learning about building and viewing dimensions. Once the dimensions are created they need to be added to the cube and the right relationship type between the fact data and dimension needs to be defined. Analysis Services ...