dcsimg

MDX Essentials: Basic Set Functions: The TopCount() Function, Part II - Page 2

June 6, 2005

Practice

Perform a Topcount() within the Context of Another Dimensional Level

Because we discussed syntax and other considerations that accompany the basic use of Topcount() in our previous article, we will move directly into the practice examples in this article. The examples are a bit more elaborate, and will require somewhat detailed explanations, as they involve the juxtaposition of TopCount() with other MDX functions. Consistent with our routine approach within the MDX Essentials series, we will use TopCount() within the context of meeting illustrative business needs for a group of hypothetical information consumers in the examples that follow.

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 been contacted by a group of information consumers within the FoodMart organization requesting some assistance with a new reporting requirement. The group, composed of analysts from the Finance and Accounting departments of the organization, wishes to be able to present 1998 Warehouse Profit for the three Warehouses with the highest Warehouse Profit in each of the States (the group is only concerned with U.S. operations from the perspective of this business need). We are told that the Warehouse cube, which houses the lion's share of the FoodMart Warehouse data, contains the information we need to meet the business requirement.

Working with the consumers, we draft a sketch of the proposed report layout, illustrated in Table 1, to confirm our understanding of the requirement.

State

Warehouse Profit

Warehouse 1

CA

(Highest Profit in CA)

Warehouse 2

CA

(Next Highest Profit in CA)

Warehouse 3

CA

(Third Highest Profit in CA)

Warehouse 1

OR

(Highest Profit in OR)

Warehouse 2

OR

(Next Highest Profit in OR)

Warehouse 3

OR

(Third Highest Profit in OR)

Warehouse 1

WA

(Highest Profit in WA)

Warehouse 2

WA

(Next Highest Profit in WA)

Warehouse 3

WA

(Third Highest Profit in WA)


Table 1: Results Dataset, with TopCount() Defining Columns

The challenge in the requirement we have described lies within the fact that we are working within multiple levels within the Warehouse dimension to produce the requested results. We are, in effect, being asked to classify the three top Warehouse performers by a higher level in the Warehouse dimension (State Province). In order to do this, we will employ the TopCount() function as we have done in previous scenarios, but this time in conjunction with other MDX functions, to present the State information associated with each of the top performing Warehouses.

Let's construct part of the query to get started. First, we will construct the part with which we are familiar, from our previous article at least, the core TopCount() function.

5.  Type the following query into the Query pane:


-- MDX032-1-1, TopCount() and Descendants() Combination to retrieve "3 Most 
--      Profitable Warehouses in 1998"
SELECT
     {[Measures].[Warehouse Profit]}  ON COLUMNS,
     {TOPCOUNT(
          DESCENDANTS(
               [Warehouse].CURRENTMEMBER, [Warehouse].[Warehouse Name]
          ),3,[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: Results Dataset - Simple Use of the TopCount() Function








The Network for Technology Professionals

Search:

About Internet.com

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