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:
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
hello
nice to meet you!
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.
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.
Thanks for clarifying my long days doubt
Nice explanation, Thanks