Practice
Preparation
To reinforce our understanding of the basics we have covered
so far, we will use the Subset() function in a manner that illustrates
its operation. We will do so in a simple scenario that places Subset()
within the context of meeting a business need.
To begin, we will construct a SELECT query with a
clearly defined set, then put Subset() to use in limiting that set to
meet an illustrative need for a group of hypothetical information consumers.
The intent is, of course, to demonstrate the operation of the Subset()
function in a straightforward manner.
Let's return to the MDX Sample Application as a
platform from which to construct and execute the MDX we examine, and to view
the results datasets we obtain.
1.
Start the MDX
Sample Application.
2.
Clear the top
area (the Query pane) of any queries or remnants that might appear.
3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
4.
Select the Sales
cube in the Cube drop-down list box.
Let's assume, for our practice example, that we have
received a call from the Marketing department of the FoodMart organization,
requesting some information surrounding sales promotions that have been
conducted. The Marketing information consumers specifically wish to know the Unit
Sales figures attributed to each of the promotions, broken out by gender
of the purchasers, from which to derive a recurring report that is more
filtered.
To rephrase, the objective will be to present a single
measure, Units Sales, for "all time" within the context of the
FoodMart Sales cube. (For our exercise, the cube can be assumed to
represent the current year-plus activity of the organization.) We wish to
return data showing Unit Sales broken out by male and female purchasers,
for each of the promotions that we have conducted within the time frame
represented by the Sales cube. It is from the results dataset that is
returned that the consumers want to narrow their request, once they get a look
at overall figures, to a compact, recurring report.
Let's construct a simple query, therefore, to return the Unit
Sales information, presented by gender (as columns) and the promotion
name (as rows).
5.
Type the
following query into the Query pane:
-- MDX021-1, Preparation for Use of Subset() Function in a Basic Query
SELECT
{[Gender].Members} ON COLUMNS,
{[Promotions].[Promotion Name].Members} ON ROWS
FROM
[Sales]
WHERE ([Measures].[Unit Sales])
6.
Execute the
query by clicking the Run Query button in the toolbar.
The Results pane is populated by Analysis Services,
and the dataset shown in Illustration 1 appears.
Illustration 1: Result Dataset Preparation for Use of
Subset() Function
We see Male, Female, and All
Gender populating the columns across, and the Promotion Name (from
the Promotions dimension) appearing on the row axis.
7.
Select File
-> Save As, name the file MDX021-1,
and place it in a meaningful location.
8.
Leave the
query open for the next section.
Next, let's say that our information consumers are provided
with the somewhat raw Promotion-by-Gender metrics we have generated. They
state that they need the data in a slightly different presentation, before determining
the thresholds for the ultimate recurring report.
The department has recently decided to emphasize its focus
on the purchasing activities of female purchasers, while perusing the
corresponding activities of male purchasers, in an attempt to identify
patterns. More specifically, they want the same information that we have
provided, but sorted by Unit Sales values, from highest sales promotion
to lowest, from the perspective of female shoppers.
We can accomplish this re-sort using the Order()
function that we explored in Basic
Set Functions: The Order() Function, as we shall see in the
following steps.
9.
Within the
query we have saved as MDX021-1, replace the top comment line of the
query with the following:
-- MDX021-2, Preparation for Use of Subset() Function -Ordered Query
10.
Save the query
as MDX021-2, to prevent damaging MDX021-1.
11.
Change the
following line of the query (the rows axis definition):
{[Promotions].[Promotion Name].Members} ON ROWS
to the following
{ORDER([Promotions].[Promotion Name].Members, ([Gender].[All Gender].[F],
[Measures].[Unit Sales]), BDESC)} ON ROWS
12.
Remove the
following line (the slicer at the bottom) from the MDX query:
WHERE ([Measures].[Unit Sales])
The
Query pane
appears as shown in Illustration 2.
Illustration 2: The Query with Ordering Enhancement
» 
Comment and Contribute
