Blog Home  Home RSS 2.0 Atom 1.0 CDF  
Mark Mrachek - Business Intelligence Blog - MDX NonEmpty() function vs NonEmptyCrossjoin() function in Analysis Services 2005
 
 Sunday, February 19, 2006

If you have ever used the NonEmptyCrossjoin function, you may have been surprised (and frustrated) with the results your queries returned.  This function was added to Analysis Services 2000 to help with performance of queries, but it commonly returned incorrect results.  Fortunately the NonEmptyCrossjoin function is being deprecated.

 

The NonEmpty() function is new in Analysis Services 2005 and should be used instead of the NonEmptyCrossjoin function.  The NonEmpty() function is powerful because it can be applied to MDX expressions whereas the NON EMPTY clause was only available at the top level in SELECT statements.

 

This syntax for the NonEmpty() function is NonEmpty({set},[filter set])

 

I met with a developer and PM from the Analysis Services team last week in Redmond and they provided me with more information on the NonEmpty() function. 

 

If you use the NonEmpty() function without a filterset, then the expression is evaluated using the default measure in your cube.  So, if you do not define a filterset and you did not specify a default measure in your cube, then you may get unexpected results with this function.  Because, even though you may not have explicity defined a default measure, all cubes have a default measure (Analysis Services will define a default measure even if you did not define one) and the NonEmpty() function will use this default measure as the context in which to evaluate the expression.

2/19/2006 6:21:11 PM (Pacific Standard Time, UTC-08:00)  #    Comments [0]   MDX  | 
Copyright © 2009 Mark Mrachek. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.