Creating Calculated Cells

Creating Calculated Cells

With calculated cells, you can define a Multidimensional Expressions (MDX) formula that can be used to supply a value for each cell in a specific group of cells. Optionally, each cell in the group of cells can be evaluated against a logical MDX statement to determine whether the formula is applied for a particular cell in the group of cells.

For example, you can create a calculated cells definition that provides forecasting values for the next year from a calculation based on the actual values of the current year, but only for specific clients and products. Unlike calculated members, custom members, or custom rollups, this functionality can affect specific cells of a cube, instead of an entire member; a calculated cells definition can be created for only a single cell in an entire cube.

As with calculated members, the definitions for calculated cells are stored. The values for calculated cells are evaluated only when the cube is queried by a client application. Unlike calculated members, however, the cells retain their original underlying data. Thus, calculated cells can be added to a cube without affecting the underlying data, and calculated cells can be removed from the cube without invalidating the cube. Calculated cells are not evaluated when processing a regular or virtual cube; the cube is processed as if the calculated cells definitions do not exist.

With the introduction of calculation passes, calculated cells can be recursively applied across any number of calculation passes, with evaluation starting at a specified calculation pass. For more information about calculation passes, see Understanding Pass Order and Solve Order.

You can create calculated cells in regular or virtual cubes. For more information about creating calculated cells, see Creating Calculated Cells in Regular Cubes and Creating Calculated Cells in Virtual Cubes.

To create a calculated cells definition, use the Calculated Cells Wizard. It enables you to specify the following options for the calculated cells:

• Calculation Subcube

  Select the members from each dimension in the cube to define the calculation subcube. The calculation subcube contains the group of cells against which the calculation formula is applied. The combination of all dimensions with specified members and all other dimensions define the calculation subcube. If a given dimension does not have any specified members, all members in that dimension are considered part of the calculation subcube.

  If members are not specified from any of the dimensions of the regular or virtual cube, the calculation subcube is defined as the entire regular or virtual cube.

• Calculation Condition

  Define an MDX logical expression (an expression that evaluates to either True or False) that will be applied to each cell in the calculation subcube. If the calculation condition evaluates to True for a cell, the result of the calculation formula applied to that cell is returned when the cell is queried. If the calculation condition evaluates to False for a cell, the actual value of the cell is returned when the cell is queried.

  If no calculation condition is specified, the calculation formula will apply to all cells in the calculation subcube.

• Calculation Formula

  Define an MDX expression that will supply the value of each cell in the calculation subcube, subject to the calculation condition. The calculation formula can be any MDX expression that returns a string or numeric value.

After they have been created, calculated cells can also be edited in Cube Editor (for regular cubes) or Virtual Cube Editor (for virtual cubes).

To edit a calculated cells definition in Cube Editor