July 22, 2003

MDX in Analysis Services: Measuring Change over Time

By William Pearson

Let's insert an argument to leverage further the flexibility of the expression.

16. Return to the Value Expression box for the Parallel Amount calculated member.

17. Place the following text between the parentheses for the argument that we discussed earlier:

[Year]

The completed expression should now appear as below:

(ParallelPeriod([Year]),[Amount])

18. Click OK.

19. Expand 1998 as we did for 1997 earlier, as shown in Illustration 29.

Illustration 29: Expanded 1998

20. Drag the Parallel Amount calculated measure to the top of the calculated measures listed in the Cube tree, to make comparisons between Amount and Parallel Period easier (simply drag and drop Parallel Amount over the top calculated member to perform the desired substitution).

The relevant portion of the results appears as shown in Illustration 30:

Illustration 30: The Results with the Modified ParallelPeriod() Function

As we see above, the Amount value (at each level) now has a matching Parallel Amount in the respective reporting period of the previous year (except, obviously, where no data exists within the cube to be retrieved).

We will now conclude the lesson with the computation of a variance (often called a "delta" or, basically enough, "growth") between the current Amount and the Prior and Parallel Amounts. This is handled via a simple subtraction of one calculated member from another, as we see in the following steps:

21. Double-click the Quarter dimension label once again, to zoom up - and free real estate for our next step.

22. Select Insert (top menu). Select Calculated Member on the drop-down menu.

The Calculated Member Builder appears.

23. Type Chg Over Parallel Period into the Member Name box.

24. In the Value Expression box, type the following expression:

[Amount]-[Parallel Amount]

25. Click OK to accept the expression entered.

Compare the result set to that partially shown in Illustration 31. Keep in mind the effects that are to be expected when dealing with debit / credit (+ or -) signs.

Illustration 31: The New Chg Over Parallel Period Column Appears

Notice that we did not 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.

26. Select Insert (top menu).

27. Select Calculated Member on the drop-down menu.

The Calculated Member Builder appears.

28. Type Chg Over Prior Period into the Member Name box.

29. In the Value Expression box, type the following expression:

[Amount]-[Prior Amount]

30. Click OK to accept the expression entered.

Compare the result set to partially shown in Illustration 32.

Illustration 32: The New Change Over Prior Period Column Appears

While the examples above restrict themselves to simple variances, percentage variances and a host of other embellishments might be added via a well-placed calculated member in the same general manner.

31. Save the work accomplished in this session by selecting File -> Save from the top menu, as desired.

In conclusion, our efforts have shown the relative ease with which we can embed the concept of time within our 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 is integral to the design of MDX makes handling time members quite similar to handling members of any other dimension.

Next in Our Series ...

In this tutorial, we expanded upon the topics we introduced in our previous lesson, Retrieve Data from Multiple Cubes. We began the lesson with another integrated practice example, where we explored further the handling of hierarchical relationships in our expressions. We practiced using conditional tests to handle "divide-by-zero" scenarios. Finally, we introduced the concept of time within the context of expression design, and worked through examples of providing support for time-based analysis, such as the quantification of change over time in values, with MDX functions designed for that purpose.

Our next lesson, Using Named Sets, will begin an exploration of MDX Sets. We will discuss the differences between MDX expressions and MDX query statements, and practice working with rudimentary queries. We will explore set functions as part of our tutorial, as well as the general creation of sets and some of their potential uses.

» See All Articles by Columnist William E. Pearson, III