Building Calculated Cells in MDX
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.
For more information on pass order, see Understanding Pass Order and Solve 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.