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.

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

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

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:

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

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.

The Calculated Member Builder appears.

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

The Calculated Member Builder appears as shown in Illustration 5.

The Data Viewing pane appears as shown below.

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.

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

The Calculated Member Builder appears.

[Amount]/[Units Sold]

The Data Viewing pane appears as shown in Illustration 8.

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.

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

(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.

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

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

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.

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

Compare the result set to that shown in Illustration 13.

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.

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

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

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.

The Calculated Member Builder appears.

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

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

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

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

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.

, [Amount]

The final expression appears in Illustration 19 below:

19.  Click OK to accept the expression as displayed.

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

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.

The Calculated Member Builder appears.

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

, [Avg Total Cost Per Unit]

The final expression appears in Illustration 22 below:

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

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

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

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.

The "-" prefixing the label name changes to "+", signifying that we have collapsed it (the axes labels should appear as shown in Illustration 25).

The Calculated Member Builder appears.

(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.

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.

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

[Year]

The completed expression should now appear as below:

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

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

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:

The Calculated Member Builder appears.

[Amount]-[Parallel Amount]

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.

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.

The Calculated Member Builder appears.

[Amount]-[Prior Amount]

Compare the result set to partially shown in Illustration 32.

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.

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