Professional SQL Server™ Analysis Services 2005 with MDX

8.1. Custom Rollups

The name "custom rollup" is very much self describing. Custom refers to the "user defined" nature of a rollup, such that a measure value for a member is not a simple sum of values of its children as you move up a hierarchy. Rollup describes how those calculations typically start at the leaf or lower-level node and move (roll) up toward the root. There are several ways in which you can apply a custom rollup to a hierarchy: by using the attribute property CustomRollup column, using unary operators (used for parent-child hierarchies), and by using MDX scripts to specify custom rollup for members in a level. Note that unary operators can be used on non-parent-child hierarchies too.

A business scenario will help you better understand the need for and concept of the custom rollup. Perhaps you are familiar with the word depreciation.Technically, the definition of depreciation is "mapping an asset's expense over time to benefits gained through use of those assets." It simply means that the value of an asset decreases over time. When working on the financial side of business intelligence it is only a matter of time before you encounter the concept of depreciation, so if you don't already know it, study this carefully. As you may already know, the value of a car decreases over the years. In fact, after the moment you drive off the lot, the new car's value starts to decrease. This is a common example of depreciation.

There are two types of depreciation you should be familiar ...