Advanced MDX - Microsoft SQL Server 2008 Analysis Services - page 182

total—aggregated values. Let's take the Time hierarchy of the Time dimension as an
example. Look at Figure 11.28 for an illustration.

All ?

1997

Q1

Q2

Q3

Q4

1998 ?

Q1

Q2

Q3

Q4

FIGURE 11.28

The Time hierarchy appears as a tree.

As shown in Figure 11.28, the Time hierarchy has the member ALL on the top level. On
the next level— Year —there are two members: 1997 and 1998 . On the third level—
Quarters —there are eight members, and so on down the tree. If we query Sales Counts
for 1997, Analysis Services will return an aggregated value of all the children of the
member 1997 (that is, the quarters of the year). The same applies to the year 1998.

The cell value for the coordinate at the top of the tree—the member ALL —will have an
aggregated value of the years 1997 and 1998. So far, so good. Let's look at a query that is
executed in the context of a subcube that restricts our space to just two members on the
third level— Q2 and Q3 of the year 1998 .

By default, when Analysis Services calculates the value of the top-level cell, it aggregates
the values of the cells that correspond to the child members that exist with a subcube. So,
in our example, Analysis Services would aggregate the values of Q2 and Q3 . Visually, the
total values seem correct. You'd never know that there were two quarters left out. This
behavior of the system is called visual totals .

There are many applications of visual totals. For example, it is used when dimension secu-
rity is turned on. Or a client application can use a VisualTotals MDX function to achieve
visual totals behavior. (If you're familiar with Analysis Services 2000, the concept of visual
totals is not new.)