Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

NonEmpty() and that all-important second parameter

with 6 comments

Here’s a question which comes up all the time – it was asked at Mosha’s MDX seminar last week, and a friend of mine asked me about it recently too – what does the NonEmpty function do if you don’t specify the second parameter?

Let’s take a look at some example queries. I think everyone knows that you can use NON EMPTY before an axis definition to remove all the empty tuples on that axis, as with:

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY
[Date].[Date].[Date].MEMBERS
ON 1
FROM [Adventure Works]
WHERE([Product].[Subcategory].&[1])

The problem comes when people assume that you can use the NonEmpty() function in the following way to get the same result:

SELECT [Measures].[Internet Sales Amount] ON 0,
NONEMPTY(
[Date].[Date].[Date].MEMBERS
)
ON 1
FROM [Adventure Works]
WHERE([Product].[Subcategory].&[1])

In a lot of cases you might not see any obvious differences between what the two uses return, but if you run the query above you can see a lot of empty rows returned so they clearly aren’t the same. So what’s happening? If you clear the cache, rerun this second query and then run a Profiler trace you can get a hint:

NEProfiler

Why are the Reseller Sales measure group partitions being hit? Because the Reseller Sales Amount measure is the default measure on the Adventure Works cube, and since we didn’t specify a measure in the second parameter for NonEmpty() it’s using the default measure to decide which dates have values or not. To fix this we can explicitly tell AS which measure to use:

SELECT [Measures].[Internet Sales Amount] ON 0,
NONEMPTY(
[Date].[Date].[Date].MEMBERS
,[Measures].[Internet Sales Amount])
ON 1
FROM [Adventure Works]
WHERE([Product].[Subcategory].&[1])

The moral here is always, always, always specify a measure in the second parameter for NonEmpty() whenever you use it. If you don’t you may get unexpected results back and you may also get poor performance, for example if the default measure comes from a very large measure group.

Oh, and as a bonus tip, don’t ever use NonEmptyCrossjoin() with AS2005 or later. It’s difficult to use and frankly unpredictable in what it does sometimes; you can always do whatever you want with NonEmpty or Exists (when specifying a measure group in the third parameter) much more reliably and just as fast.

For more information on this topic, have a look at this old-but-good blog post from Mosha:
http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx

Written by Chris Webb

November 27, 2008 at 1:55 pm

Posted in MDX

6 Responses

Subscribe to comments with RSS.

  1. hello
    nice to meet you!

    云宁

    November 28, 2008 at 5:19 pm

  2. By using the perspective instead of the cube we can use the following query and get the same desired results (without hitting the Reseller Sales measure group partitions)SELECT [Measures].[Internet Sales Amount] ON 0,NONEMPTY([Date].[Date].[Date].MEMBERS )ON 1FROM [Direct Sales]WHERE([Product].[Subcategory].&[1]) Here we have just replaced the [Adventure Works] cube in Query 2 with [Direct Sales] perspective.In perspective we have to specify the default measure and as the default measure in [Direct Sales] happens to be [Internet Sales Amount], the query does not hit the Reseller Sales measure group partitions and returns the same desired NonEmpty results.

    Gautham

    December 4, 2008 at 11:19 am

  3. If you have a very large Cube making a NULL default meausre helps in with queries that don\’t directly need to access one of the measures (i.e. querying dimensions directly). It would be good to know where a unspecified default measures could break in this situation like you described above.

    Abe

    June 17, 2009 at 3:27 pm

  4. Thanks for clarifying my long days doubt

    Arunkumar

    May 18, 2010 at 12:15 pm

  5. Nice explanation, Thanks

    Meganathan

    February 24, 2011 at 10:12 pm

  6. [...] axis in my query. I must also mention that the Nonempty function accepts a second argument and its very important that you specify this second argument even though it is not absolutely necessary for you to use the [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: