Now, let's
nest multiple dimensions into the row axis, to demonstrate a useful approach to
meeting business needs in this way, if required. We can do this with the CrossJoin()
function.
39.
Modify the
query to match the following:
SELECT
{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales],
[Measures].[Units Shipped]} ON COLUMNS,
[Store].[Store State].Members ON ROWS
FROM Warehouse
Here
we are simply juxtaposing our last statement, where we are specifying the
multiple member set for the column axis, with a statement using a set
function (.Members) to specify the row axis. Because we are
using a set function, note that we can leave off the braces.
40.
Click the Run
Query button.
The
results appear as shown in Illustration 9.
Illustration 9: The
Result Set, Specifying Both Axes
Now, let's say we need
to nest two sets in the row axis set. We will do this with the CrossJoin()
function we discussed above.
41.
Modify the
query to add the CrossJoin() function, just before the ON ROWS
instruction, as shown below (the CrossJoin() function appears in blue):
SELECT
{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales],
[Measures].[Units Shipped]} ON COLUMNS,
CrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS
FROM Warehouse
42.
Click the Run
Query button to view the effect of the changes (scroll to the USA
Store State rows).
The
results appear as partially depicted in Illustration 10.
Illustration 10: The
Result Set, With a Little Help from the CrossJoin Function
The CrossJoin()
function has enabled us to juxtapose two sets, from two dimensions, as
if it were one combined set, on the row axis. This produces the nesting
of the Product Family level members into the Store State levels
that we see above, and gives us a practical preview of the power of the CrossJoin()
function.
Now,
let's go one step further for the sake of finesse, and remove the many empty
spaces in our new report. This is a simple matter of replacing the CrossJoin
function with a derivative function, the NonEmptyCrossJoin() function,
as shown below (I have highlighted the only change over the last query in
blue):
SELECT
{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales],
[Measures].[Units Shipped]} ON COLUMNS,
NonEmptyCrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS
FROM Warehouse
43.
Modify the
query to reflect the change in the function name noted above.
44.
Click the Run
Query button.
The results
appear as depicted in Illustration 11.
45.
Save the query as MXAS06-4.MDX,
and exit the Sample Application as desired.
We now have a report,
albeit simple in structure that hints at the potential available to us within
MDX for multidimensional analysis. We have used the CrossJoin()
function to create a report that allows us to simultaneously analyze multiple
dimensions, as well as to nest two of these dimensions on one axis (in our case
the Row axis) as part of the objective.
Next in Our Series ...
In
this lesson, we began
an exploration of MDX queries. We discussed the differences between MDX
expressions and MDX query statements, and as part of
this overview, discussed key MDX concepts and terminology. We followed
our overview with an exploration of MDX query building from the ground
up, using the MDX Sample Application as a vehicle for crafting our statements
and practicing their use.
We
delved into set functions, and the creation and use of sets,
discussing their importance in our MDX queries. We progressively built the
specification of members, and the combination of multiple dimensions, into our
row and column axes, to add truly multidimensional capabilities to the reports
we produce for ourselves and for the information consumers we support.
Our
next topic will span a three-article set. In these three lessons, we will
focus on building and using calculated members within our queries to meet
various business needs. We will begin by previewing the creation of dynamic
calculated members to set the stage for the functionality and processes we will
explore together.
Throughout the Building Calculated Members articles,
we will explore and practice the creation of increasingly sophisticated calculated
members, discussing various types and their uses. We will further transit into
the realm of using functions within our calculated members, practicing different
uses for the components we have explored. Finally, we will preview member
functions, in preparation for lessons to follow Building Calculated
Members.
»
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute
