About the Series ...
This is the fourth 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 expanded further the intermediate topics we introduced in Tutorial
Three of the
series. We undertook practice examples where we explored 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 this
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 an example 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.
Accessing Multiple
Cubes Simultaneously
In this lesson, we will extend our
evolving use of calculated members to add the retrieval of data from another
cube, by creating an expression to "look up" a value from the
secondary data source. While truly sophisticated uses of this capability are
possible, we will undertake a simple instance to illustrate that we can rely on
data from other sources to enhance the end product that we deliver to the
targeted information consumers. (Indeed, optimal cube design principles
dictate that we do not replicate data that we can easily entrain from other
sources in creating new ones).
To set a scenario, let's say that we
want to bring Sales Units data into our information product. We know
that our primary Sales data source for purposes of this lesson, the Budget
cube, does not contain this information, not only because its designers would
have wanted to restrict its size, but also to limit its focus to the business requirements
of the Budgeting consumers, who we will assume did not express an interest in
unit quantity information as a part of the business requirements collection
phase of design. A quick review of the measures data in the Budget
cube confirms that, indeed, unit information does not exist in the cube.
Over time, let's say an ad-hoc need
arises to be able to access Sales Units data that corresponds to the
respective sales information that we store in the Budget cube. We will
say, for this example that we need to compare some rough revenue per-unit
calculations between stores. This will also give us an opportunity to expand
our integrated practice exercise to include the calculation of some high-level
per-unit average costs.
We add the foregoing considerations to
our list of requirements, and set out to design our model to meet the newly
expressed needs. We will use these example business
requirements within an integrated practice example to flesh out a solution that
incorporates the objectives of our lesson.
Extending Our MDX Expressions:
Retrieving Data from a Second Cube
In this preliminary
exercise, we will examine how we can use MDX to retrieve a value from a
second cube, offering us the capability of using multiple OLAP data
sources together for analysis and reporting. In addition, 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. We will perform exercises to reinforce these
concepts within the context of our OLAP data sources.
To
prepare for the lesson, let's open Analysis Manager, and create a new calculated
member within the Budget cube.
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 below), by clicking the "+"
sign to its left.
Illustration 1: Sample Cubes provided with the
Analysis Services Installation
2.
Right-click the Budget
cube.
3.
Click Edit from the
flyout context menu.
4.
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.
5.
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 level of the Store dimension above All Stores
(the top level), in the current dimension's old place).
6.
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.
7.
Select 1997, if
necessary, in the filter field for the Year dimension atop the Data
Viewing pane.
The Data
Viewing pane now appears as shown below:
Illustration 2: The Data
Viewing Pane after our Dimension "Swap" (Compressed View)
Our first focus will be to retrieve the
Sales Units data from the Sales cube (another sample cube that
comes along with the Analysis Services installation). To create a calculated
member to accomplish this, we will use a "lookup"
function. We will take the following steps to proceed:
8.
Click the filter field
for the Account dimension, and, from the dropdown hierarchy that
appears, click the "+" signs continually to expand to, and select,
Gross Sales, as shown in Illustration 3.
Illustration 3: Select
Gross Sales as the Filter
9.
Double-click
the USA member of the Store Country
level (Rows axis) to explode to the USA Store State view of the Store
dimension for USA.
The
result set should be identical to that shown in Illustration 4.
Illustration 4: The Results in the Data Viewing Pane
We
notice that the Budget cube contains data for USA Stores
only.
Now, let's
create a new calculated measure to act as our "pipeline" from
the Sales cube.
10. Select Insert -> Calculated Member from the top menu.
The Calculated
Member Builder appears.
11.
Type Sales
Units into the Member Name box. In the Value Expression
box, input the following expression:
LookupCube("Sales","([Unit Sales],"+[Store].CurrentMember.UniqueName +")")
The Calculated
Member Builder appears as shown in Illustration 5.
Illustration 5: The
Calculated Member Builder, with New Expression Entered
12.
Click OK
to accept the expression entered as above.
The Data
Viewing pane appears as shown below.
Illustration 6: The
Sales Units Data as Retrieved from the Sales Cube
The simple expression
we created above exploits the LookupCube function; within the function,
we specify two things: a cube string, specifying the name of the cube
targeted as the source (Sales); and a string expression,
whereby we specify the tuple whose value we seek to return. We enforced
the criteria for specification of dimensions by using the now familiar .CurrentMember
function, appending the unique name function (which returns a string
for containing the "qualified name," which is based upon the entire
hierarchy "path" for the member), after first closing the string,
then appending (via the second "+") the remainder of the string.
A quick comparison of
the result set to the data in the Sales cube verifies its accuracy. We
can perform such verification quickly, without leaving our current position in
the Cube Editor, by taking the following steps:
13.
Leaving the Cube Editor in its
current state, select click the Start button.
14.
Open the MDX Sample
Application (installed with the typical MSSQL Server 2000 Analysis
Services installation, and located, by default, within the MSSQL Server
--> Analysis Services program group in the Start menu.)
The Connect
dialog appears, as shown in Illustration 7, with my defaults.
Illustration 7:
The Connect Dialog for the MDX Sample Application Appears
15.
Input the appropriate Server
and Provider information into Connect dialog, or accept the
defaults that appear.
16.
Click OK.
The Connect dialog
closes, and the MDX Sample Application window opens.
17.
Ensure that the FoodMart
2000 database is selected in the DB selector atop the MDX Sample
Application window.
18.
Ensure that the Sales
cube is selected in the Cube selector midway down the window.
19.
Clear the Query pane in the top
third of the MDX Sample Application of any residual expression(s) that
might remain.
20.
Type the following into the
query pane:
SELECT
{[Measures].[Unit Sales]} ON COLUMNS,
{[Store].[Store Country].[USA].Children} ON ROWS
FROM Sales
Our intent here will be to do a quick
verification of the USA Stores by State, as there is no Sales Unit
data for the other countries in the Sales cube anyway.
21.
Select Query -->
Run from the top menu.
The result dataset is
returned as shown in Illustration 8.
Illustration 8:
The Result Dataset of Our Verification in the Sample Application
We see that the totals
for Sales Units by Store States agree to those we see displayed,
courtesy of the LookupCube function, in the Cube Editor.
Let's return to the
Cube Editor for further design work, leaving the Sample Application open for
further verification exercises later.
Creating a Calculated Measure
Using Data from Two Different OLAP Cubes
Next, let's meet
another business need, and create another expression to compute the average
total revenue (or "gross sales") per unit sold as a "quick
and dirty" metric to use in perhaps identifying significant outliers
(realizing, of course, that this would only be a rough indicator, that would
perhaps serve to identify further, more precisely designed analysis
opportunities).
22. Select Insert (top menu).
Select calculated member on the drop-down menu.
The Calculated
Member Builder appears.
23.
Type Avg
Rev Per Unit Sold into the Member Name box. In the Value Expression
box, type the following expression:
[Amount]/[Sales Units]
24.
Click OK
to accept the expression entered.
The Data
Viewing pane appears as shown below.
Illustration 9: The
Sales Units Data with the Avg Revenue per Unit Sold Calculated Member
NOTE: If, at this juncture or at any
point within the lesson, the column order of the results obtained differs from
that shown in the illustrations, simply drag the calculated member field(s) to
the correct position in the cube tree
We obtain
the desired results for Avg Revenue per Unit Sold for the USA Stores,
The Canadian and Mexican stores contain no data at the levels under
consideration.
25.
Click OK
to apply the new expression.
For the purpose of general "beautification," let's
make a couple of format adjustments.
26.
Ensure the Avg Revenue per Unit Sold calculated member is selected in the Cube tree.
27.
Select the Basic
tab of the Properties pane, which appears underneath the Cube tree,
if not already selected.
28.
Select the Parent
Dimension property of Avg Rev Per Unit Sold, on the Basic
tab.
29.
Ensure that
the Measures dimension is selected (via the drop-down arrow).
30.
Click the Advanced
tab in the Properties pane.
31.
Select the Format
String property of Avg Rev Per Unit Sold.
32.
Click the
drop-down arrow.
33.
Select Currency
as the format for the string.
The Advanced
tab in the Properties pane appears as shown in Illustration 10.
Illustration 10: The
Format String Setting in the Advanced Properties Pane
34.
Press Enter.
35.
Select Sales
Units in the Calculated Members folder for the Budget
cube.
36.
Select the Basic
tab in the Properties pane.
37.
Select the Parent
Dimension property of Sales Units, on the Basic tab.
38.
Ensure that
the Measures dimension is selected (via the drop-down arrow).
39.
Click the Advanced
tab in the Properties pane.
40.
Select the Format
String property of Sales Units.
41.
Click the
drop-down arrow.
42.
Select #,#
(whole units with thousands separator - no decimal placed) as
the format for the string.
43.
Press Enter.
The Calculated
Member Builder closes, and the new dataset returned appears as shown in Illustration
11.
Illustration 11: The
Dataset with New Formatting Refinements
We see both the Sales
Units and the Avg Rev Per Unit Sold calculated members appear,
complete with our formatting specifications.
Next
in Our Series ...
In this
tutorial, we exposed the use of the MDX LookupCube
function within Analysis Services, as a means of retrieving values from
multiple cubes simultaneously. This offers us the often useful option of
accessing multiple OLAP data sources together for analysis and
reporting, an example real-world scenario of which we presented in the lesson. In
addition, we demonstrated how we could compute a per-unit average,
within the context of providing a Revenue per Unit Sold measure, based
upon values retrieved from two separate OLAP data sources, further exploring
and practicing the use of calculated members as part of the process.
In our
next lesson, Measuring Change over Time, we will explore the use of MDX functions that
incorporate the concept of time, within the context of expression design. We
will practice incorporating support for time-based analysis, such as the
quantification of change in values over time, with MDX functions that are
suited for that purpose. We will perform exercises to reinforce these concepts
within the context of our OLAP data sources. Finally, within the perspective of the subject
matter, we will practice further the use of calculated members, including the
creation of a variance calculation.
»
See All Articles by Columnist William E. Pearson, III