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

Sales Amount Previous Year

Alaska September 1996 $3,741.30

Alaska October 1996 $934.50

Alaska February 1997 $1,755.00

Alaska July 1997 $565.50

Alaska September 1997 $1,261.88 $3,741.30

Alaska October 1997 $1,893.00 $934.50

Alaska January 1998 $3,638.89

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

Query 6.4. Monthly sales growth per product, that is, total sales per
product compared to those of the previous month.
WITH MEMBER Measures.[Previous Month] AS
(Measures.[Sales Amount],
[Order Date].Calendar.CURRENTMEMBER.PREVMEMBER),
FORMAT STRING = ' $###,##0.00 '
MEMBER Measures.[Sales Growth] AS
(Measures.[Sales Amount]) - (Measures.[Previous Month]),
FORMAT STRING = ' $###,##0.00; $-###,##0.00 '

SELECT { Measures.[Sales Amount], Measures.[Previous Month],

Measures.[Sales Growth] ON COLUMNS,

NON EMPTY ORDER(Product.Categories.Product.MEMBERS,
Product.Categories.CURRENTMEMBER.NAME, BASC) *
[Order Date].Calendar.Month.MEMBERS ON ROWS
FROM Sales
In this query, we do a cross join of the Product and Order Date dimensions
to display the products and months on the row axis. The calculated measure
Previous Month computes the sales amount of the previous month of the
current category and month, while the calculated measure Sales Growth
computes the difference of the sales amount of the current month and the
one of the previous month.

}

Sales Amount Previous Month Sales Growth

Alice Mutton July 1996 $936.00 $936.00

Alice Mutton August 1996 $819.00 $936.00 $-117.00

Alice Mutton September 1996 $1,248.00 $819.00 $429.00

Alice Mutton October 1996 $2,948.40 $1,248.00 $1,700.40

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

Query 6.5. Three best-selling employees.

SELECT Measures.[Sales Amount] ON COLUMNS,
TOPCOUNT(Employee.[Full Name].CHILDREN, 3,
Measures.[Sales Amount]) ON ROWS
FROM Sales