MDX #1 – Why should we always provide set_expression2 in NonEmpty() function
NonEmpty() is a very powerful MDX function for improving your MDX query performance. It is primarily used to remove unwanted empty sets in a very fast manner.
According to MDX Function Reference, it 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. The syntax is simple enough:
Look closely, you will see that the usage of the set_expression2 is optional. I’ve been using NonEmpty() without the set_expression2 for many of my SSRS reports. Going from “out-of-memory” on the report server to only a few seconds to render my reports, I was so happy that I’ve never thought about testing record counts or some measures, until I finally had time.
What I saw in my test results shocked me. After some research and reading, I realized that the problem is in the omission of the set_expression2. By including the appropriate set_expression2, I got the perfect results.
In this blog, I’ll try to share with you this little lesson I’ve learned.
Non-empty is a characteristic of the cells references by the tuples, not the tuples themselves
This is an important note from the above MDX Function Reference from Microsoft. Now that I am reading this note, I start to visualize:
1) set_expression1 will be typically the cross product of members of some hierarchies, for example,
DEDESCENDANTS([Queue].[Queue].[Level 03].[Online Email], [Queue].[Queue].[Level 05], self) *
2) Since the non-empty is really a characteristic of the cells, and we want to remove sets that have no certain measures, does that that mean we really need to specify the measures we are referring to? Now it makes sense to me that we do need to specify the measures we are referring to.
If a second set is not provided, the expression is evaluated in the context of the current coordinates of the members of the attribute hierarchies and the measures in the cube
This is another important note from the above MDX Function Reference from Microsoft. So if the second set is not provided, we are blindly relying on the Analysis Services to find these “current coordinates”. “Current” really means at the moment of evaluation. Unfortunately, these “current coordinates” can be pretty random at the moment of evaluation, depending on how we setup our default members, and especially if we are defining calculated measures in our query. The default initial value of any calculated measure is null. This can lead to a very messy, confusing, and unexpected query results.
set_expression2 is not provided – wrong result
This is part of my initial query, where the second set expression is not provided.
Although the NonEmpty() function is used, rows with all nulls are plainly showing. Further testing showed that the record counts and measures are incorrect.
set_expression2 is provided – All looks good!
This is part of my revised query, where the second set expression is now provided with a measure.
There is no longer any rows that are all null. Further testing showed that all numbers are correct.
Conclusion – Always include a measure in the second set in NonEmpty() function
I will put this lesson in my MDX best practice list. Hope you will do the same.