Optimization Switches Based on NONEMPTY
Several of the query optimization switches make use of the NONEMPTY function in Microsoft MDX to reduce the number of blanks in the query results. By removing “sparsity” from the query, performance can be dramatically improved in many cases. The improvement is most notable when two or more hierarchies from DIFFERENT dimensions are used on a single query axis (either columns or rows).
The following BI Office optimization switches are based on the NONEMPTY function.
- Optimize Columns
- Optimize Rows
- Measure Optimization
- Optimize Totals
- Filtering Data (see Optimize checkbox in Data Filter dialog)
- N-of-N Iterations (see Optimize checkbox in N-of-N dialog)
Limitations of NONEMPTY
When using the NONEMPTY function, the user should be aware of the following limitations.
No Results for NONEMPTY
The NONEMPTY function can sometimes return no results at all, depending on the cube structure and/or calculation logic. This scenario can typically occur in the following cases.
- There are one or more instances of "ALL levels being empty".
- MDX scope statements that programmatically return no results for the query selections.
- The fact table and dimension table intersect with no results for the selected dimensions and the default model/cube measures.
NONEMPTY Function Causes Slowdown
In some cases, the NONEMPTY function can cause queries to run slower. This can occur when the determination of sparsity takes longer than running the query itself.
Axis with Single Hierarchy
Using the NONEMPTY function for a column or row axis that has a single hierarchy can often prove counterproductive. In these cases, the NONEMPTY function should not be employed.
NONEMPTY vs. NON EMPTY
It is important to remember the differences between the NONEMPTY and NON EMPTY functions in MDX. The practical difference between these two functions is usually minimal, except for cases when:
- Cube is very large.
- Query result set is very large.
- Complex calculations are involved.
The NONEMPTY function performs optimization by attempting to eliminate sparsity in the query as part of the query’s functional design.
The "NON EMPTY" clause, applied to each axis, attempts to eliminate blank columns or rows from the final data set AFTER the result has been generated.