NON EMPTY vs NONEMPTY(<set>, <set>)

To remove NULL values from the query output we use NON EMPTY or NONEMPTY functions. There is small difference between NON EMPTY and NONEMPTY function, I am providing difference with examples through this post.

Let’s run following MDX query -

SELECT { [Measures].[INV PROD SALES ACTUAL], [Measures].[LEAD TO CALL PRODUCT ACTUAL]} ON COLUMNS, { [MEASURE TYPE].[MEASURE TYPE].Children } ON ROWS FROM [YOUR CUBE]

Result of the above query. As you see there are plenty of NULL values we got for the MEASURE TYPE set.

Now let’s use NON EMPTY on the set, here is the changed query -

SELECT { [Measures].[INV PROD SALES ACTUAL], [Measures].[LEAD TO CALL PRODUCT ACTUAL]} ON COLUMNS, NON EMPTY { [MEASURE TYPE].[MEASURE TYPE].Children } ON ROWS FROM [YOUR CUBE]

Here is the output with NON EMPTY -

As you see now wherever we had NULL for both the measure those measure types have been filtered (meaning removed). So NON EMPTY works on the overall result, meaning after set get first evaluated, all the tuples with NULL values for all the measures, are removed.

On the other hand NONEMPTY() takes 2 sets as parameters. Its uses tuples of the first set and filtered out all empty tuples based on the cross product with second set. Meaning behind the scene what happens is, NONEMPTY is evaluated when the set defining the rows axis is evaluated. Let’s analyze the query -

SELECT NON EMPTY { [Measures].[INV PROD SALES ACTUAL], [Measures].[LEAD TO CALL PRODUCT ACTUAL]} ON COLUMNS, { NONEMPTY([MEASURE TYPE].[MEASURE TYPE].Children, [Measures].[LEAD TO CALL PRODUCT ACTUAL]) } ON ROWS FROM [YOUR CUBE]

Before we get into the result, let’s analyze the above query. As we discussed above NONEMPTY will be evaluated with LEAD TO CALL PRODUCT ACTUAL measure in the ROW axis first and will bring all the MEASURE TYPE tuples wherever we have value for LEAD TO CALL PRODUCT ACTUAL measure. Also note that if we don’t pass any measure in the NONEMPTY it takes default measure and filter non empty value for the set. From the first query output I am graying out the cells that we don’t expect in the query result.

Here is the above query output.
 
NONEMPTY should be used to get rid of empty tuples in the set for specified measure and it helps improve query performance. Specially when you cross join the dimension(s) members you should always consider NONEMPTY. Leave your comments..see you back soon!