Professional Microsoft® SQL Server® Analysis Services 2008 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 or aggregate formula, 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 aggregate (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 CustomRollupColumn, 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, though this is not a very common application in practice.

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. There are two types of depreciation you should be familiar with and understand. They are called straight-line and accelerated. Typically, businesses keep two (sometimes more) sets of accounting books, which, by the way, is completely legal. One set of books is for the IRS and one set is for investors. The books for the IRS often use accelerated depreciation because ...