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.
» 
Comment and Contribute
