INF: How to Increase the Speed of MDX Queries that Contain the NON EMPTY Keyword (304137)

MORE INFORMATION

Use of the NON EMPTY keyword on a MDX statement causes the calculated member to evaluate the calculated member's expression for each member to determine whether or not the member is empty. The extra time taken for the evaluation is what causes the NON EMPTY keyword to slow down the MDX query.

To optimize a query that uses the NON EMPTY keyword, set the Non Empty Behavior property to a base measure of the cube, so that if the base measure is empty the calculated member's value is considered empty and the expression is never evaluated, which increases the query performance.

How to Enable the Non Empty Behavior Property

To enable the Non Empty Behavior property, use these steps:

1. Right-click the Sales cube, and then click Edit.
2. In the Cube Editor, scroll down to the Calculated Members folder.
3. By default, the Calculated Members folder is expanded. Select the calculated member Sales Average, and then click Properties to open the Properties pane for the Sales Average calculated member.
4. In the list of properties, locate the Non Empty Behavior drop-down list box and click Store Count.
5. Save the cube.