Difference between Non Empty(Keyword) and NonEmpty (Function)

Since this is my first blog, I would like to introduce myself. I am Nitin Gulati working as a Business Intelligence Consultant at Aspect Software.I have around 5 years experience in IT field. I started working as an ETL developer ( mostly on IBM DataStage) and then after 2 yrs I got an opportunity to work on Microsoft Business Intelligence (SSIS,SSAS,SSRS). Since then I am working on different aspects of BI and learning about it more and more everyday. I really love working as a BI consultant and how I help our clients to analyze data with different perspective, which is amazing.

Last night, I was reading my favorite book “Microsoft SQL Server 2008 Analysis Services Unleashed” and I came across the most common topic and most frequently asked question “ What is the difference between Non Empty and NonEmpty ? ” I will try to explain it in a very simple language.

Non Empty (keyword) : Definition ( As per BOL/ Books) :  Removes tuples for which all the cells are empty. Hence, it removes only columns or rows in which ALL the cells are empty.

When we execute Non Empty it operates on top level of the query meaning it creates the set based upon the axis in the query and then removes the empty tuples. Here is an example : The query shows sales for July 2004 for different product category.

image  fig 1

When we run this query , we get the following result set:

image fig 2

Where as now , if we use Non Empty on the row axis :

image  fig 3

We  get the following result set with Non Empty :

image fig 4

So, this is how it works when we use Non Empty :

  1. When we run the Query at fig 3.
  2. Internally AS generates the query output as shown in fig 2
  3. Now the Non Empty keyword plays its role and removes all the empty tuples of that empty row ( Removes  rows in which all the cells are empty).
  4. Non Empty results are calculated in context of column July 2004. Since, it doesn’t have any sales for both Bikes and Components. Therefore, both are removed from the result set and we get the output as shown in fig 4.

The above operation shows that Non Empty keyword is both time and memory intensive. So, it should be used cautiously.

Now, Lets discuss about NonEmpty function which was introduced in AS 2005

NonEmpty() function returns the set of tuples that are not empty from the specified set, based on the cross product of the specified set with a second set. Lets take the same example as above using NonEmpty() function:

image fig 5

When we run this query we get the following result set :

image fig 6

Did you notice even though we are using NonEmpty() on [Measures].[Internet Sales Amount] ,we are still getting Bikes displayed as output with no sales at all ?

This is how it works :

  1. NonEmpty() is evaluated when the set that is placed on the ROW axis is evaluated independent of the column axis.In our example query , the NonEmpty() evaluates Product dimension in the current context and not Date dimension i.e. ( [Product].[Category].members,[Date].[ALL],[Measures].[Internet Sales Amount])
  2. NonEmpty() evaluates sales for all time periods for all the product categories. If a category has sales for any time , they will not be removed.
  3. The above result set reflects that there had been sales for Bikes in some other time periods , so it’s not removed and Components category  which doesn’t have sales for all time periods is removed.

A quick look at the results of both Non Empty (keyword) and NonEmpty():

              image                                                     image

                         Non Empty (Result)                                                                   NonEmpty (Result)


About nitinbi
Senior Business Intelligence Consultant @ Aspect Software

6 Responses to Difference between Non Empty(Keyword) and NonEmpty (Function)

  1. karan says:

    Good work Mr. Gulatio….like the way you have explained in very simple words….yes SQL 2008Unleash is my fav book…too…and what a coincidence that i am Gulati too 😉

  2. HY says:

    good stuff sir

  3. nitinbi says:

    Thanks Guys !
    @Karan: Nice to meet you !

  4. Suneel says:

    Hi nice article…and good explanation…you rock man

  5. jainmr says:

    Good article. To the spot explanation.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: