- About the Series
- Introduction
- Returning Values Based on Specified Dimensions
- Measuring Change over Time with MDX Expressions
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Measuring Change over Time with MDX Expressions
As a final section in our examination of several intermediate MDX concepts, we'll explore the Time dimension, a component of the vast majority of cubes that are created. Financial and other business reporting needs almost always contain a time element, the purposes of which typically include the isolation of results to a specific reporting period, such as the following:
Providing a basis for comparison of the current period results to those of the immediately preceding period (year, quarter, month, and so on)
Providing a means for comparing parallel periods (the current month, but in the prior year, for example) to build in seasonality factors for the business
MDX provides for the analysis of values over time with a large and robust group of functions that are well suited to the purpose. In this section, we'll explore the ways we can use these functions to achieve time-based analysis objectives.
Let's first look at a common scenario: the calculation of change over a period of time. As the basis of our study, we will again use the Budget cube, with a calculated member to expose the handling of this basic time consideration.
Analyzing Change in Values from a Prior Measurement Period
Near and dear to virtually any participant in business is the concept of analyzing change over the prior operating period, particularly over the prior month. MDX handles time like any other dimension—with hierarchies and members that it handles efficiently. Let's add a time consideration to our existing project, and get a feel for the ease with which MDX enables the support of the time-based analysis needs of information consumers.
First, let's provide some screen real estate for things to come by removing the calculated members from our recent efforts. Then we'll begin our exploration of the MDX time functions.
Select the Avg Rev Per Unit Sold calculated member.
Right-click and select Delete from the context menu.
Click Yes at the confirmation dialog box to complete the deletion.
In the same way, delete the Sales Units calculated member and the Expense % calculated member.
Drag the Time dimension from the top pane down to replace the dimension in the row axis. The heading will appear as Year, with the levels 1997 and 1998 appearing just underneath.
Expand the 1997 and Q4 levels in the Row axis.
Now we'll create a new calculated member to demonstrate the use of time functions in MDX expressions.
Double-click the Time dimension heading in the row axis to collapse and zoom the Time dimension back to the Year levels.
Double-click Year 1998 to expand Year 1998 to the Quarter level.
In similar fashion, drill down in the 1998 Quarter level to reach the Month level of 1998.
Select All Account in the filter field for the Account dimension.
Choose Insert, Calculated Member to display the Calculated Member Builder.
Type Prior Amount in the Member Name box.
In the Functions tree, expand the Member folder (see Figure 12).
Figure 12 Preparing to add the expression.Double-click the PrevMember function within the Member folder.
Click the <<Member>> token to select it.
Double-click the CurrentMember function within the Member folder.
Click the newly appearing <<Dimension>> token to select it.
Double-click the Time dimension within the Data tree.
Figure 13 shows the Calculated Member Builder at this point.
Figure 13 The expression takes form.
The PrevMember function works in a fashion very similar to that of Ancestor (another member function we've used several times in this series). It refers to the current member as a starting point, and returns the immediately preceding member occupying the same level in the Time hierarchy. This is especially useful within the context of year-end cutoffs and other financial and accounting time-range boundaries; the function ignores such boundaries and returns the immediately preceding member.
Append the following text to the expression in the Value Expression box, and then enclose the entire expression in parentheses.
, [Amount]
Figure 14 shows the final expression.
Figure 14 The expression as it appears in the Value Expression
box.Click OK to accept the expression as displayed. Figure 15 shows the results.
Figure 15 The results in the data viewing pane.
)
Figure 12)
Figure 13)
Figure 14)
Figure 15You can readily see that the values that appear in the Prior Amount column accurately reflect the prior period balance, as shown in the associated previous time period slot in the Amount column. For example, the Q3, Month 8 Prior Amount ($86.505.46) matches the Q3, Month 7 Amount. The same applies to the Quarter level rollups (except for periods for which the cube doesn't contain data for a prior period or other time member).
MDX handles parallel-period time considerations in much the same fundamental way: through the comprehension of hierarchical relationships that was built into its design.
Analyzing Change in Values from a Parallel Measurement Period
Another good example of real-world time-based analysis is the concept of parallel periods. In essence, the concept entails comparing a period in one year with the corresponding period in another year; for example, comparing June 1998 to June 1997.
This comparison is useful for comparing seasonal changes in business operations. For a retail establishment, for example, it might be 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'll practice an example with the following steps:
Drill down the Quarters level of the 1997 Year level to make the months of 1997 visible in addition to those of 1998 (for comparative purposes).
Choose Insert, Calculated Member to display the Calculated Member Builder.
Type Parallel Amount in the Member Name box.
Type the following expression in the Value Expression box (see Figure 16):
(ParallelPeriod(),[Amount])
While the ParallelPeriod function can be found in the Member folder of the Functions tree (similarly to the PrevMember function used earlier), our sample expression is actually easier to type than to build from the objects in the Functions and Data trees. The () in the expression above represents an empty argument pair, used to stipulate levels "removed" from the current member, as well as other sophistications. The arguments are optional, and won't be needed for our initial practice exercise.
Figure 16 A simple example of a parallel period expression.Click OK. The results should appear as partially depicted in Figure 17
Figure 17 The partial results set in the data viewing pane.
)
Figure 16)
Figure 17The effect of the expression has been to match a 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. Notice that the quarter amounts of the same quarter in the previous year are shown as the parallel amount.
Let's insert an argument to further leverage the flexibility of the expression.
Return to the Value Expression box for the Parallel Amount calculated member.
Place the following text within the parentheses for the argument discussed earlier:
[Year]
The completed expression should now appear as follows:
(ParallelPeriod([Year]),[Amount])
Click OK. Figure 18 shows the results.
Figure 18 The results with the modified ParallelPeriod
function.
)
Figure 18Each Amount value (each level) now has a matching Parallel Amount in the respective reporting period of the previous year (except where no data exists within the cube to be retrieved).
Let's conclude the lesson with the computation of a variance (often called delta or simply growth) between the current Amount and the Prior and Parallel Amounts. This is handled via a simple subtraction of one calculated member from another.
Double-click the Quarter dimension heading to zoom up, freeing some real estate for our next step.
Choose Insert, Calculated Member to display the Calculated Member Builder.
Type Change Over Prior Period in the Member Name box.
In the Value Expression box, type the following expression:
[Amount]-[Prior Amount]
Click OK to accept the expression entered.
Compare the result set to that partially shown in Figure 19. Keep in mind the effects that are to be expected when dealing with debit/credit (+ or -) signs.
Figure 19 The new Change Over Prior Period column appears.Notice that we didn't have to set the format string for the new calculated member; it automatically assumed the format string of the two calculated members from which it was computed.
Choose Insert, Calculated Member to display the Calculated Member Builder.
Type Change Over Prior Year in the Member Name box.
In the Value Expression box, type the following expression:
[Amount]-[Parallel Amount]
Click OK to accept the expression entered.
Compare the result set to that partially shown in Figure 20.
Figure 20 The new Change Over Prior Year column appears.Save your work by selecting File, Save from the top menu.
)
Figure 19)
Figure 20These exercises have shown the relative ease with which you can embed the concept of time within MDX expressions to support the time-based analysis needs of information consumers, such as the quantification of change in values over time. The inherent grasp of hierarchical relationships that's integral to the design of MDX makes handling time members quite similar to handling members of any other dimension.