MDX in Analysis Services: Measuring Change over Time

Tuesday Jul 22nd 2003 by William Pearson
Share:

Explore the incorporation of time-based analysis into MDX expressions in Analysis Services. Author Bill Pearson presents a hands-on lesson in using MDX to analyze change in values from a prior period, a parallel period, and more.

About the Series ...

This is the fifth tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services ("Analysis Services,"); our primary focus is the manipulation of multidimensional data sources, using MDX expressions in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

Introduction

In our last tutorial, we exposed the use of the MDX LookupCube function within Analysis Services, as a means of retrieving values from multiple cubes simultaneously. We discussed how this offers us the often-useful option of accessing multiple OLAP data sources together for analysis and reporting, presenting an example real-world scenario where this option would be valuable. In addition to exploring the retrieval of data from multiple cubes, we demonstrated how we could compute a per-unit average, based upon values retrieved from two separate OLAP data sources, while further exploring and practicing the use of calculated members as part of the process.

In this lesson, we will explore the use of MDX functions that incorporate the concept of time into the analysis product that we wish to create, within the context of expression design. We will practice adding support for time-based analysis, such as the quantification of change in values over time, with MDX functions that are ideally suited for that purpose. Moreover, we will perform exercises to reinforce these concepts within the context of our OLAP data sources.

In this article, we will examine the roles that MDX can play in helping us to support the time-based analysis needs of information consumers, as well as increase our general knowledge of MDX in Analysis Services, by:

  • Building the concept of time into our expression designs;
  • Incorporating support for time-based analysis with MDX functions;
  • Performing additional practice with the "from scratch" creation of calculated members
  • Handling "divide-by-zero" scenarios in calculated members;
  • Generating variance calculations.

Measuring Change over Time with MDX Expressions

As our next topic in meeting the business requirements of information consumers, we will 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, providing a basis for comparison of the current period results to those of the immediately preceding period (be it a year, quarter, month, or whatever), or providing a means for comparing parallel periods (Analysis Services terminology for the current month, but in the prior year, as an example) to build in seasonality factors for the business, ascertain the effectiveness of new initiatives (such as marketing campaigns) begun in the current quarter over the last quarter, and so on.

Preparing for our Examination of the Time Functions: A Review of Calculated Members

To prepare for our exploration of the MDX time functions let's open Analysis Manager (see previous tutorials if you need a refresher on Analysis Manager, or if you are joining the current series with this article), and create a new calculated member within the Budget cube. This will provide a "fresh start," and allow us a review of a few steps we accomplished before, in keeping with the desire to maintain our lessons as standalone tutorials, wherever possible, and for the purpose of continuity and portability.

1.      From the Analysis Manager console, within the FoodMart 2000 database sample, expand the Cubes folder (seen in the Illustration 1), by clicking the "+" sign to its left.


Illustration 1: Sample Cubes provided with the Analysis Services Installation

2.      Right click the Budget cube.

3.      Click Edit from the flyout shortcut menu.

4.      When the Cube Editor screen appears, click the Data tab at its lower left (Data Viewing pane).

The Data Viewing pane is activated, retrieving the view that was last saved, or perhaps the default, if none was saved from previous lessons.

5.      Drag the Store dimension from the top pane down and over to the current heading atop the Rows axis. (You can drop the icon that appears over the heading currently in place - a small, double-headed arrow appears at the "drop" point), to "swap" the current dimension to the top and Store below (it will appear as Store Country, the label that appears physically above All Stores (technically the top level), in the current dimension's old place).

6.      Drag the Measures dimension from the top pane down to replace the dimension in the Columns axis, unless it is already there. The heading will appear as MeasuresLevel, with the measure Amount appearing just underneath.

7.      Ensure that 1997 is selected in the filter field for the Time dimension atop the Data Viewing pane.

The contents of the Data Viewing pane now appear as shown below:


Illustration 2: The Data Viewing Pane Contents after our Modifications (Compressed View)

Let's create a calculated measure similar to the one we created in the last article of our series, Retrieve Data from Multiple Cubes, computing an average based upon two measures. Once again, we will create a calculated example metric that might be useful to information consumers at a high level, perhaps in identifying significant outliers (realizing, of course, that this would only be a rough indicator, that would perhaps serve to identify further, more precisely designed analysis opportunities)

Our first focus will be to retrieve the Sales Units data from the Sales cube (another sample cube that comes along with the Analysis Services installation), just as we accomplished in our last lesson. To that end, we will use a "lookup" function to create a calculated member based upon components in two separate cubes. We will take the following steps to proceed:

8.      Click the filter field for the Account dimension, and, from the dropdown hierarchy that appears, click the "+" signs continually to expand to, and select, Gross Sales, as shown in Illustration 3.


Illustration 3: Select Gross Sales as the Filter

9.      Double-click the USA member of the Store Country level (Rows axis) to explode to the USA Store State view of the Store dimension for USA.

The result set should be identical to that shown in Illustration 4.


Illustration 4: The Results in the Data Viewing Pane (Compressed View)

As in our earlier lesson, we notice that the Budget cube contains data for USA Stores only.

Next, let's create a new calculated measure as a conduit from the sample Sales cube, to bring in data that we require for our current activities.

10.  Select Insert -> Calculated Member from the top menu.

The Calculated Member Builder appears.

11.  Type Units Sold into the Member Name box.

12.  In the Value Expression box, input the following expression:

LookupCube("Sales","([Unit Sales],"+[Store].CurrentMember.UniqueName+")")

The Calculated Member Builder appears as shown in Illustration 5.


Illustration 5: The Calculated Member Builder, with New Expression Entered

13.  Click OK to accept the expression entered as above.

The Data Viewing pane appears as shown below.


Illustration 6: The Sales Units Data as Retrieved from the Sales Cube

For more information on the rationale behind the expression we have entered above, see our last lesson in this series, Retrieve Data from Multiple Cubes.

Now that we have the number of units sold, let's create a calculated member that we will use as the vehicle to illustrate how MDX can help us support time-based analysis. But first, let's vary our requirement from the last lesson and change to a view of expenses, versus the current revenues perspective.

14.  Select Total Expense in the filter field for the Account dimension atop the Data Viewing pane.

The contents of the Data Viewing pane now appear as shown in Illustration 7.

Click for larger image

Illustration 7: The Data Viewing Pane Contents after Changing the Accounts Filter (Compressed View)

15.  Select Insert -> Calculated Member from the main menu once again.

The Calculated Member Builder appears.

16.  Type Avg Total Cost Per Unit into the Member Name box. In the Value Expression box, type the following expression:

[Amount]/[Units Sold]

17.  Click OK to accept the expression entered.

The Data Viewing pane appears as shown in Illustration 8.


Illustration 8: The Total Expense Amount with the Avg Total Cost per Unit Calculated Measure

We note that, while all else appears to be working reasonably (at least from a cursory level), the Avg Total Cost Per Unit for Canada and Mexico is suffering from the "divide by zero" malady we discussed in Lesson Three: Intermediate Concepts - Part 2. Let's handle this "undefined" condition by adapting the expression behind our Avg Total Cost Per Unit calculated member to handle the occasions where we are presented with zeros in the member denominators.

18.  Right-click the Avg Total Cost Per Unit calculated member.

19.  Select Edit from the context menu that appears

The Calculated Member Builder reappears.

20.  In the Value Expression box, replace the existing expression with the following:

IIF(IsEmpty(([Units Sold],[Store].CurrentMember)), 0, [Amount]/ ([Units Sold], [Store].CurrentMember))

 

21.  Click OK to apply the changes.

The result set that is retrieved should appear as shown in Illustration 9.


Illustration 9: The New Result Set from the Data Viewing Pane

(Remember to take advantage of the Check button to make sure that the correct number of parentheses, etc., is placed anytime you work with the Value Expression in the Calculated Member Builder.)

We obtain the desired results for Avg Total Cost Per Unit for the Stores depicted, noting that the Canadian and Mexican stores now display zeros instead of confusing results, both at this and the various drill-down levels.

Let's make a few cosmetic adjustments while we're here, before moving on to our primary focus.

22.  Ensure the Avg Total Cost Per Unit calculated member is selected in the Cube tree.

23.  Select the Basic tab of the Properties pane, which appears underneath the Cube tree, if not already selected.

24.  Ensure that the Measures dimension is selected for the Parent Dimension property.

25.  Click the Advanced tab in the Properties pane.

26.  Select the Format String property of Avg Total Cost Per Unit.

27.  Click the drop-down arrow.

28.  Select Currency as the format for the string.

The Advanced tab in the Properties pane appears as shown in Illustration 10.


Illustration 10: The Format String Setting in the Advanced Properties Pane

29.  Select Units Sold in the Calculated Members folder for the Budget cube.

30.  Select the Basic tab in the Properties pane.

31.  Select the Parent Dimension property of Units Sold, on the Basic tab.

32.  Ensure that the Measures dimension is selected (via the drop-down arrow).

33.  Click the Advanced tab in the Properties pane.

34.  Select the Format String property of Units Sold.

35.  Click the drop-down arrow.

36.  Select #,# (whole units with thousands separator - no decimal placed) as the format for the string.

37.  Press Enter.

The updated Data Viewing Pane appears as shown in Illustration 11.


Illustration 11: The Dataset with New Formatting Refinements

We see both the Units Sold and the Avg Total Cost Per Unit calculated members appear, complete with our formatting specifications.

The forgoing steps have served as both preparation for primary objective of our lesson and a hands-on review and practice of some of the things we have learned about calculated members before this lesson. We will move now to our exploration of the MDX time functions.

Analyzing the Effects of Time with MDX Expressions

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 will explore the ways we can use these functions to achieve our 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 use the calculated members we have created in the immediately foregoing section 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, but to a perhaps unnatural extreme to those in Accounting and Finance (just kidding - I'm a recovering CPA myself) is the concept of analyzing change over the prior operating period, particularly over the prior month. MDX handles time like any other dimension - within the familiar structure of the hierarchies and members that it handles so effectively. 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 our information consumers.

At this point, we will use our new calculated members within an exploration of the ways we can use MDX time functions to achieve our time-based analysis objectives. After first making adjustments to help them to perform in a more adaptive manner, we will explore the steps required to build in time-based analysis features.

1.      Double-click the USA member of the Store Country level (Rows axis), once again, to contract the USA Store State view, and to "drill up" to USA only.

The Data Viewing pane should appear as shown in Illustration 12.


Illustration 12: The Results in the Data Viewing Pane

2.      Drag the Time dimension from the top pane down to replace the dimension in the Rows axis. The heading will appear as Year, with the levels 1997 and 1998 appearing just underneath.

3.      Expand (by double-clicking) the 1997 and Q4 levels in the Row axis.

Compare the result set to that shown in Illustration 13.


Illustration 13: The Results in the Data Viewing Pane

We see immediately that, while the Amount measure changes to reflect our "slice and dice" operation, the Units Sold calculated member remains fixed - for all rows. We need to adjust our Units Sold calculation to reflect the new dimensions of our display. The Avg Total Cost Per Unit measure should, of course, adjust itself automatically, subsequent to our modifications to the Units Sold calculation; it is

merely derived from the Units Sold measure and the Amount measure, after all.

4.      Right-click the Units Sold calculated member, once again.

5.      Select Edit to open the Calculated Member Builder again.

6.      Change the Value Expression for the calculated member to the following:

LookupCube("Sales","([Unit Sales],"+[Time].CurrentMember.UniqueName +")")

7.      Click OK to apply the new expression.

The new dataset is returned, and appears as shown in Illustration 14.


Illustration 14: The Results in the Data Viewing Pane with Unit Sales Modified Expression

We have simply replaced the [Store] dimension with the [Time] dimension in the expression to make our results "dimension -sensitive," as it were, and causing them to behave as expected for the different Time levels and members in the respective rows of the display.

Now let's add a calculated member that uses a time function - and presents a measure that is the equivalent of the existing Amount measure, with which it can be compared side-by-side to illustrate its nature.

8.      Select All Account in the filter field for the Account dimension.

9.      Select Insert --> Calculated Member

The Calculated Member Builder appears.

10.  Type Prior Amount into the Member Name box.

11.  In the Functions tree, expand the Member folder.

The present view within the Calculated Member Builder should appear as shown in Illustration 15.


Illustration 15: Preparing to Add the Expression (Compressed View)

12.  Double-click the PrevMember function within the Member folder.

13.  Single-click the <<Member>> token, within the Value Expression box, to select it.

The <<Member>> token should highlight to indicate selection, as partially shown in Illustration 16.


Illustration 16: Select the <<Member>> Token

14.  Double-click the CurrentMember function within the Member folder.

15.  In the Value Expression box, single-click the newly appearing <<Dimension>> token to select it.

The <<Dimension>> token should highlight to indicate selection, as partially shown in Illustration 17.


Illustration 17: Select the <<Dimension>> Token

16.  Double-click the Time dimension within the Data tree.

The Value Expression box of the Calculated Member Builder now appears as shown in Illustration 18.


Illustration 18: The Expression Takes Form

NOTE: We explore the .PrevMember and .CurrentMember functions, as well as other concepts that we have encountered in this series, in depth in the sister DatabaseJournal series, MDX Essentials. Please see the appropriate article in the series for an explanation of the respective MDX function(s).

In our expression, the .PrevMember function 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 is quite effective as it ignores such boundaries and returns the immediately preceding member, as is desired in most cases.

17.  Append the following to the expression in the Value Expression box

, [Amount]

18.  Enclose the entire expression in parentheses.

The final expression appears in Illustration 19 below:


Illustration 19: The Expression as it Appears in the Value Expression Box

19.  Click OK to accept the expression as displayed.

The results dataset appears in the Data Viewing pane as depicted in Illustration 20.


Illustration 20: The Results in the Data Viewing Pane

We 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 Q4, Month 11 Prior Amount ($ 28,448.04) matches the Q4, Month 10 Amount. The same applies to the Quarter level rollups (except for periods for which the cube does not contain data for a prior period / a respective time member).

Now let's repeat the process for the Average Total Cost Per Unit calculated member.

1.      Select Insert --> Calculated Member

The Calculated Member Builder appears.

2.      Type Prior Avg into the Member Name box.

3.      In the Functions tree, expand the Member folder, as we did earlier.

4.      Double-click the PrevMember function within the Member folder.

5.      Single-click the <<Member>> token, within the Value Expression box, to select it.

6.      Double-click the CurrentMember function within the Member folder.

7.      In the Value Expression box, single-click the newly appearing <<Dimension>> token to select it.

8.      Double-click the Time dimension within the Data tree.

The Value Expression box of the Calculated Member Builder now appears as shown in Illustration 21.


Illustration 21: The Expression Takes Form

9.      Append the following to the expression in the Value Expression box

, [Avg Total Cost Per Unit]

10.  Enclose the entire expression in parentheses.

The final expression appears in Illustration 22 below:


Illustration 22: The Expression as it Appears in the Value Expression Box

11.  Click OK to accept the expression as displayed.

Let's free up some real estate on the screen.

12.  Click the Units Sold calculated member in the cube tree to select it.

13.  In the Properties pane, select the Advanced tab.

14.  Click the Visible property.

15.  Select False in the selector box for the Visible property.

The Properties pane - Advanced tab appears as depicted in Illustration 23.


Illustration 23: The Expression as it Appears in the Value Expression Box

16.  Press Enter to apply the modification.

The new results dataset appears in the Data Viewing pane, as depicted in Illustration 24.


Illustration 24: The Results in the Data Viewing Pane

As we noted in our earlier example, we can see that the values that appear in the Prior Avg column accurately reflect the prior period balance, as shown in the associated previous time period slot in the Avg Total Cost Per Unit column. For example, the Q4 Total Prior Avg ($ 1.50) matches the Q3 Total Avg Cost Per Unit value. The same applies to the Year level rollups (both the 1997 Total Avg Cost Per Unit and 1998 Total Prior Avg rows reflect $ 1.49).

MDX handles "parallel period" time considerations in much the same fundamental way: through the comprehension of hierarchical relationships, which was built into its design. Let's take a look at how we can leverage this functionality yet further in the final section of this lesson.

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.

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved