Multidimensional Expressions (MDX) provides you with a number of tools for generating calculated values, such as calculated members, custom rollups, and custom members. Although powerful and versatile features, they provide limited functionality because they affect members, not cells. It is difficult to affect a specific set of cells, or a single cell for that matter, using these features.
The calculated cells feature provides this functionality by allowing you to define a specific slice of cells, called a calculation subcube, and apply a formula to each and every cell within the calculation subcube, subject to an optional condition that can be applied to each cell.
Calculated cells take advantage of the pass order feature in Microsoft® SQL Server™ 2000 Analysis Services to provide such complex functionality as goal-seeking formulas, by allowing recursive passes to be made with calculated cells, with calculation formulas applied at specific passes in the pass order.
In terms of creation scope, calculated cells are similar to calculated members in that calculated cells can be made globally available as part of a cube, or temporarily created for the lifetime of either a session or a single query.
To create calculated cells as part of a cube, use the CREATE CELL CALCULATION statement. For existing cubes, the ALTER CUBE statement can also be used to add calculated cells.
To create calculated cells for the lifetime of a session, use the CREATE CELL CALCULATION statement.
To create calculated cells for the lifetime of a query, use the WITH statement.
ALTER CUBE Statement
This statement allows client applications to control the structure of a cube after it has been created.
For updating the hierarchy of a calculated member
ALTER CUBE
UPDATE DIMENSION MEMBER
For updating user defined default members
ALTER CUBE
UPDATE DIMENSION
For updating dimensions
CREATE CELL CALCULATION Statement
This statement creates a calculated cell formula for a specified set of tuples within a cube.
| ALTER CUBE
[,
Using WITH to Create Calculated Cells
Similar to the way it is used in calculated members, the WITH keyword in Multidimensional Expressions (MDX) is used to describe calculated cells.
The following syntax is used to add the WITH keyword to the MDX SELECT statement:
[WITH
[ ...]]
SELECT [
[, ...]]
FROM []
[WHERE []]
The
::= CELL CALCULATION
FOR '()'
AS ''
[,]
The
::= '
[, = ''...]
The
| Category | Description |
|---|---|
| Empty set | An MDX set expression that resolves into an empty set. In this case, the set is ignored. |
| Single member set | An MDX set expression that resolves into a single member. |
| Set of level members | An MDX set expression that resolves into the members of a single level. An example of this is the «Level».Members MDX function. To include calculated members, use the «Level».AllMembers MDX function. |
| Set of descendants | An MDX set expression that resolves into the descendants of a specified member. An example of this is the Descendants(«Member», «Level», «Desc_flags») MDX function. |
If a dimension is not described in the
The
The
The following properties apply specifically to calculated cells.
| Property | Description |
|---|---|
| CALCULATION_PASS_DEPTH | The pass depth for the calculation formula, this property determines how many passes are needed to resolve the calculation formula. |
| CALCULATION_PASS_NUMBER | The pass number for the calculation formula, this property determines on which pass the calculation formula will begin calculation. The default for this property is 1. |
| CELL_EVALUATION_LIST | The semicolon-delimited list of evaluated formulas applicable to the cell, in order from lowest to highest solve order. |
| CONDITION | The calculation condition of the calculated cells, this property receives an MDX logical expression, which is evaluated on each cell in the calculation subcube. If it returns True, the calculation formula is applied and the cell returns the resulting value. If it returns False, the cell returns the original cell value. If not specified, CONDITION defaults to True (in other words, the calculation formula applies to all cells in the calculation subcube). |
| DESCRIPTION | A human-readable text description of the calculated cells definition. |
| DISABLED | A Boolean property which indicates whether or not the calculated cells are disabled. DISABLED defaults to False. |
Other standard cell properties, such as FORE_COLOR and BACK_COLOR, can be used as well.
Additional Considerations
The calculation condition, specified by the CONDITION property, is processed only once, depending on the creation scope of the calculated cells definition. This provides increased performance for the evaluation of multiple calculated cells definitions, especially with overlapping calculated cells across cube passes.
If created at global scope, as part of a cube, the calculation condition is processed when the cube is processed. If cells are modified in the cube in any way, and the cells are included in the calculation subcube of a calculated cells definition, the calculation condition may not be accurate until the cube is reprocessed. This can occur through the use of writebacks, for example. The calculation condition is reprocessed when the cube is reprocessed.
If created at session scope, the calculation condition is processed when the statement is issued during the session. As with calculated cells definitions created globally, if the cells are modified, the calculation condition may not be accurate for the calculated cells definition.
If created at query scope, the calculation condition is processed when the query is executed. The cell modification issue applies here, as well, although data latency issues are minimal at best due to the low processing time of MDX query execution.
The calculation formula, on the other hand, is processed whenever an MDX query is issued against the cube involving cells included in the calculated cells definition, no matter the scope.
