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

Beverages Condiments Confections ···

Austria Q3 1996 All $708.80 $884.00 $625.50 ···

Austria Q3 1996 Federal Shipping $100.80 $625.50 ···

Austria Q3 1996 Speedy Express $608.00 $884.00 ···

Austria Q3 1996 United Package ···

Austria Q4 1996 All $12,955.60 $703.60 $36.00 ···

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

6.1.6 Subqueries

As stated above, the WHERE clause applies a slice to the cube. In the queries
so far, we have used this clause to select the measure to be displayed. But
this can also be used for dimensions. If we were only interested in the sales
amount for the beverages and condiments product categories, we could write
the following query:

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

Instead of using a slicer in the WHERE clause of above query, we can define
a subquery in the FROM clause as follows:

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

Product.Category.Condiments } ON COLUMNS

FROM Sales )

This query displays the sales amount for each quarter in a subquery which
only mentions the beverages and condiments product categories. As we can
see in the query above, different from SQL, in the outer query we can mention
attributes that are not selected in the subquery.
Nevertheless, there is a fundamental difference between these two
approaches. When we include the product category hierarchy in the WHERE
clause, it cannot appear on any axis, but this is not the case in the subquery
approach as the following query 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 } ON COLUMNS
FROM Sales )

The answer of this query is given next.