MDX Essentials: Basic Set Functions: The TopCount() Function, Part I - Page 3

May 2, 2005


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"
  {[Measures].[Warehouse Profit]}  ON COLUMNS,
{ TOPCOUNT([Warehouse].[City].Members, 10, 
      ([Measures].[Warehouse Profit]))}  ON ROWS

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:

    [Measures].[Margin %]
   ' [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.

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers