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

customers that purchased a particular product category. This can be done
by counting the number of tuples obtained by joining the sales amount and
customer names. Excluding empty cells is necessary to restrict the count
to those customers for which there are sales in the corresponding product
category. This is shown below:

WITH MEMBER Measures.[Customer Count] AS
COUNT( Measures.[Sales Amount] *

[Customer].[Company Name].MEMBERS

{

} , EXCLUDEEMPTY)

SELECT { Measures.[Sales Amount], Measures.[Customer Count] } ON COLUMNS,

Product.Category.MEMBERS ON ROWS
FROM Sales

The result of the query is as follows:

Sales Amount Customer Count

Beverages $237,203.91 82

Condiments $91,528.81 65

Confections $162,443.91 79

Dairy Products $221,157.31 80

··· ··· ···

6.2 Querying the Northwind Cube in MDX

In this section, we further illustrate the MDX language by revisiting the
queries given in Sect. 4.4 addressed to the Northwind cube.

Query 6.1. Total sales amount per customer, year, and product category.
SELECT [Order Date].Year.CHILDREN ON COLUMNS,
NON EMPTY Customer.[Company Name].CHILDREN *
Product.[Category Name].CHILDREN ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]
Here, we display the years on the column axis and we use a cross join of
the Customer and Category dimensions to display both dimensions in the
row axis. We use the CHILDREN function instead of MEMBERS to prevent
displaying the All members of the three dimensions involved in the query.
The NON EMPTY keyword is used to avoid displaying customers that never
ordered articles from a particular category. Finally, we state the measure to
be displayed as a slicer in the WHERE clause.