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

the customers who bought more than 10 products in each store. To do this, you can write
an MDX query:
SELECT Filter([Customer].[Customers].[Customer].members *
[Store].[Stores].[Store Country].members, [Measures].[Unit Sales] >10) ON COLUMNS
FROM [Warehouse and Sales] WHERE [Measures].[Unit Sales]
You can easily use the NonEmpty function to optimize this query so that it will remove the
empty tuples before the set is filtered:
SELECT Filter(NonEmpty([Customer].[Customers].[Customer].members *
[Store].[Stores].[Store Country].[Canada],
[Measures].[Unit Sales]), [Measures].[Unit Sales] >10) ON COLUMNS
FROM [Warehouse and Sales] WHERE [Measures].[Unit Sales]
NOTE
The NonEmpty function in Analysis Services 2005 replaces NonEmptyCrossjoin from
earlier versions because in some advanced scenarios, NonEmptyCrossjoin returns
unexpected results.
For example, NonEmptyCrossjoin doesn't work with calculated members or other cube
calculations. (You'll find information about those in Chapter 12, “Cube-Based MDX
Calculations.”)
If you have been using the NonEmptyCrossjoin function in your MDX expressions, we
strongly recommend that you replace it with NonEmpty .
At first glance, the NonEmpty function and the NON EMPTY operator seem to be the same
thing, but they are executed in different contexts. Queries that might look similar can
produce different results. Let's take a look at two queries, one using NonEmpty and another
using NON EMPTY :
SELECT [Time].[Time].[Year].[1997] ON COLUMNS,
NonEmpty ([Store].[Stores].[Store Country].members) ON ROWS
FROM [Warehouse and Sales]
and
SELECT [Time].[Time].[Year].[1997] ON COLUMNS,
NON EMPTY [Store].[Stores].[Store Country].members ON ROWS
FROM [Warehouse and Sales]
The difference between these two is just one space—the one between non and empty —but
those queries return different results. You can see both of those results a little later on, in
Figures 11.12 and 11.13.