Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

24.5. Custom Rollup Scenarios

At times you will want to run a query but you want the information to be rolled up differently than the standard aggregation up the hierarchy. In this section, we review unary operators and custom member formulas. To apply custom member formulas and unary operators, Analysis Services has column bindings that allow you to add these operator metadata. Although there is extra effort to metadata within the relational database (instead of Analysis Services directly) this allows you to simplify the application of these custom rollups down to the lowest level of granularity desired.

Some would prefer rewriting these custom rollups in MDX as part of a calculated member, and depending on your comfort level with MDX, there are both advantages and disadvantages to this approach. For example, if you wanted to apply your custom rollup only to a few employees or to only a few accounts (for example, Income Expense), writing custom MDX to be included in a calculated member may be advantageous because it would be a relatively simple IIF statement within your calculated member. If the custom rollup is applicable to a lot of accounts, you will have performance degradation due to the Analysis Services formula engine traversing through all of the different IIF statements. You could improve performance by using a SCOPE statement instead of IIF but after a while, this may become a very complex MDX statement to write and manage.

But please do note that unary operators and ...