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

The TopCount() Function

According to the Analysis Services Books Online, the TopCount() function returns a specified number of items from the topmost members of a specified set, optionally ordering the set first." The TopCount() function stands out as an excellent general example of the potential power of MDX. We specify three parameters, a set, a count, and a measure, in most cases, and TopCount() returns the number of top performers (or "top anything," in effect), based upon our input.

As we shall see, TopCount() sorts the set we specify by another expression we provide within the function, thus breaking the natural hierarchy of the set. The basis of sorting by TopCount() closely resembles that used by the Order() function. We should therefore keep in mind that, in the absence of a specified sort, the function will institute a sort based upon the default member.

NOTE: For information surrounding the Order() function, see my article Basic Set Functions: The Order() Function, a member of the MDX Essentials series at Database Journal.

We will examine the syntax for the TopCount() function, and then look at its behavior based upon parameter input we might provide. Next, we will undertake practice examples constructed to support hypothetical business needs that illustrate uses for the function. This will allow us to activate what we explore in the Discussion and Syntax sections, by getting some hands-on exposure in creating expressions that leverage the function. Our next article will build upon the basics we expose here, and leverage TopCount() within more sophisticated examples of business usage.

Discussion

To restate our initial explanation of its operation, the TopCount() function sorts a set based upon a numerical expression we provide, and then picks the top (whatever number we provide in the Count parameter of the function) items in that set, based upon the rank order established by the function. The frequent requirement to examine a subset of members at the top of the general set is easily answered by the TopCount() "sort, and then pick this number of members from the top down" approach.

Let's look at some syntax illustrations to further clarify the operation of TopCount().

Syntax

Syntactically, the set upon which we seek to perform the TopCount() operation is specified within the parentheses to the right of TopCount, a common arrangement within MDX functions, as we have seen in our previous articles. The syntax is shown in the following string.

TopCount(≪≪ Set >>, ≪≪ Count >> [,≪≪ Numeric Expression >>])

We follow <<Set>>, the set specification, with a comma, which is followed by <<Count>>, the numeric expression we provide to dictate the number of "top" members we wish for the function to return. <<Count>> is, in turn, followed by a numeric expression whereby we specify the "sort by" criteria. As we have mentioned, TopCount() always breaks the natural hierarchy of the set - due to the fact that the set is sorted upon the third argument we provide as <<Numeric Expression>>, before returning the top (specified number of) members from that sort.

The items returned by TopCount() are limited to the << Count >> input that we provide, even in the case of "ties" in the <<Numeric Expression>> value. This may be important to consider when evaluating the data retrieved by the function, in addition to default behavior of the function when no sort criteria (in the form, again, of the <<Numeric Expression>>) is provided.

The following example expression illustrates the use of the TopCount() function, within a simple business context. Let's say that a group of information consumers within the FoodMart Planning organization wishes to see the top ten Warehouse Cities for operating year 1997, based upon total Warehouse Profit for each city.

The basic TopCount() function specifies the "top ten Warehouse Cities" (with the number "10" as the Count specification, and [Warehouse].[City].Members as the Set specification, of the function). TopCount() assembles the top ten members from the perspective of Warehouse Profit (the Numeric Expression upon which the complete set of Warehouse Cities will first be sorted by the function). The function with arguments is represented in the ON ROWS line of the following query:

SELECT
   {[Measures].[Warehouse Profit]} ON COLUMNS,
   {TOPCOUNT([Warehouse].[City].Members, 10, 
       [Measures].[Warehouse Profit])} ON ROWS
FROM
    [WAREHOUSE]
WHERE
     ([Time].[Year].[1997])

Assuming that we placed the TopCount() function within the query as constructed above, our returned dataset would resemble that shown in Table 1.

As we expected, the top ten Warehouse Cities are returned, having been sorted by Warehouse Profit, and having had the top ten values in that sort isolated as we requested in the function.

As is the case with many MDX functions, TopCount() can be used to deliver sophisticated analysis datasets, particularly when we employ it along with other MDX functions. We will practice the use of TopCount() in the section that follows, building from a simple example, similar to that which we saw above, to a scenario where we apply the function, in conjunction with a calculated member, to reveal an additional analytical perspective. In our next article, we will examine even more sophisticated applications of the function, combining its use with that of other functions we have explored in articles of the series.