MDX : Non Empty v/s NonEmpty
User Rating: / 49
PoorBest 
Written by Jason Thomas   
Friday, 07 May 2010 00:44

Reposted from Jason Thomas blog with the author's permission.

The last few months, recession worries have allayed and it is common to see the Talent Acquisition guys of our company stalking us techies to take interviews for lateral hires at odd hours. Now spending a beautiful Saturday morning + afternoon taking interviews is not my cup of tea but then their persistence paid off and finally I agreed to come for taking the interviews. That is when I thought of giving my readers a sneak peek of my list of interview questions, one by one per post.

One of my favourite questions in MDX is the difference between Non Empty and NonEmpty because even though many people use them daily to remove NULLS from their queries, very few understand the working behind it. Many times, I have even got answers like “there is a space between Non and Empty, that is the difference”. The objective of this post is to clearly differentiate between the two.

Let us say my initial query is

SELECT 
  {
    [Measures].[Hits]
   ,[Measures].[Subscribers]
   ,[Measures].[Spam]
  } ON COLUMNS
,{
    [Geography].[Country].Children
  } ON ROWS
FROM [Blog Statistics];

This will give the following output

Full query results

NON EMPTY

Non Empty is prefixed before the sets defining the axes and is used for removing NULLs. Let us see what happens when we add Non Empty on the Rows axis.

SELECT 
  {
    [Measures].[Hits]
   ,[Measures].[Subscribers]
   ,[Measures].[Spam]
  } ON COLUMNS
,NON EMPTY 
    {
      [Geography].[Country].Children
    } ON ROWS
FROM [Blog Statistics];

The output is shown below

Non Empty on Rows

 

 

 

You will notice that Chile (CL) has been filtered out while rows like UK, Canada, etc are still there even if they have NULLs for some of the measures. In short, only the rows having NULL for all the members of the set defined in the column axis is filtered out. This is because the Non Empty operator works on the top level of the query. Internally, the sets defined for the axes are generated first and then the tuples having NULL values are removed. Now that we know how NON EMPTY works, it shouldn’t be hard for us to tell the output of the below query

SELECT 
  NON EMPTY 
    {
      [Measures].[Hits]
     ,[Measures].[Subscribers]
     ,[Measures].[Spam]
    } ON COLUMNS
,{
    [Geography].[Country].Children
  } ON ROWS
FROM [Blog Statistics];

The output is shown below

Non Empty on Columns 

NONEMPTY()

The NonEmpty() returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. Suppose we want to see all the measures related to countries which have a non-null value for Subscribers

SELECT 
  {
    [Measures].[Hits]
   ,[Measures].[Subscribers]
   ,[Measures].[Spam]
  } ON COLUMNS
,{
    NonEmpty
    (
      [Geography].[Country].Children
     ,[Measures].[Subscribers]
    )
  } ON ROWS
FROM [Blog Statistics];

This will give the following output

NonEmpty on Subscriber measure 

As you can see, the NonEmpty operator takes all the rows having a not NULL value for Subscribers in the rows and then displays all the measures defined in the column axis. Basically what happens internally is that NonEmpty is evaluated when the sets defining the axis are evaluated. So at this point of time, there is no context of the other axes. What I said now can be better understood from the following example

NonEmpty second example

Now, we write the below query

SELECT 
  {[Date].[Month].[March]} ON COLUMNS
,{
    [Geography].[Country].Children
  } ON ROWS
FROM [Blog Statistics]
WHERE 
  [Measures].[Hits];

Output is given below

NonEmpty second example - March 

Think for a while and predict which all rows would be returned when the NonEmpty operator is applied on the rows

SELECT 
  {[Date].[Month].[March]} ON COLUMNS
,{
    NonEmpty([Geography].[Country].Children)
  } ON ROWS
FROM [Blog Statistics]
WHERE 
  [Measures].[Hits];

 If you guessed just IN, US, GB and AU, please go back and read once again. If you replied All rows except Chile, full marks to you, you have been an attentive reader. The reason is because NonEmpty is evaluated when the set defining the axis is evaluated (here, Country) and at that point of time, NonEmpty is evaluated for each member of the country against the default member of the Date dimension (which would be ALL generally). As you can see, we already have values for CA and AP for other months and hence they will not be filtered out.

 

Optimizing Non Empty by using NonEmpty

Ok, now you know how Non Empty and NonEmpty works internally and we can apply this knowledge to optimize our queries. Suppose there is a complex logic in our axes like finding all the countries that have 30 or more hits in any month. The query is given below

SELECT 
  {[Measures].[Hits]} ON COLUMNS
,{
    Filter
    (
        [Geography].[Country].Children
      * 
        [Date].[Month].Children
     ,
      [Measures].[Hits] > 30
    )
  } ON ROWS
FROM [Blog Statistics];

Now my time dimension will have 10 years of data, which means around 120 (10*12) members for the month attribute and my country attribute may have let’s say, 100 members. Now even though I just have 3 months of data for 10 countries for hits, the filter function will need to go through all the combinations of country and month (120*100 combinations). Instead of that, we can just use the NonEmpty operator and bring down the combinations to less than 30 (3 months*10 countries) by using the below query

SELECT 
  {[Measures].[Hits]} ON COLUMNS
,{
    Filter
    (
      NonEmpty
      (
          [Geography].[Country].Children
        * 
          [Date].[Month].Children
       ,[Measures].[Hits]
      )
     ,
      [Measures].[Hits] > 30
    )
  } ON ROWS
FROM [Blog Statistics];

 


jason-thomas

Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients. He is currently working for Mariner and is based out of Charlotte, NC. His personal blog can be found at http://road-blogs.blogspot.com


 

 
Comments (10)
10 Wednesday, 22 January 2014 11:30
Rajendra
Nice explanation
9 Thursday, 02 January 2014 18:56
AVANADE Guy
Agreed. Nice explanation. The impact on performance is extremely important when work with attributes/levels with thousands of members. I recently had a NON EMPTY query that was running around 19 minutes tuned to under 10 seconds with NONEMPTY.
8 Thursday, 03 January 2013 05:00
Preeti
Nice explanation
7 Tuesday, 06 March 2012 10:49
Sree1234
Very Usefull ....Here we have one more like 'non empty behavior' it is good to have example for 'non empty behavior'.
6 Sunday, 21 August 2011 10:16
Soniya
Very informative ... thanks
5 Tuesday, 09 August 2011 13:04
Sudhanshu Sekhar Padhan
One helpful article to avoid confusion between Non Empty and Nonempty. Thanks
4 Tuesday, 16 November 2010 12:41
165083
Could you provide information on how to implement the non empty in cube design
3 Tuesday, 16 November 2010 09:21
roopesh babu v
kudos .. really a nice article .. thx for it ..
2 Friday, 14 May 2010 13:39
Jason Tom Thomas
Could you please let me know which example you are referring to, maybe I could post the explanation in the comments section itself.
If you are speaking of the image with mouseover as Nonempty second example, I am actually considering it as the source for my queries below.
1 Tuesday, 11 May 2010 16:47
Saviour Faire
Interesting article, but some of the examples, code and samples, do not seem to make sense because the explanations were not provided.
For example, the last "NON EMPTY" example query and results were not explained, and do not make sense. ie: "spam" column not shown and "Chile" is shown but empty, but these results not explained. As a student of ssas and mdx, based on the author's explanation provided I would be lead to believe a different result would have happened.
As a student of the software, my interest as been piqued and I'll investigate further.

Tags: article, mdx
 

Pyramid Analytics