MSAS: Creating Custom Rollups

To enable proper aggregation of values along a dimension each member of the dimension needs its own aggregation rule. These rules are provided by custom roll up. Custom rollup operators provide a simple way of controlling the process of rolling up a member to its parents values. Custom rollup operators assigned to a column during the process of creating a dimension. The rollup then, uses the contents of the column as custom rollup operator for each member and is used to evaluate the value of the member’s parents.

Custom rollups are enabled when the Unary Operators property of the level is set to True. The values of the Custom Rollup are stored in the Define Unary Operator Column dialog box.

Custom Rollup Operators and Custom Member formulas are very similar but the former is much simpler. Where Custom member formulas use Multidimensional Expressions to determine the method of roll up of members, the custom rollup operator uses simple math to determine how the value of the member affects the parent. The aggregation rule consists of a single character code --(+) for addition and (–) for subtraction,( *)asterisk for multiplication and( /) for division. (~) is used to prevent the members from aggregating at all. These codes are called unary operators because each value gets its own operator.

Custom rollup expressions of a previous level are overridden by custom rollup operators. However, custom member formulas of a preceding level can override the custom rollup members of a level.

Custom rollup operators are enabled both for shared and private dimensions. This can be done by setting the Unary Operators property in the properties pane of the Dimension editor or cube editor. Clicking the Edit button beside this property’s value displays the Define Unary Operator Column dialog box. This dialog box can be used to select or create a column to store the formulas. Once this is set, it is possible to select values for Unary_operator member property for a write enabled, shared, parent child dimension in the custom member formula pane of the Dimension Editor or Dimension Browser. If the dimension is not write enabled the Formulas cannot be inserted in Analysis manager. It must be noted that if a cube has a measure whose Aggregate function property is set to Distinct Count, adding a custom rollup operator or expression to a level will cause the structure of the cube to become invalid.

Custom Rollup Formulas and Custom Member Formulas

Custom Rollup formulas and Custom member formulas are Multidimensional Expressions. They determine the cube cell values associated with members. While a custom rollup formula applies to all members (except calculated members) a custom member applies to a single member. Calculated members are not stored in the dimension table and provide additional members to the dimension table unlike Custom rollup formulas and custom member formulas.

Aggregate functions associated with measures are overridden by custom rollup formulas and custom member formulas. For example a measure uses a Sum aggregate function of the following kind. 

2001: 2100 

         First quarter::500 

         Second quarter :500 

          Third quarter : 100 

          Fourth quarter:1000 

2002: 900 

        First quarter: 100 

        Second quarter: 300 

        Third quarter: 400 

       Fourth quarter: 100

If the custom rollup formula specified is Time.CurrentMember.LastChild the result would be 

2000:1000 

2001: 100

The values for the quarters would remain unchanged.

Custom member formulas perform similar operations but are restricted to single members. The value for the fourth quarter of 2001 member in the Time dimension can be supplied by the formula

Time.[fourth quarter] *1.5

To apply custom rollup formula to only some members of the level, the IIf and RollupChildren functions are used. The RollupChildren function can roll up the children specified, using unary operators specified in the function.

Custom member formulas override custom rollup formulas. Calculated members have to be resolved before custom rollup formulas and custom rollup members are resolved. If a cube has multiple custom rollup formulas and custom rollup members, then the formulas are resolved in the order in which the dimensions have been added to the cube. The order of the dimensions can be viewed and changed using the Cube Editor window.

To specify a custom rollup formula in any level except the All Level, the Custom Rollup Formula property of the level has to be used. To specify it for the All Level the All Member Formula property of the dimension has to be used.

Custom rollup formulas can be specified both in a shared and a private dimension. If a cube contains both a shared and private dimension with custom rollup formulas, then the custom rollup formula of a private dimension takes precedence over a shared dimension’s formula.

Custom member formulas in a level can be enabled using the Custom Members property in the properties pane of the Dimension Editor(shared dimension) or Cube Editor(private dimension). Clicking the ellipsis button beside this property value displays the Define Custom Member Column dialog box in which the formulas can be defined. It must be noted that the formulas can only be created in the custom member formula pane of the Dimension editor or browser only if the dimension is write enabled.