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

Beverages Condiments Confections Dairy Products ···

Maison Dewey $108.00 $680.00 $2,659.38 $2,972.00 ···

Supremes delices $3,108.08 $1,675.60 $4,820.20 $5,688.00 ···

Blondesddsl pere et fils $3,975.92 $1,939.00 $2,872.00 ···

Bon app ' $877.50 $2,662.48 $2,313.67 $1,912.43 ···

La maison d ' Asie $1,499.15 $525.90 $2,085.90 $757.76 ···

Du monde entier $194.00 $60.00 $201.60 ···

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

The PREVMEMBER function can be used to show growth over a time
period. The following query displays net sales and the incremental change
from the previous time member for all months in 1996:

WITH MEMBER Measures.[Net Sales Growth] AS
(Measures.[Net Sales]) -
(Measures.[Net Sales], [Order Date].Calendar.PREVMEMBER),
FORMAT STRING = ' $###,##0.00; $-###,##0.00 '

SELECT { Measures.[Net Sales], Measures.[Net Sales Growth] } ON COLUMNS,

DESCENDANTS([Order Date].Calendar.[1996],
[Order Date].Calendar.[Month]) ON ROWS
FROM Sales

The format expression above defines two formats, the first one for positive
numbers and the second one for negative numbers. Using NEXTMEMBER in
the expression above would show net sales for each month compared with
those of the following month. The result of the query is given next.

Net Sales Net Sales Growth

July 1996 $25,982.68 $25,982.68

August 1996 $21,849.97 $-4,132.71

September 1996 $19,698.94 $-2,151.03

October 1996 $32,586.14 $12,887.20

November 1996 $42,337.16 $9,751.03

December 1996 $39,919.97 $-2,417.19

As shown above, the net sales growth for the first month is equivalent to the
net sales. Since the cube only holds sales starting from July 1996, the growth
for the first month cannot be measured. In this case, a value of zero is used
for the previous period that is beyond the range of the cube.
In the query above, instead of the PREVMEMBER function, we can use
the LAG(n) function, which returns the member located a specified number
of positions preceding a specific member along the member dimension. If
the number given is negative, a subsequent member is returned; if it is zero,
the current member is returned. Thus, PREV , NEXT ,and CURRENT can be
replaced with LAG(1) , LAG(-1) ,and LAG(0) , respectively. A similar function
called LEAD exists, such that LAG(n) is equivalent to LEAD(-n) .