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.
Illustration 8: The Result Set after Addition of the Conditional Test
(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.
»
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute
