dcsimg

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

June 24, 2003

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers