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

6.1.9 Time Series Functions

Time period analysis is an essential component of business intelligence
applications. For example, one could want to examine the sales of a month
or quarter compared to those of the same month or quarter last year. MDX
provides a powerful set of time series functions for time period analysis. While
their most common use is with a time dimension, most of them can also be
used with any other dimension.
The PARALLELPERIOD function is used to compare values of a specified
member with those of a member in the same relative position in a prior
period. For example, one would compare values from one quarter with those
of the same quarter in the previous year. In the previous query, we used the
PREVMEMBER function to compute the growth with respect to the previous
month. The PARALLELPERIOD function can be used to compute the growth
with respect to the same period in the previous year, as shown next:

WITH MEMBER Measures.[Previous Year] AS
(Measures.[Net Sales],
PARALLELPERIOD([Order Date].Calendar.Quarter, 4)),
FORMAT STRING = ' $###,##0.00 '
MEMBER Measures.[Net Sales Growth] AS
Measures.[Net Sales] - Measures.[Previous Year],
FORMAT STRING = ' $###,##0.00; $-###,##0.00 '
SELECT { [Net Sales], [Previous Year], [Net Sales Growth] } ON COLUMNS,
[Order Date].Calendar.Quarter ON ROWS
FROM Sales

Here, the PARALLELPERIOD selects the member that is four quarters (i.e.,
a year) prior to the current quarter. The query result is as follows:

Net Sales Previous Year Net Sales Growth

Q3 1996 $67,531.59 $67,531.59

Q4 1996 $114,843.27 $114,843.27

Q1 1997 $125,174.40 $125,174.40

Q2 1997 $121,518.78 $121,518.78

Q3 1997 $133,636.32 $67,531.59 $66,104.73

Q4 1997 $159,989.61 $114,843.27 $45,146.34

Q1 1998 $259,322.36 $125,174.40 $134,147.95

Q2 1998 $104,552.03 $121,518.78 $-16,966.75

As can be seen, the net sales growth for the third quarter of 1997 is the
difference between the net sales in that quarter and the net sales of the third
quarter of 1996. Notice that the net sales growth for the first four quarters
is equal to the net sales. As already said, since the Northwind cube contains
sales data starting from July 1996, the net sales for the first four quarters
shown in the result above is null. In this case, a value of zero is used for
parallel periods beyond the range of the cube.