MDX in Analysis Services: Retrieve Data from Multiple Cubes

Tuesday Jun 24th 2003 by William Pearson
Share:

Join Bill Pearson as he examines how we can use MDX within Analysis Services to retrieve values from multiple cubes simultaneously.

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved