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

David Wall

M

44

FIGURE 11.5 Analysis Services converts a nonexisting tuple to a null tuple.

In Analysis Services, you can place members of the same dimension, but of different hier-
archies, on different axes. In the following query, we have a cell referenced by tuples on
different axes and the crossjoin of those tuples doesn't exist:

SELECT { [Nina Metz], [David Wall] } ON COLUMNS, [Customer].[Gender].&[M]
ON ROWS FROM [Warehouse and Sales] WHERE [Measures].[Unit Sales]

Contrary to what you might expect, Analysis Services doesn't remove the cell referenced
by the tuple— ([Nina Metz], [Customer].[Gender].&[M]) —from the resultset, but the
cell produced by that combination of tuples will be a nonexisting cell. A nonexisting cell
is not just an empty cell; it can't be written back to and you can't define a calculation on
the nonexisting cell.

Let's look at an example of a query that produces nonexisting cells. If we place an existing
set of two customers— Nina Metz and David Wall —on one axis and a set that contains the
member [Customer].[Gender].&[M] on the other axis, the result of the query will be two
cells. But the cell produced by the intersection of Nina Metz and Gender Male is a nonex-
isting cell. You can see this result in Figure 11.6.

Analysis Services doesn't perform auto-exist between the sets of the axes. But, it does
perform auto-exist on the sets projected on the axes with the set projected on the WHERE
clause. For example, if we put the same set— {[Nina Metz], [David Wall]} —on the axis

Nina Metz
(null)

David Wall
44

M

FIGURE 11.6 The cell produced by the intersection of Nina Metz and Gender Male is a

nonexisting cell.