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

Sales Amount NbProducts Quantity

10248 $342.00 2 17

10249 $1,863.40 2 49

10250 $1,552.60 3 60

10251 $654.06 3 41

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

Query 6.14. For each month, total number of orders, total sales amount,
and average sales amount by order.
WITH MEMBER Measures.AvgSales AS
Measures.[Sales Amount]/Measures.[Order No],
FORMAT STRING = ' $###,##0.00 '

SELECT ON COLUMNS,

NON EMPTY [Order Date].Calendar.Month.MEMBERS ON ROWS
FROM Sales

{ Measures.[Order No], [Sales Amount], AvgSales }

This query displays the months of the Order Date dimension on the row axis
and the measures Order No , Sales Amount ,and AvgSales on the column axis,
the latter being a calculated measure. For Sales Amount , the roll-up operation
computes the sum of the values in a month. For the Order No measure, since
in the cube definition the aggregate function associated with the measure is
DistinctCount , the roll-up operation computes the number of orders within
a month. Notice that for computing the average in the calculated measure
AvgSales , we divided the two measures Sales Amount and Order No .Ifweused
instead AVG(Measures.[Sales Amount]) , the result obtained will correspond to
the Sales Amount . Indeed, the average will be applied to a set containing as
only element the measure of the current month.

Order No Sales Amount AvgSales

July 1996 21 $27,246.10 $1,297.43

August 1996 25 $23,104.98 $924.20

September 1996 21 $20,582.40 $980.11

October 1996 25 $33,991.56 $1,359.66

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

Query 6.15. For each employee, total sales amount, number of cities, and
number of states to which she is assigned.
WITH MEMBER NoCities AS
Measures.[Territories Count]
MEMBER NoStates AS
DISTINCTCOUNT(Employee.[Full Name].CURRENTMEMBER *
City.Geography.State.MEMBERS)
SELECT { Measures.[Sales Amount], Measures.NoCities, Measures.NoStates }
ON COLUMNS, Employee.[Full Name].CHILDREN ON ROWS
FROM Sales