Optimizing MDX queries using the NonEmpty() function

Both the NonEmpty() function and the NON EMPTY keyword can reduce sets, but they do it in a different way.

The NON EMPTY keyword removes empty rows, columns, or both, depending on the axis on which that keyword is used in the query. Therefore, the NON EMPTY operator tries to push the evaluation of cells to an early stage whenever possible. This way, the set on axis is already reduced and the final result is faster.

Take a look at the initial query in this recipe, remove the Filter() function, run the query, and notice how quickly the results come, although the multi–dimensional set again counts millions of tuples. The trick is that the NON EMPTY operator uses the set on the opposite axis, the columns, to reduce the set on rows. Therefore, it can be said that NON EMPTY is highly dependent on members on axes and their values in columns and rows.

Contrary to the NON EMPTY operator found only on axes, the NonEmpty() function can be used anywhere in the query.

The NonEmpty() function removes all the members from its first set, where the value of one or more measures in the second set is empty. If no measure is specified, the function is evaluated in the context of the current member.

In other words, the NonEmpty() function is highly dependent on members in the second set, the slicer, or the current coordinate, in general.

If a second set in the NonEmpty() function is not provided, the expression is evaluated in the context of the current measure at the moment of evaluation, and current members of attribute hierarchies, also at the time of evaluation. In other words, if you are defining a calculated measure and you forget to include a measure in the second set, the expression is evaluated for that same measure which leads to null, a default initial value of every measure. If you are simply evaluating the set on the axis, it will be evaluated in the context of the current measure, the default measure in the cube, or the one provided in the slicer. Again, this is perhaps not something you expected. In order to prevent these problems, always include a measure in the second set.

The NonEmpty() function reduces sets, just like a few other functions, namely Filter() and Existing(). But what's special about NonEmpty() function is that it reduces sets extremely efficiently and quickly. Because of that, there are some rules about where to position NonEmpty() function in calculations made by the composition of MDX functions (one function wrapping the other). If we are trying to detect multi-select, that is, multiple members in the slicer, NonEmpty() function should go inside, with the EXISTING function/keyword outside. The reason is that although they both shrink sets efficiently, the NonEmpty() function works great if the set is intact. The EXISTING keyword is not affected by the order of members or compactness of the set. Therefore, the NonEmpty() function should be applied earlier.

You may get System.OutOfMemory errors if you use the CrossJoin() operation on many large hierarchies because the cross join generates a Cartesian product of those hierarchies. In that case, consider using the NonEmpty() function to reduce the space to a smaller subcube. Also, don't forget to group the hierarchies by their dimension inside the cross join.