Cube Space and Auto-Exists
Earlier in this topic, we defined cube space as the product of the members of its attribute hierarchies. The concept of auto-exists limits this cube space to those cells that actually exist. Members of an attribute hierarchy in a dimension may not exist with members of another attribute hierarchy in the same dimension.
For example, if you have a cube that has a City attribute hierarchy, a Country attribute hierarchy, and an Internet Sales Amount measure, the space of this cube only includes those members that exist with each other. For example, if the City attribute hierarchy includes the cities New York, London, Paris, Tokyo, and Melbourne; and the Country attribute hierarchy includes the countries United States, United Kingdom, France, Japan, and Australia; then the space of the cube does not include the space (cell) at the intersection of Paris and United States.
When querying cells that do not exist, non-existing cells return nulls; that is, they cannot contain calculations and you cannot define a calculation that writes to this space. For example, the following statement includes cells that do not exist.
SELECT [Customer].[Gender].[Gender].Members ON COLUMNS,
{[Customer].[Customer].[Aaron A. Allen]
,[Customer].[Customer].[Abigail Clark]} ON ROWS
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount].gif) Note: |
|---|
|
This query uses the Members (Set) (MDX) function to return the set of members of the Gender attribute hierarchy on the column axis, and crosses this set with the specified set of members from the Customer attribute hierarchy on the row axis.
|
When you execute the previous query, the cell at the intersection of Aaron A. Allen and Female displays a null. Similarly, the cell at the intersection of Abigail Clark and Male displays a null. These cells do not exist and cannot contain a value, but cells that do not exist can appear in the result returned by a query.
When you use the Crossjoin (MDX) function to return the cross-product of attribute hierarchy members from attribute hierarchies in the same dimension, auto-exists limits those tuples being returned to the set of tuples that actually exist, rather than returning a full Cartesian product. For example, run and then examine the results from the execution of the following query.
SELECT CROSSJOIN
(
{[Customer].[Country].[United States]},
[Customer].[State-Province].Members
) ON 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]| Note: |
|---|
|
Notice that 0 is used to designate the column axis, which is shorthand for axis(0) - which is the column axis.
|
The previous query only returns cells for members from each attribute hierarchy in the query that exist with each other. The previous query can also be written using the new * variant of the * (Crossjoin) (MDX) function.
SELECT
[Customer].[Country].[United States] *
[Customer].[State-Province].Members
ON 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]The previous query could also be written in the following manner:
SELECT [Customer].[State-Province].Members
ON 0
FROM [Adventure Works]
WHERE (Measures.[Internet Sales Amount],
[Customer].[Country].[United States])
The cells values returned will be identical, although the metadata in the result set will be different. For example, with the previous query, the Country hierarchy was moved to the slicer axis (in the WHERE clause) and therefore does not appear explicitly in the result set.
Each of these three previous queries demonstrates the effect of the auto-exists behavior in SQL Server Analysis Services.