Analyzing
Change in Values from a Parallel Measurement Period
Another
real-world example of a time-based analysis scenario incorporates the
concept of "parallel" periods (the term used in Analysis
Services, and among many in the Finance and Accounting arenas). In
essence, the concept entails comparing a period in, say, the current year, with
the corresponding period in the prior year (for example, say we are in the
month of June, 1998; the parallel period to which we might want
to compare operating results would be June, 1997).
This
comparison is, in many cases, appropriate, because of seasonality in
business operations. A good illustration might be seen in the case of a retail
establishment: It would be more useful to compare the sales in the fourth
quarter of a given calendar year (the Christmas shopping season) with those of
the fourth quarter of the previous calendar year, rather than to compare Q4
1998 results to Q3 1998 results, when sales are hardly comparable.
MDX
provides "parallel" functions to support these sorts of
analytical needs. We will practice an example through the following steps,
beginning where we left off in the last section.
1.
Double-click
the Quarter label (atop the Quarter column in the Data Viewing
pane) to collapse the quarter rows.
The "-"
prefixing the label name changes to "+", signifying that we
have collapsed it (the axes labels should appear as shown in Illustration 25).
Illustration 25:
Collapsing the Quarters (Axes Labels View)...
2.
Double-click
the Quarter label again, to expand it uniformly, as shown in Illustration
26, to show the months comprising each of the quarters.
Illustration 26:
Expanding the Quarters Uniformly ...
3. Select Insert --> Calculated Member from the top menu.
The Calculated
Member Builder appears.
4.
Type Parallel
Amount into the Member Name box.
5.
Type the
following expression into the Value Expression box:
(ParallelPeriod(),[Amount])
While
the ParallelPeriod function can be found in the Member folder of
the Functions tree, similarly to the PrevMember function with
which we dealt earlier, our example expression is simple, and is actually
easier to type than to build from the objects in the Functions and Data
trees. The "()" shown in the expression above represents an
empty argument pair, whereby, much like other functions, we can stipulate "levels
removed" from the current member, as well as other
sophistications. The arguments are, however, optional, and will not be needed
for our initial practice exercise.
NOTE: For more information concerning
various MDX functions, I invite you to see the sister DatabaseJournal
series, MDX Essentials.
The
new expression should be identical to that pictured in Illustration 27.
Illustration 27: A
Simple Example of a ParallelPeriod() Function
6.
Click OK.
7.
Click the Avg
Total Cost Per Unit calculated member in the cube tree to select it.
8.
In the Properties
pane, select the Advanced tab.
9.
Click the Visible
property.
10.
Select False
in the selector box for the Visible property.
11.
Click the Prior
Avg calculated member in the cube tree to select it.
12.
In the Properties
pane, select the Advanced tab.
13.
Click the Visible
property.
14.
Select False
in the selector box for the Visible property.
15.
Press the Enter
key.
The results
should appear as partially depicted in Illustration 28.
Illustration 28: Partial
Results Set in the Data Viewing Pane (Compressed)
First,
we have suppressed the averages that we used in the earlier section to allow us
more space. Next, we see that the effect of the expression has been to match
period (Month, in this case) for a given Quarter level with the
same relative Month (first, second, or third) of the Quarter
immediately preceding it. This is a "parallel month" in its
most basic form. For example, The Parallel Amount in Q4, Period 4 (or
Month 12), $ 29,931.74, equals the Amount in Q3, Period 4 (or Month 9).
We note, too, if we similarly expand the 1998 year, that the Quarter
Amounts of the same Quarter in the previous year are shown as the Parallel
Amount, as we might expect.
» 
Comment and Contribute
