Cube-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 187

SUM

Highest level of aggregation

SUM = Formula

= Formula SUM SUM SUM

Fact Data

FIGURE 12.2 Custom formulas (MDX calculations) are assigned to cells of a cube.

MDX Scripts

Starting from Analysis Services 2005, the way you define and store calculations inside the
cube is vastly improved and simplified. All cube-based calculations are stored in the same
location: an MDX script. Having a single location for the majority of calculations simpli-
fies development; it improves visibility and simplifies maintenance of dependencies
between calculations.

You can use Data Definition Language (DDL) or Business Intelligence Development Studio
(BI Dev Studio) to create an MDX script. Semicolons separate the commands in a script.
The script might look like the code in a procedural language, but it is indeed MDX, a
declarative language. That is, MDX commands declare calculations; after those calcula-
tions are declared, they are always in effect. When Analysis Services loads the cube, it
loads the MDX script as part of the cube. The MDX commands evaluate in the order in
which they appear in the script.

NOTE
In Analysis Services 2000, commands that create cell calculations execute according
to the properties SOLVE_ORDER and CALCULATION_PASS_NUMBER . In later versions of
Analysis Services, the order that the commands appear in the script replaces
SOLVE_ORDER and CALCULATION_PASS_NUMBER .