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

Here, we use the LAG function and the range operator ' : ' to construct the set
composed of the current month and its preceding 2 months. Then, we take
the average of the measure Sales Amount over these 3 months.

July 1996 August 1996 September 1996 October 1996 ···

Beverages $3,182.50 $3,288.69 $2,998.99 $4,172.77 ···

Condiments $1,753.40 $1,570.85 $1,334.43 $2,124.74 ···

Confections $5,775.15 $5,390.96 $5,509.31 $4,760.46 ···

Dairy Products $6,838.34 $5,800.02 $4,805.35 $4,838.42 ···

Grains/Cereals $1,158.86 $714.73 $719.69 $1,123.72 ···

Meat/Poultry $2,268.72 $2,882.19 $3,351.79 $3,812.65 ···

Produce $3,868.80 $2,336.56 $1,945.97 $944.04 ···

Query 6.12. Personal sales amount made by an employee compared with
the total sales amount made by herself and her subordinates during 1997.
WITH MEMBER Measures.[Personal Sales] AS
(Employee.Supervision.DATAMEMBER, [Measures].[Sales Amount]),
FORMAT STRING = ' $###,##0.00 '

SELECT ON COLUMNS,

ORDER(Employee.Supervision.MEMBERS - Employee.Supervision.[All],
Employee.Supervision.CURRENTMEMBER.NAME, BASC) ON ROWS
FROM Sales
WHERE [Order Date].Calendar.Year.[1997]

{ Measures.[Personal Sales], Measures.[Sales Amount] }

Andrew
Fuller

Employee

Nancy
Davolio

Janet
Leverling

Margaret
Peacock

Steven
Buchanan

Laura
Callahan

Employee

Michael
Suyama

Robert
King

Anne
Dodsworth

Employee

Fig. 6.2 Supervision hierarchy in the Employee dimension (repeated from Fig. 4.5 )

In this query, we use the parent-child hierarchy Supervision of the Employee
dimension, as depicted in Fig. 6.2 . In such a hierarchy, each employee has
personal sales amount values. As we have seen in Sect. 5.9.3 , such value is kept
in system-generated child members. This value can be accessed in MDX using
the keyword DATAMEMBER as shown in the calculated measure Personal
Sales of the above query. Furthermore, the value of the total sales amount for
an employee at the lower level of the hierarchy (i.e., without subordinates,
such as Robert King) is equal to its personal sales. For employees with
subordinates, the value of the measure is the sum of her personal sales