Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 24, 2003

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

By William Pearson

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:

{[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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM