MDX in Analysis Services: Measuring Change over Time - Page 6
July 22, 2003
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).
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.
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:
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.
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.
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.