articles >> business intelligence >> Optimizing Microsoft SQL Server Analysis Services: MDX ...

Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Optimizing NON EMPTY

By : William E. Pearson, III Apr 20, 2004

Page 3 / 3


The Calculated Member Builder opens. We see the expression that determines the value of the Employee Salary calculated member in the Value expression box. Part of the value is the Org Salary measure, as depicted in Figure 7.

Figure 7: The Org Salary Measure is Part of the Value Expression

We have determined that the Org Salary measure is directly associated with the Employee Salary calculated member. We will put this information to use in the next steps.

12. Click OK to close the Calculated Member Builder.

13. Click the Non Empty Behavior property, just above the Value property, on the Properties pane – Basic tab.

14. Select Org Salary in the drop-down selector to the right of the Non Empty Behavior property label.

The Non Empty Behavior property, with new setting, appears as shown in Figure 9.

 

Figure 8: Non Empty Behavior Property Setting

Org Salary, as we saw in the Calculated Member Builder, is the base measure of the calculated member. Setting the Non Empty Behavior property to the base measure optimizes a query that applies the NON EMPTY keyword to this calculated member. This works because we are telling Analysis Services, with this setting, that, when it encounters the Employee Salary calculated member in a query that uses the NON EMPTY keyword on the same axis, it is not to evaluate the member, but it is, instead, to look to the referenced measure. If it determines the base measure Org Salary to be empty, it is to consider the calculated member empty automatically.

This makes sense in most scenarios (although not always), because if the base measure for a calculated member is empty, the calculated measure itself will typically be empty. Skipping the evaluation of the member means dramatic performance enhancement, in most cases.

15. Select File ---> Save to save changes to the cube as desired.

16. Select File ---> Exit to close the Cube Editor.

17. Exit Analysis Manager when ready.

Summary

In this article, we extended our performance enhancement toolset by adding a property setting for optimizing our MDX queries. We discussed the use of NON EMPTY in our queries in general, commenting on its usefulness in rendering more user-friendly presentations of datasets. We then exposed the cause for the performance degradation that can occur when we apply this popular keyword in a query axis involving a calculated member.

We then performed a hands-on exercise where we set the Non Empty Behavior property for a calculated measure we selected from the HR cube of the sample FoodMart 2000 database that accompanies the installation of MSAS. Finally, we commented upon the operation of the property setting, explaining how it works to eliminate the overhead that otherwise accrues within the context of the NON EMPTY keyword / calculated member combination.

Throughout our Optimizing MSSQL Server Analysis Services series, we will continue to add new MDX Optimization Techniques segments from time to time, on an ad-hoc basis. Many articles will involve a combination of MSAS settings and structural considerations (the property setting we have manipulated in this article is an excellent example) together with methods and strategies we can employ in MDX queries. Optimization of query performance is the objective, and the targeted audiences are developers and consumers alike.

I discover approaches to more efficient MDX, and enhanced MSAS structural strategies, daily, in my work with some of the largest companies in the world, as well as in my labs. The tools for developing powerful business intelligence and analysis capabilities have never been better. Bookmark this site as a place to check for a growing library of OLAP performance content in the months to come!

 

Copyright 2004 by the author.