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

Here, the TOPCOUNT function obtains the tuple corresponding to the
maximum sales amount. Then, the ITEM function retrieves the first member
from the specified tuple, and finally, the NAME function obtains the name of
this member. The result of the query is given below:

Maximum Sales Maximum Period

Beverages $21,817.76 January 1997

Condiments $5,629.70 December 1997

Confections $11,538.61 April 1997

Dairy Products $12,992.48 November 1997

Grains/Cereals $6,012.65 June 1997

Meat/Poultry $14,110.16 October 1997

Produce $12,157.90 December 1997

Seafood $8,448.86 September 1997

The previous query can be further elaborated to obtain the maximum sales
by category and by country, as well as the month in which they occurred.
This can be written as follows:

WITH MEMBER Measures.[Maximum Sales] AS
MAX(DESCENDANTS([Order Date].Calendar.Year.[1997],
[Order Date].Calendar.[Month]), Measures.[Sales Amount])
MEMBER Measures.[Maximum Period] AS
TOPCOUNT(DESCENDANTS([Order Date].Calendar.Year.[1997],
[Order Date].Calendar.[Month]), 1,
Measures.[Sales Amount]).ITEM(0).NAME

SELECT ON COLUMNS,

Product.Categories.Category.MEMBERS *
Customer.Geography.Country.MEMBERS ON ROWS
FROM Sales

{ [Maximum Sales], [Maximum Period] }

The result of the query is given next.

Maximum Sales Maximum Period

Beverages Austria $2,149.40 December 1997

Beverages Belgium $514.08 March 1997

Beverages Denmark $10,540.00 January 1997

Beverages Finland $288.00 February 1997

Beverages France $915.75 December 1997

Beverages Germany $8,010.00 May 1997

··· ··· ··· ···

The COUNT function counts the number of tuples in a set. This
function has an optional parameter, with values INCLUDEEMPTY or
EXCLUDEEMPTY , which states whether to include or exclude empty cells.
For example, the COUNT function can be used to compute the number of