Professional SQL Server™ Analysis Services 2005 with MDX

Chapter 8. Advanced Dimension Design

In this look at advanced dimension design, you learn to aggregate data up to the parent member through custom rollup operations and learn about the effects of dimension and hierarchy properties. Also, you learn about business intelligence wizards which help you enhance dimensions. Finally, you are introduced to dimension writeback, which is a way to make changes to dimension members for "what-if" if analysis. Consider first the details you already learned about dimension design back in Chapter 5; you learned how dimensions, which are made up of hierarchies, consist of tiers called levels. The two types of hierarchies were described, both attribute and multi-level hierarchies, and the Time dimension and Parent-Child dimensions were discussed in some detail.

Here you learn about the ways you can leverage and extend dimensions to get even more value out of them. Normally, you would expect the data to be aggregated to its parent. For example, if you have a hierarchy such as Time, then Sales per month will be rolled up to calculate first the Sales of a quarter, and Sales Quarters will be rolled up to calculate the Sales of a year. Even though this is the most common way a user would expect the data to be aggregated, there are dimensions in which the data does not get rolled up by a simple sum. Another take-away from Chapter 5 was that parent-child hierarchies are special, and Analysis Services 2005 provides properties that help enhance dimensions ...