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

Sales Amount

Q3 1996 Beverages $8,996.98

Q3 1996 Condiments $4,003.30

Q4 1996 Beverages $32,937.70

Q4 1996 Condiments $10,778.16

··· ··· ···

We can see that the members of the Category hierarchy now are only the
beverages and condiments categories and not the other categories which are
present in the original Northwind cube. Thus, the structure of the cube itself
has been altered.
The subquery may include more than one dimension, as the following
example shows:

SELECT Measures.[Sales Amount] ON COLUMNS,
[Order Date].Calendar.Quarter.MEMBERS *
Product.Category.MEMBERS ON ROWS
FROM ( SELECT ( { Product.Category.Beverages,

Product.Category.Condiments } ,

{ [Order Date].Calendar.[Q1 1997],

[Order Date].Calendar.[Q2 1997] } )ONCOLUMNS

FROM Sales )

whoseanswerisasfollows:

Sales Amount

Q1 1997 Beverages $33,902.08

Q1 1997 Condiments $9,912.22

Q2 1997 Beverages $21,485.53

Q2 1997 Condiments $10,875.70

We can also nest several subquery expressions, which are used to express
complex multistep filtering operations, as it is done in the following query,
which asks for the sales amount by quarter for the top two selling countries
for the beverages and condiments product categories:

SELECT Measures.[Sales Amount] ON COLUMNS,
[Order Date].Calendar.[Quarter].Members ON ROWS
FROM ( SELECT TOPCOUNT(Customer.Country.MEMBERS, 2,
Measures.[Sales Amount]) ON COLUMNS
FROM ( SELECT { Product.Category.Beverages,
Product.Category.Condiments } ON COLUMNS
FROM Sales ) )

This query uses the TOPCOUNT function, which sorts a set in descending
order with respect to the expression given as third parameter and returns the
specified number of elements with the highest values. Notice that although we
could have used a single nesting, the expression above is easier to understand.