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

You can use other cell properties to enable a client application to take advantage of some
of the advanced features of Analysis Services. For example, you can use the ACTION_TYPE
property to retrieve information about an action associated with the cell. (For more infor-
mation about actions, see Chapter 15, “Key Performance Indicators, Actions, and the
DrillThrough Statement.”)

Dealing with Nulls

In MDX, you define the resulting multidimensional space using dimension members. In
practice, it's quite possible that an MDX query would reference a member that doesn't
exist in the cube or a cell value that is empty. In scenarios such as that, you have to deal
with null values and null members.

Null Members, Null Tuples, and Empty Sets
When you write an MDX expression or an MDX query, you might specify a coordinate
using a member that lies outside of the boundaries of the cube. There are different scenar-
ios in which this might happen (for example, a query that requests the parent of a
member at the top level). To work with such scenarios, Analysis Services uses the concepts
of the null member and the null tuple:

. Analysis Services uses a null member to reference a coordinate that is outside the

cube space.

. If a tuple contains at least one null member, it's called a null tuple .

In some cases, null members and null tuples are allowed; in others, they are not. For
example, some MDX functions return an error if a null member or tuple is passed as a
parameter, such as <null_member>.Dimension ; others don't return an error:
IsAncestor(<null_member>,<member>) .

If a set contains only null tuples, it's called an empty set . If a set contains both regular and
null tuples, only regular tuples are returned to the user. For example, the following query
returns just one tuple, shown in Figure 11.3:

SELECT {[All], [All].Parent} ON COLUMNS FROM [Warehouse and Sales]

All
626866

FIGURE 11.3 Only regular tuples are returned to the user.

Let's take a look at some scenarios in which null members, null tuples, and empty sets
might occur.