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.

  fig 1

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

fig 2

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

  fig 3

We  get the following result set with Non Empty :

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:

fig 5

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

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():

                                                                  

                         Non Empty (Result)                                                                   NonEmpty (Result)