Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 283

Assume you have a sales cube with a Time dimension containing a Time hier-
archy with levels Year, Quarter, Month, and Date. In this case, the fact table
contains data for each day. Analysis Services would aggregate the data for
cells corresponding to month, quarter, and year. We refer to the cube space
that is accessible to the users, and which can be manipulated through calcu-
lations, as the real cube space. Certain cells in the cube space can be ac-
cessed through MDX queries but are actually not of the real cube space. For
example, assume a Customer dimension that has attributes Name, Gender,
and Marital Status. There is customer named Aaron Flores who is Male in the
Adventure Works DW sample database. The cell corresponding to Cus-
tomer.Customer.[Aaron Flores] and Customer.Gender.Female does not exist
in the cube space:
select [Customer].[Customer].[Aaron Flores] on 0,
[Customer].[Gender].&[F] on 1
from [Adventure Works]
You can request the cell corresponding to this coordinate with the preceding
MDX query and you will get a null value. You might want to see multiple attrib-
utes of a dimension on a specific axis. If you do a cross-join of multiple attrib-
utes you will get the entire cross product of all the member of the attributes in-
volved in the cross-join. However, if you do a cross-join of attributes within the
same dimension, Analysis Services eliminates the cells corresponding to at-
tributes members that do not exists with one another in the cube space. This
specific behavior is called AUTO EXISTS, which can be interpreted as an
EXISTS function automatically being applied to attributes within the same di-
mension. The EXISTS MDX function, which you learn about later in this
chapter, takes two sets and returns members of one set that exist with one or
more tuples of one or more other sets. Analysis Services 2005 automatically
applies EXISTS for attributes within the same dimension when they are in-
cluded on the same axis. For example, if you query Internet Sales Amount
along with customers across states and countries, your MDX query will be:
SELECT [Measures].[Internet Sales Amount] on COLUMNS,
[Customer].[Country].[Country].MEMBERS *
[Customer].[State-Province].[State-Province].MEMBERS
ON ROWS
FROM [Adventure Works]