About the Series ...
This is the third tutorial article of the
series, MDX in Analysis Services. The series is designed to
provide hands-on application of the fundamentals of MDX from the perspective of
MS SQL Server 2000 Analysis Services (to which I will refer in most
cases as simply "Analysis Services," to
save time and space). Our primary focus is the manipulation of multidimensional
data sources, using MDX expressions in a variety of scenarios designed to meet
real-world business intelligence needs.
For more information on the series, as well as the hardware /
software requirements to prepare for the tutorials we will undertake, please
see Tutorial 1: MDX Concepts and Navigation.
Note: At the time of writing, Service
Pack 3 updates
are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis
Services, and the related Books Online and Samples.
Introduction
In our last tutorial,
we took MDX beyond the retrieval of member names and properties, and
began to focus on leveraging the capabilities of the language to return values
from a multidimensional cube. We created calculated measures whose
values were based upon a constant, then upon current members, and
explored additional uses of calculated members and measures. We
practiced returning values, based upon the specification of dimensions within
MDX expressions, to extend the expressions' utility within the context of
reporting from an OLAP data source. Moreover, we examined various aspects of
the MDX notation system along the way.
In this tutorial, we
will expand further the intermediate topics we introduced in our last lesson.
We will take on practice examples where we will delve into handling hierarchical
relationships in our expressions. We will also discuss a way to identify empty
members, and illustrate why this is often important in building
expressions.
Leveraging OLAP
Hierarchies to Return Values
In the practice exercises in our last
session, we created a calculated member and performed retrieval of
values from the sample Budget cube. We made use of the current
member concept and discovered how we could perform a calculation for every
cell in the calculated member column, based upon the current member
of the corresponding row axis. We then practiced specifying dimensions in our
expressions to achieve desired results.
In this lesson, we will extend our
evolving use of calculated members to determine members' contribution to greater
wholes. This determination is quite desirable and common in financial and
other reporting. A good example might be the percentage of total
organizational expense that belongs to each individual store, to support
analysis of store performance relative to peer stores, or for other possible
purposes. We will also perform the calculation of each store's share of the
subtotals at the various levels of the Store dimension hierarchy
(City, State, and Country).
Our intent will be to demonstrate
further the power of OLAP cubes to embrace and exploit the hierarchical
relationships between dimension members. As a part of our efforts, we will
practice using tests to identify empty members, illustrating both why
this might be necessary, and how we can build in logic to deal with
complications that these members might present.
To
prepare for the lesson, let's open Analysis Manager (see previous
tutorials if you need a refresher on Analysis Manager, or if you are joining
the current series with this article), and create a new calculated member
within the Budget cube. This will provide a "fresh start," and
allow us a review of a few steps we accomplished before, within a standalone
tutorial.
From
the Analysis Manager console, and at the Cubes folder within the FoodMart
2000 database sample:
1.
Expand the Cubes
folder (seen in the Illustration 1), by clicking the "+"
sign to its left.
Illustration 1: Sample
Cubes provided with the Analysis Services Installation
2.
Right click the Budget
cube, and then click Edit from the flyout shortcut menu.
3.
When the Cube Editor screen
appears, click the Data tab at its lower left (Data Viewing
pane).
The Data Viewing pane
is activated, retrieving the view that was last saved, or perhaps the default,
if none was saved from previous lessons.
4.
Drag the Store
dimension from the top pane down to the current dimension in the Rows
axis (you can drop the icon that appears over the heading currently in place -
a small, double-headed arrow appears at the "drop" point), to "swap"
the current dimension to the top and Store below. (It will appear as Store
Country, the label that appears physically above All Stores
(technically the top level), in the current dimension's old
place).
5.
Drag the Measures dimension from the top pane down
to replace the dimension in the Columns axis, unless it is already
there. The heading will appear as MeasuresLevel, with the measure Amount
appearing just underneath.
6.
Select 1998, in the
filter field for the Time dimension atop the Data Viewing pane.
The contents
of the Data Viewing pane now appear as shown below:
Illustration 2: The Data
Viewing Pane Contents after our Modifications (Compressed View)
7.
Select Insert
(top menu).
8.
Select Calculated
Member on the drop-down menu, as shown in Illustration 3 below.
Illustration 3:
Selecting Insert -> Calculated Member
The Calculated
Member Builder appears.
9.
Type Expense
% into the Member Name box. In the Value Expression
box, input the following expression:
[Amount]/([Amount], Ancestor([Store].CurrentMember, #91;Store].[(All)]))
The Calculated
Member Builder dialog should now appear as partially shown below.
Illustration 4: The Calculated
Member Builder Dialog (Partial View)
10.
Click the Check
button to ascertain proper MDX syntax.
The Syntax is OK message box appears, indicating that
the expression entered at least does not violate any general syntax rules.
While the "check" process does not validate the propriety or accuracy
of the expression beyond the context of syntax, it still serves as a great way
to detect missing or unpaired parentheses, and other typical keystroke errors.
11.
Click OK.
The
message box closes, placing us back at the Calculated Member Builder
dialog.
12.
Click OK
again.
The Calculated
Member Builder dialog closes.
13.
In the Cube
tree, select Expense % in the Calculated Members
folder for the Budget cube, if it is not already selected.
The Basic
tab of the Properties pane appears underneath Expense %.
14.
Select the Parent
Dimension property of Expense %, on the Basic tab.
15.
Ensure that
the Measures dimension appears in the selector box for the Parent
Dimension property.
16.
Click the Advanced
tab in the Properties pane.
17.
Select the Format
String property of Expense %.
18.
Select Percent
as the format for the string, by insuring the word appears in the selector box
for the Format String property.
19.
Press Enter.
The
result set appears as shown in Illustration 5.
Illustration 5: The Modified Result Set from the Data Viewing
Pane
20.
Double-click
the Mexico member of the Store Country
level (Rows axis) to explode to the Mexico Store State view of Store dimension.
We have
now exploded to the second level for Mexico in the Store hierarchy, that of the individual Mexican Store State members. The results should
appear as shown below.
Illustration 6: Data View Drilled Down to the Mexican Store State Members
We note that, while the
Expense % for the individual Mexican Store States
does, indeed, equal 100% at the All Stores Total rollup (the top
dimension level), our calculated member does not extend the same treatment to
the hierarchical levels. That is, the Expense % for the Mexican
Store States does not indicate the relative contribution of the Store
States to their respective immediate rollup levels. (In other words,
the Mexican Store States' percentages do not total to 100% at
their hierarchical rollup level Mexico Total - they show their
respective contributions to the top level, All Stores,
only, in that they simply add to a total of 76.21%, the Mexico Total
contribution).
We will modify our
expression to use an extended version of the Ancestor function to 1)
identify the hierarchical Parent for the Store States and 2)
calculate context sensitive rollup percentages, based upon our position
in the drill-down of the hierarchy. The distance version of the Ancestor
function has the same meaning as the Parent function, when the "distance,"
or the number of levels removed from the original member (in our
case, the current member) to the target member (in our case, the Parent
member) is 1.
21.
Right-click the Expense %
calculated member.
22.
Select Edit from the
appearing Context Menu.
The Calculated Member Builder reappears.
23.
In the Value
Expression box, input the following expression:
[Amount]/([Amount], Ancestor([Store].CurrentMember,1))
24.
Click OK
to apply the changes.
[Amount]/([Amount], Ancestor([Store].CurrentMember,1))
The results of the
modified expression should appear as shown below.
Illustration 7: The New Result Set from the Data Viewing Pane
Now the
percentages for the Mexican Store States add to 100%. We
have achieved our objective, in that the group of the Store Country
level Expense % values, as well as the group of the Store State level Expense % values,
total 100% of their respective rollup levels (the level Totals).
This scenario demonstrates how MDX allows us to leverage the hierarchical
structure of the OLAP data source in our expressions.
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