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

However, because Missing Member mode is specified per dimension, you'll get an error if
you misspell the name of a dimension.
Missing Member mode is automatically turned off when you specify an expression for
dimension security, key performance indicators, actions, or cube calculation (all features
that we haven't talked about yet, but promise to in future chapters). It would cause quite a
mess if a typo in the dimension security definition caused dimension security to be ignored.
A client application can use the MdxMissingMemberMode connection string property to turn
off Missing Member mode. For example, if an application allows user input a text of an
MDX expression and needs to generate an error when a user misspells a member name,
the application would turn off the feature.
Existing and Nonexisting Tuples, Auto-Exist
Previously, we said that the space of the cube can be defined by the members of the
attribute hierarchies. In reality, however, actual space of the cube is smaller. There are
combinations of members from different attribute hierarchies that just don't exist in the
dimension table or in the cube. For example, because Nina Metz is female, we have a
record Nina Metz, F in the Customer dimension table. Therefore, the tuple
([Customer].[Gender].&[F], [Nina Metz]) exists in the Customer dimension. But, the
tuple ([Customer].[Gender].&[M], [Nina Metz]) doesn't exist. So, we have an existing
tuple and a nonexisting tuple.
It is possible to reference a tuple that doesn't exist in the cube, but it will resolve to a null
tuple internally. For example, the following query returns an empty result because the
tuple ([Customer].[Gender].&[M], [Nina Metz]) doesn't exist:
SELECT {([Customer].[Gender].&[M], [Nina Metz]) } on COLUMNS FROM [Warehouse and
Sales]
The result of executing an MDX expression can't be a nonexisting tuple. Therefore, the
system internally removes nonexisting tuples from the set in an operation we call auto-
exist . You can see the results of the system employing auto-exist in the execution of the
following CrossJoin function. If the sets participating in the CrossJoin function belong
to the same dimension, the tuples that don't exist are removed. For example, if we take a
set with two customers—Nina Metz (female) and David Wall (male)—and use CrossJoin
to combine it with a set containing a single member, [Customer].[Gender].&[M] , the
resulting set won't contain the full CrossJoin (Nina Metz, M), (David Wall, M) , but
only the single tuple (David Wall, M) :
SELECT { [Nina Metz], [David Wall] } * [Customer].[Gender].&[M] on COLUMNS
FROM [Warehouse and Sales]
WHERE [Measures].[Unit Sales]
Figure 11.5 shows the results of this query.