Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 199

6.1.12 Top and Bottom Analysis

When displaying information such as the best-selling cities based on sales
amount, a usual requirement is to limit the query to, say, the top three. The
HEAD function returns the first members in the set based on the number that
one requests. A similar function TAIL returns a subset from the end of the
set. The query “Top three best-selling store cities” is expressed as follows:

SELECT Measures.MEMBERS ON COLUMNS,
HEAD(ORDER(Customer.Geography.City.MEMBERS,
Measures.[Sales Amount], BDESC), 3) ON ROWS
FROM Sales

This query yields the following answer:

Unit Price Quantity Discount Sales Amount Freight Sales Count

Cunewalde $101.46 3,616 21.40% $103,597.43 $4,999.77 77

Boise $90.90 4,809 32.41% $102,253.85 $6,570.58 113

Graz $88.00 4,045 23.57% $93,349.45 $5,725.79 92

Alternatively, the function TOPCOUNT can be used to answer the previous
query:

SELECT Measures.MEMBERS ON COLUMNS,
TOPCOUNT(Customer.Geography.City.MEMBERS, 3,
Measures.[Sales Amount]) ON ROWS
FROM Sales

As a more elaborated example, suppose that we want to display the top
three cities based on sales amount together with their combined sales and
the combined sales of all the other cities. This can be written as follows:

WITH SET SetTop3Cities AS TOPCOUNT(
Customer.Geography.City.MEMBERS, 3, [Sales Amount])
MEMBER Customer.Geography.[Top 3 Cities] AS
AGGREGATE(SetTop3Cities)
MEMBER Customer.Geography.[Other Cities] AS
(Customer.[All]) - (Customer.[Top 3 Cities])
SELECT Measures.MEMBERS ON COLUMNS,
{ SetTop3Cities, [Top 3 Cities], [Other Cities], Customer.[All] } ON ROWS
FROM Sales

The query starts by selecting the three best-selling cities and denotes this set
SetTop3Cities . Then, it adds two members to the Geography hierarchy. The
first one, denoted Top 3 Cities , contains the aggregation of the measures of the
elements in the set SetTop3Cities . The other member, denoted Other Cities ,
contains the difference between the measures of the member Customer.[All]
and the measures of the member Top 3 Cities .The AGGREGATE function