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

CHAPTER 12

IN THIS CHAPTER

. MDX Scripts

Cube-Based MDX
Calculations

. Calculated Members

. Assignments

. Named Sets

. Order of Execution for Cube

Calculations

W hen we discussed calculations of cell values and
Multidimensional Expressions (MDX) in the Chapter 11,
“Advanced MDX,” we assumed that those values derive
from the fact data. Indeed, in simple cubes, the data for the
lowest granularity (leaf cells) is equivalent to the physical
data—fact data. And data for higher granularity—aggregated
values—is calculated using an aggregate function associated
with a measure.

Usually, this function is an additive formula such as SUM ,
MIN , MAX , or COUNT , but it can be one of the more complex
built-in aggregate functions (semi-additive measures) that
Analysis Services supports. (We introduce semi-additive
measures in Chapter 13, “Dimension-Based MDX
Calculations.”) Figure 12.1 shows how fact data can be
aggregated using a simple SUM formula.

In addition to those built-in formulas, Analysis Services
enables designers and MDX developers to define their own
calculation formulas (MDX calculations).

To understand the concept of MDX calculations, imagine
an Excel spreadsheet in which each cell contains either a
value or a formula. Similarly, in a multidimensional cube's
cell, you can have either a value derived from fact data or a
custom formula for the calculation of the value. Figure 12.2
illustrates the use of both additive SUM formulas and custom
MDX calculations (shown in the figure as =Formula ).