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

1996 1997 1998

Alfreds Futterkiste Beverages $553.50

Alfreds Futterkiste Condiments $938.00 $400.80

Alfreds Futterkiste Dairy Products $1,255.00

Alfreds Futterkiste Produce $513.00 $91.20

Alfreds Futterkiste Seafood $18.00 $503.50

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

Query 6.2. Yearly sales amount for each pair of customer country and
supplier countries.
SELECT [Order Date].Year.MEMBERS ON COLUMNS,
NON EMPTY Customer.Country.MEMBERS *
Supplier.Country.MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]

In this query, we use a cross join of the Customer and Supplier dimensions to
display the pair of countries from both dimensions in the row axis.

All 1996 1997 1998

Austria Denmark $675.67 $432.00 $243.67

Austria Finland $900.00 $900.00

Austria France $29,307.19 $12,437.20 $4,569.99 $12,300.00

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

Query 6.3. Monthly sales by customer state compared to those of the
previous year.
WITH MEMBER Measures.[Previous Year] AS
(Measures.[Sales Amount],
PARALLELPERIOD([Order Date].Calendar.Month,12)),
FORMAT STRING = ' $###,##0.00 '
SELECT { Measures.[Sales Amount], Measures.[Previous Year] } ON COLUMNS,
NON EMPTY ORDER(Customer.Geography.State.MEMBERS,
Customer.Geography.CURRENTMEMBER.NAME, BASC) *
[Order Date].Calendar.Month.MEMBERS ON ROWS
FROM Sales
In this query, we do a cross join of the Customer and Order Date dimensions
to display the states and months on the row axis. We use the ORDER function
to sort the states of the customers in alphabetical order irrespective of the
Geography hierarchy. The calculated measure Previous Year computes the sales
amount of the same month of the previous year for the current state and
month using the PARALLELPERIOD function. The format for displaying the
new measure is also defined.