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

1997
(null)

Canada

Mexico (null)

227238

USA

FIGURE 11.12 The NonEmpty function produces null values for Canada and Mexico.

If we analyze the context in which the two algorithms are applied, we can see the ratio-
nale for each. The NonEmpty function is evaluated when the set that is placed against the
ROWS axis is evaluated. This evaluation is done independently of an evaluation of the set
that is placed against the COLUMNS axis. In our query, the set of the ROWS axis references
only the Store dimension, not the Time dimension. So, the NonEmpty function is evalu-
ated in the context of the default member ALL of the Time dimension. Therefore, the
NonEmpty function is calculated for the values of total sales in each country. The values are
not empty for both Canada and Mexico.

Therefore, the tuples for Canada and Mexico are not removed from the set after the
NonEmpty function is applied. When the actual cell values are calculated, however, they are
calculated for the intersection of the COLUMNS and ROWS axes. The current coordinate of the
Time dimension, therefore, is the year 1997 (where we have no data for either Canada or
Mexico), and thus we end up with null values, as you can see in Figure 11.12.

On the other hand, the NON EMPTY operator takes into consideration the tuples from all
the axes. Therefore, when the NON EMPTY algorithm is applied, the results are calculated in
the context of the year 1997, for which we have no data for Canada and Mexico. So, the
NON EMPTY algorithm removes the tuples Canada and Mexico from the results, as you can
see in Figure 11.13.

1997
227238

USA

FIGURE 11.13 The Non Empty operator removes Canada and Mexico from the results.

Type Conversions Between MDX Objects

MDX in Analysis Services 2000 is strongly typed. If a function or expression is defined to
accept an object of a particular type, you have to explicitly convert the object to that type
(if such a conversion is possible, of course) before you can pass it to the function or
expression. Let's take a look at the syntax of a SELECT statement:

SELECT <set> ON COLUMNS, <set> on ROWS FROM <cube> WHERE <set>