Practice
To reinforce our understanding of the basics we have covered
so far, we will first use the TopCount() function in a simple scenario
to illustrate its operation as the primary focus. We will do so in a backdrop
that places TopCount() within the context of meeting a business need for
a group of hypothetical information consumers.
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 Warehouse
cube in the Cube drop-down list box.
Let's assume, for our practice example, that we have
received a call from the Finance department of the FoodMart organization,
requesting some information surrounding general profitability among the
organization's Warehouse Cities. The Finance information consumers
specifically wish to know the Warehouse Profit figures attributed to
each of the top ten performing cities for operating year 1998.
To rephrase, the objective will be to present a single
measure, Warehouse Profit, for 1998 within the context of the
FoodMart Warehouse cube. (For our exercise, the cube can be assumed to
represent the prior year's activity of the organization.) We wish to sort the Warehouse
Cities by the total Warehouse Profit of each, and then to return
data for the highest ten Warehouse Cities based upon the profit measure.
Let's construct a simple query, therefore, to return the top
ten performers, as requested.
5.
Type the
following query into the Query pane:
-- MDX031-1,Simple use of TopCount(): "Top Ten 1998 Profit Producers"
SELECT
{[Measures].[Warehouse Profit]} ON COLUMNS,
{ TOPCOUNT([Warehouse].[City].Members, 10,
([Measures].[Warehouse Profit]))} ON ROWS
FROM
[WAREHOUSE]
WHERE
([Time].[Year].[1998])
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 - Simple Use of the
TopCount() Function
In a manner similar to that in the Syntax
section illustration above, the top ten Warehouse Cities are returned
(this time specifically for operating year 1998). The operation of TopCount()
has again been to sort by Warehouse Profit, and then to return the top
ten values from that sort, as we requested in the function.
7.
Select File
-> Save As, name the file MDX031-1, and place it in a
meaningful location.
8.
Leave the
query open for the next section.
Next, let's say that the Finance information consumers are
provided with the simple "top ten" data we have generated. They state
that they need to add the Warehouse Sales and a Margin % to the
presentation, keeping the TopCount() based upon the Warehouse Profit
measure, as before. This, they feel, will "round out" the
presentation to provide more utility from an analysis perspective, presenting "more
information at a single glance."
9.
Within the
query we have saved as MDX031-1, replace the top comment line of the
query with the following:
-- MDX031-2, "Top Ten 1998 Producers": Add Sales and Margin Calc Member
10.
Save the query
as MDX031-2, to keep MDX031-1 intact as a working sample.
11.
Add the
following lines to the query, between the top comment line we just modified,
and the SELECT keyword that begins the query:
WITH
MEMBER
[Measures].[Margin %]
AS
' [Measures].[Warehouse Profit]/[Measures].[Warehouse Sales]'
This
will create the calculated member Margin %, which we will present
alongside the Warehouse Sales and Warehouse Profit measures in
the following steps.
12.
Modify the ON
COLUMNS line of the query to contain the Warehouse Sales measure,
together with the new Margin % calculated measure defined above, as
follows:
{[Measures].[Warehouse Sales], [Measures].[Warehouse Profit],
[Measures].[Margin %]} ON COLUMNS,
13.
Leave the
remainder of the query in its original state.
The
Query pane
appears as depicted in Illustration 2, with our modifications marked.
Illustration 2: The Query with Added Measure and Calculated
Measure
14.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated, and the dataset shown in Illustration 3 appears.
Illustration 3: Result Dataset - With Our Modifications
We note that, although the order of the "top ten"
has not changed (the TopCount() function continues, after all, to be
based upon the Warehouse Profit measure), the Margin % calculated
measure is not sorted. (We will focus on this calculated measure as an "additional
perspective" next.)
15.
Re-save the
file as MDX031-2.
» 
Comment and Contribute
