Calculated Cells
Calculated cells are cells whose value is calculated at run time using a Multidimensional Expressions (MDX) expression that you specify when you define the calculated cells. Additionally, the expression can be conditionally applied to specific cells, based on an MDX logical expression also specified when you define the calculated cells.
Calculated cells enable you to apply the functionality previously reserved for calculated members, custom members, and custom rollup formulas to a specific range of cells, or even to a single cell, allowing you to finely tune the performance of your cube or query.
Calculated cells consist of a multidimensional section of cells, defined by an MDX set expression, to which an MDX value expression is selectively applied depending upon a condition described by an MDX logical expression.
Calculated cells are constructed from three elements:
Calculation subcube
The MDX set expression used to define the slice of the cube upon which the calculated cells feature will work. The calculation subcube is defined by a list of single dimension sets, with each set containing one of the following:
- All members of a dimension (including the Measures dimension).
Note This option typically excludes calculated members. To include calculated members, use an MDX expression that employs the AllMembers MDX function.
- A single specified member of a dimension (including the Measures dimension).
- All the members at a specified level in a dimension.
Note This option typically excludes calculated members. To include calculated members, use an MDX expression that employs the AllMembers MDX function.
- All the descendants of a specified member in a dimension.
- All the descendants of a specified member at a specified level in a dimension.
- An MDX expression that resolves into a set containing one of the above sets.
The combination of this list of sets and every other member of all other dimensions not specified in the list of sets defines the calculation subcube.
Calculation condition
The MDX logical expression that further restricts the application of the calculated cells feature. 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.
Calculation formula
The MDX value expression used to calculate the value of the cells contained in the calculation subcube.
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.
For example, the following diagram depicts a cube in which there are three dimensions (not counting the Measures dimension).
Now, after reviewing this cube, you want to perform some speculation as to the quantity of packages shipped by air on the fourth quarter for the Western Hemisphere routes. You want to compute these cells as equivalent to 125% of the quantity of packages shipped by air on the third quarter for the same routes, but only if the quantity for the 4th quarter is less than 125% of the quantity of the third quarter.
You could create a custom member for the Air member of the Source dimension, but it would affect all of the cells for that member, including those intersecting all other members, as shown in the following diagram.
This custom member would, of course, affect all of the other routes and times. Another approach would be to create a custom member for the 4th quarter member of the Time dimension, but this would affect all of the other routes and sources, as shown in the following diagram.
You could also create custom members for each of the Western Hemisphere routes, but that would affect all of the sources and times, as shown in the next diagram.
Finally, you could combine all of the preceding examples, creating multiple custom members to cover all possible intersections, as shown in the following diagram, providing a complex MDX formula for each member to derive calculated values for just two cells.
None of these workarounds really provides the flexibility needed to work with only a specific slice of a cube, as all of these affect a complete member, not a specific range of cells. With calculated cells, you can define a specific range of cells, supply a condition for applying a formula, and apply a formula for that specific range of cells.
For the example detailed earlier in this topic, using this feature, you would first define the calculation subcube as shown in the following diagram.
Then you would create a calculation condition that would check each applicable cell to determine if it was less than 125% of the cell in the previous quarter. This would eliminate one of the cells, as shown in the following diagram.
The calculation formula would then be applied to the applicable cell, returning the value for the appropriate cells as shown in the following diagram.
To create calculated cells, use the Calculated Cells Wizard or Cube Editor. For more information about the Calculated Cells Wizard and Cube Editor, see Calculated Cells Wizard and Cube Editor - Schema View.
In Analysis Manager, the following icon identifies a calculated cells definition.
Calculated cells can be created with a global scope using the Calculated Cell Wizard or Cube Editor, available to all users who browse the cube in which the calculated cells are defined. However, calculated cells can also be created with a session scope, available only to MDX queries executed within the session in which the calculated cells are created. This feature allows greater flexibility for client applications.