MSAS - Implementing Calculations Using MDX Part 2 Page - 3

Understanding other calculation methods

Microsoft Analysis Services supports calculations up to the most granular levels in a cube. Calculations can be defined for members of a dimension, for a combination of cells or even a single cell.

The MDX set expression used to define the slice of the cube upon which the calculated cells feature will work is called a Calculation subcube. The calculation subcube is defined by a list of single dimension sets.

The MDX logical expression that further restricts the application of the calculated cells feature is known as Calculation condition. The calculated cells condition expression is compared to each cell in the calculation subcube. If the logical expression evaluates to True for the cell, the calculated cells formula is applied and the cell returns the calculated value. If it evaluates to False, then the cell returns the original cell value. The combination of the calculation subcube and the calculated cells condition is referred to as the calculation scope.

The MDX value expression used to calculate the value of the cells contained in the calculation subcube is called a Calculation formula. This functionality sounds in many ways similar to calculated members, custom members, and custom rollup formulas, and indeed can be used in place of these features. But, calculated cells are much more than that.

Setting calculations at Member level

Right click the Expense Budget cube and click Browse Data.

Drag and drop the scenario dimension to replace the measures dimension. Note that the current year’s Budget member does not contain any data.

Close the cube browser and click the Shared dimensions folder in the Analysis Manager tree pane.

Right click the Scenario dimension and then click Edit.

Click on the Data tab to display the dimension tree.

Expand the Dimension to select the Current Year’s Budget member. Note that the Custom Member Formula pane is enabled.

Click the ellipsis(…) button to open the MDX Builder Dialog box. Expand Level02 of the scenario dimension tree, and then drag Current Year’s Actuals to the MDX expression field.

The MDX expression field is updated with the Member Key definition of the member.

At the end of the expression let us enter “*1.1”. Click Ok.

Save the changes. Notice that the Budget member has changed and shows the formulas associated with it.

Close the Dimension Editor.

Now expand the Cubes folder and right click on the Expense Budget. Click on Browse Data.

Drag the Scenario dimension to replace the measures dimension. Note that the Current Year’s Budget member now contains data. This data is calculated at 110% of the actual.

Close the Cube Browser.