Overview of Calculated Cells
The value within a calculated cell is computed at run time through a specified MDX expression. The expression is specified when the calculated cell is defined. The expression can be conditionally applied to a cell or range of cells, based upon an MDX logical expression. In these cases, the logical expression is also specified at the point of definition of the calculated cell.
The advent of MSSQL Server 2000 Analysis Services witnessed the arrival of calculated cells, which did not exist in earlier versions of the application. Calculated cells allow us to perform actions that could be accomplished only by calculated members, custom members, and custom rollup formulas.
Calculated cells consist of three main elements:
- Calculation subcube
- Calculation condition
- Calculation formula
The calculation subcube is an MDX set expression that defines the slice of the cube over which the calculated cells will be in effect. A list of single dimension sets define the calculation subcube, and each of the sets contains one of the following:
All members of a dimension (including the Measures dimension). Calculated members can be included (using the AllMembers MDX function).
A single specified member of a dimension (Measures again is included).
All members at a specified level within a dimension. Calculated members can be included (again, using the AllMembers MDX function).
The descendants of a specified member within a dimension.
The descendants of a specified member at a specified level within a dimension.
An MDX expression that generates a set containing one of the above sets.
This list of dimension sets, combined with every other member of all other unspecified dimensions in the cube, defines the calculation subcube.
Within the subcube, the calculation condition is compared with each member cell. The calculation condition is an MDX logical expression that acts to further limit the effects of the calculated cells. If the condition evaluates as True for a given cell, the formula in the calculated cell is applied to the member cell that indicates True, and the cell returns the calculated value. The member cell returns its original value if the calculation condition evaluates to False. The combination of the calculated cells condition and the calculation subcube is termed the calculation scope.
The third element of a calculation cell, the calculation formula, is an MDX value expression that calculates the value of the cells that lie within the calculation subcube. So, to summarize the interaction between the parts of a calculated cell, we define a specific target range of cells, we supply a condition that must be met before applying a formula, and we apply a formula within that specific range of cells for any cells meeting the condition.
We will see the three main elements of the calculated cell in action in the practice exercises that follow.