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

and those of her subordinates, as is the case for Andrew Fuller and Steven
Buchanan. Notice that in this query we removed the member All from the
set of members of the Supervision hierarchy, using the set difference operator
denoted '-'. If in the query above we replace Employee.Supervision.MEMBERS
with Employee.Supervision.CHILDREN , we will obtain only the first line of
the answer corresponding to Andrew Fuller. As can be seen, parent-child
hierarchies behave to this respect differently from user-defined hierarchies.

Personal Sales Sales Amount

Andrew Fuller $68,063.09 $596,630.80

Anne Dodsworth $18,906.49 $18,906.49

Janet Leverling $105,351.30 $105,351.30

Laura Callahan $56,032.62 $56,032.62

Margaret Peacock $128,809.79 $128,809.79

Michael Suyama $38,213.37 $38,213.37

Nancy Davolio $90,629.08 $90,629.08

Robert King $59,908.60 $59,908.60

Steven Buchanan $30,716.47 $147,744.92

It is worth remarking that the personal sales amount made by an employee
can also be obtained with the following query, which exploits the attribute
hierarchy [Full Name] instead of the parent-child hierarchy Supervision :
SELECT Measures.[Sales Amount] on COLUMNS,
Employee.[Full Name].CHILDREN ON ROWS
FROM Sales
WHERE [Order Date].Calendar.Year.[1997]

Query 6.13. Total sales amount, number of products, and sum of the
quantities sold for each order.
WITH MEMBER Measures.[NbProducts] AS
COUNT(NONEMPTY([Order].[Order No].CURRENTMEMBER *
[Order].[Order Line].MEMBERS))

SELECT { Measures.[Sales Amount], NbProducts, Quantity } on COLUMNS,

[Order].[Order No].CHILDREN ON ROWS
FROM Sales
In this query, we use the fact (or degenerate) dimension Order ,whichis
defined from the fact table Sales in the data warehouse. The dimension has
two attributes, the order number and the order line, and the order number is
displayed on the rows axis. In the calculated measure NbProducts , a cross join
is used to obtain the order lines associated with the current order. By counting
the elements in this set, we can obtain the number of distinct products of
the order. Finally, the measures Sales Amount , NbProducts ,and Quantity are
displayed on the column axis.