Optimizing Query Performance While Using NON EMPTY
Considerations and Comments
Many of us enthusiastically use the NON EMPTY keyword when initially faced with sparse datasets such as I have described. However, we have perhaps been “underwhelmed,” as Oscar Wilde would have said, with performance in many instances where we have used the popular keyword within a scenario involving calculated members. In this article, we will describe the degradation that accompanies the use of NON EMPTY in such instances within our MDX queries, together with the underlying causes of those effects. We will then offer an approach to minimizing the impact of the causes, and to optimize prospective uses of NON EMPTY as part of the rich collection of capabilities that make MDX, in conjunction with MSAS, a power tool without equal in providing business intelligence to the clients and other organizations we serve.
At the heart of the degradation in processing is the fact that a query can be slowed markedly when we apply NON EMPTY to an axis within an MDX query that contains a calculated member. When we use NON EMPTY in this context, the calculated member evaluates its expression for every member to ascertain emptiness or non-emptiness of each. This assessment of each member can, depending upon the number of members, take considerable time; it is this check of the member population, member by member, that can add punishing overhead and increase query processing time.
Hands-On Procedure
Having become aware of the cause of the degradation we have described in the NON EMPTY / calculated member combination, we can easily take advantage of a calculated member property setting that is in place for the specific purpose of helping us to minimize degradation of this kind. The Non Empty Behavior property, in effect, permits us to direct prospective queries to bypass the expensive member evaluation process we have described above, providing the given calculated member with an “override” of sorts that directs a “foregone conclusion” for the empty / non-empty status, instead of undertaking the costly evaluation process. We will see how to set the property, as well as obtain an understanding of how it works, in the following illustrative steps.
1. Open Analysis Manager.
2. Expand the Analysis Servers folder in the management console.
3. Expand the Analysis Server with which you are working, by clicking the “+” sign to its left.
4. Expand FoodMart 2000 database.
The Analysis Manager tree view appears as shown in Figure 3.
Figure 3: Expanded Components in the Analysis Manager Tree View
5. Expand the Cubes folder within the FoodMart 2000 database.
6. Right-click the HR sample cube that appears among the other cubes in the folder.
7. Select Edit from the context menu that appears, as depicted in Figure 4.
Figure 4: Select Edit from the Context Menu
The Cube Editor opens.
8. Select the Employee Salary calculated member, shown circled in Figure 5.
Figure 5: Select the Employee Salary Calculated Member
9. Ensure that the Properties pane – Basic tab is open to view.
10. Click the Value property on the Advanced tab to highlight / select it.
An ellipses (“…”) button appears to the far right of the Value property, as shown in Figure 6.
Figure 6: Properties Pane – Basic Tab, Value Property Setting
11. Click the ellipses button.