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.
prepare for the lesson, let's open Analysis Manager, and create a new calculated
member within the Budget cube.
the Analysis Manager console, and at the Cubes folder within the FoodMart
2000 database sample:
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
Right-click the Budget
Click Edit from the
flyout context menu.
When the Cube Editor screen
appears, click the Data tab at its lower left (Data Viewing
The Data Viewing pane
is activated, retrieving the view that was last saved, or perhaps the default, if
none was saved from previous lessons.
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).
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.
Select 1997, if
necessary, in the filter field for the Year dimension atop 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:
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
the USA member of the Store Country
level (Rows axis) to explode to the USA Store State view of the Store
dimension for USA.
result set should be identical to that shown in Illustration 4.
Illustration 4: The Results in the Data Viewing Pane
notice that the Budget cube contains data for USA Stores