Microsoft KB Archive/304137

= INF: How to Increase the Speed of MDX Queries that Contain the NON EMPTY Keyword =

Article ID: 304137

Article Last Modified on 2/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q304137



SUMMARY
In some cases, a query slows down considerably when you use both the NON EMPTY keyword on an axis of a Multidimensional Expression together with a calculated member.

This article describes how you can optimize a query, by using the Non Empty Behavior property for the calculated member.



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.

Additional query words: OLAP BIHowTo Speed performance MDX

Keywords: kbinfo KB304137

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.