MDX in Analysis Services: Intermediate Concepts - Part 2 - Page 4
May 27, 2003
Dealing with Empty Members
We notice a "fly in the ointment," however, in Illustration 7: The top level Expense % value is nonsensical. Our expression does not yet provide for cases where the Parent (distance = 1 from the current member) is non-existent; the top level cannot refer to a higher level, thus our expression generates a zero in its denominator. And, as we all know, the result of dividing by zero is "undefined."
We will handle this annoying situation with another enhancement to our expression. We will add a test that will help us to identify top-level members, and to ensure that they are treated differently, to accommodate the fact that, when it comes to Parents, they are "empty." While there are numerous ways to handle this, we will keep it simple, and take the following steps:
25. Right-click the Expense % calculated member, once again.
26. Select Edit from the appearing context menu.
The Calculated Member Builder reappears.
27. In the Value Expression box, input the following expression:
IIF([Store].Currentmember.Level.Ordinal =0,1,[Amount]/([Amount], Ancestor([Store].CurrentMember,1)))
28. Click OK to apply the changes.
The result set that is retrieved should appear as shown below.
(Remember to take advantage of the Check button to make sure that the correct number of parentheses, etc., is placed.)
Our modified expression is saying that, "if the ordinal for the current member under consideration is zero, substitute a "1," rather than performing the calculation we have built up to this point (the calculation is the "object" of the "else" clause that appears after the "then" value "1" above). The purpose of the conditional test is to determine if the ordinal (a measure of depth from the root level) of the level of the current member equals zero -whether the current member is at the top (or "All") level, and, thus, parentless.
We see from the above that we have achieved success in tailoring our expression to accommodate empty members, in our case those members from which a zero result is generated when our expression is applied to "parentless" members. The zero result would have caused mischief in our expression, as it would result in a zero denominator - something that most of us have learned to be "bad juju" in many scenarios, particularly in the present example, where information consumers are not likely to be amused with unintelligible metrics.
30. Select File -> Save or -> Save As... from the top menu, as desired.
31. Select File -> Exit to close the Cube Editor, handling prospective dialogs as appropriate.
Next in Our Series ...
In this tutorial, we expanded further the intermediate topics we introduced in Tutorial Two of the series. We took on practice examples where we delved into handling hierarchical relationships in our expressions. We also discussed one of multiple ways to identify empty members, illustrating why this is important in building expressions.
In our next lesson, Retrieving Values from Multiple Cubes, we will examine how we can use MDX within Analysis Services to retrieve values from multiple cubes simultaneously, offering us the often useful option of accessing multiple OLAP data sources together for analysis and reporting. We will discuss a real-world scenario in which a need for this capability commonly occurs: We will demonstrate how we can compute a per unit average, within the context of providing a Revenue per Unit Sold value based upon values retrieved from two separate OLAP data sources.