MDX in Analysis Services: Retrieve Data from Multiple Cubes - Page 2

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.

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:

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.

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.

We notice that the Budget cube contains data for USA Stores only.