About the Series ...

This article is a member 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; 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 the first lesson of this series: MDX Concepts and Navigation.

Overview

In this article, we resume the focus of a group of articles that began with Mastering Time: Change across Periods. In that article, as well as its immediate successor, Mastering Time: Period - to - Date Aggregations, we concentrated upon the Time dimension from the perspective of our MDX queries. Our intent, in these and occasional subsequent articles, is to explore ways to effectively report change over time, as well as to accumulate those changes to present snapshots, trends and other time-based metrics in a precise manner to meet typical business requirements. As most of us realize, time is the most pervasive dimension. A cube that has no time dimension is rare, indeed. Consequently, this group of articles holds information that is of interest to virtually anyone involved with MSAS cube design, development and use.

In this article, we will examine "rolling average" aggregations, a common business requirement. "Rolling," or "moving," averages, involve a measure, the average under consideration, that is aggregated over a progressively moving window of time periods. (While the window typically involves time, the functions that define the "window" involved here can certainly involve members of other dimensional levels).

Rolling averages are popular in the business community because they have the effect of smoothing the values of a quantity that fluctuates over time; these moving averages can be especially useful in cases where the values to which they are applied are subject to seasonal variations and other volatility factors. They aid us in "normalizing," or "flattening," the presentation of the metric for evaluation purposes. An example might be the S & P 500 Annual Yield 12-Month Rolling average, from a specific point in the past to recent times, a representation of which is depicted in Illustration 1.

As we have stated, the value of the moving average, whether presented in chart, tabular or other reports, often lies in its capacity to free us from some of the distraction of fluctuations that are meaningless, or at least not completely relevant, when it comes to trying to see long-term patterns in the analysis of a quantity / measure. Because they smooth a data series and make it easier to spot trends (something that is especially helpful in volatile markets, and in many other environments, as well), moving averages are one of the most popular and easy to use tools available to the technical analyst. Moving averages also form the building blocks for many other technical indicators and overlays.

In addition to being applied in the realm of stock prices, rolling averages are used with many other metrics that change frequently. We might, for instance, create a report to display weekly sales revenue over a three-year window. We could, in this example, plot the figures for our organization's sales revenue for each of the weeks, along with another row (or line, in the case of a chart) that displays a cumulative or a multi-week rolling average. A rolling average is generated simply by calculating the average of the current value, together with the specified number of previous values. The individual values are, of course, typically identified by a time period.

MDX affords us several approaches to generating rolling aggregates. We will explore one of these in this article, and another in the next article of this series. In both articles, we will overview the means of managing a rolling average requirement, using MDX within MSAS to accomplish our ends. We will then undertake a multi-step practice example that activates the underlying concepts, discussing our objectives, as well as the results we obtain, with each step. We will:

• Discuss considerations applicable to our approach, commenting generally upon the environment within which we will perform our practice exercises;
• Examine a hypothetical business requirement, in which a group of information consumers have requested a particular moving average capability for analysis purposes;
• Use the Sample Application to construct and test the MDX required to support a rolling average calculated member;
• Create a calculated member in Analysis Manager to provide permanent rolling average support in the cube;
• Verify accuracy of operation once again, from the Data view within the cube;
• Demonstrate that the rolling average calculated measure behaves in a "contextually sensitive" way, within the context of the time dimension levels.

Introduction to Moving Averages

Objective and Business Scenario

In the following sections, we will perform the steps required to create a calculated measure that generates a simple moving average within a sample cube, to illustrate cube-level support for an organizational analysis requirement. Once we have accomplished the simple structural additions to MSAS, we are free to use the new structure in any OLAP reporting solution that is capable of accessing MSAS. Within that solution, our use of the structures that we build here are limited only by our knowledge of the reporting tools we use, and the manner with which these tools interact with MSAS. Examples of these uses include the construction of a report that presents a rolling average of an important measure of performance within our organization, analysis of a rolling average of a critical measure with variable time granularity and range parameters, and the support of further key performance indicators within the perspectives of various organizational responsibility centers. We will develop the reporting side of these concepts in articles within my Reporting Services series at Database Journal.

For purposes of our practice procedure, we will assume that information consumers within the Finance department of the FoodMart organization have expressed the need for the capability to analyze a key measure, Warehouse Sales, on a rolling average basis. The consumers state that they wish to be able to examine the rolling average over a two-year time frame, on a quarterly basis. They need to be able to see the moving averages specifically for U.S. Store States, at present, but are cognizant that the ability to apply the rolling average of the measure to other perspectives, both within the store hierarchy ("drill up / down") and across other relevant dimensions ("slice and dice"), will be a "nice to have" capability.

We determine, in discussing the business requirements with the consumers, that we need to construct a calculated measure that will take the Warehouse Sales value for the current quarter (let's say we are Q4 of 1997, for purposes of illustration), then average that value with the same value from the last three quarters (Q1, Q2, and Q3 of 1997, to continue the illustration). As time passes, and as we move into the next quarter, the "four quarter" range over which the Warehouse Sales average is computed becomes Q2, Q3, and Q4 (of 1997), and Q1 of 1998.

We immediately recognize that such a rolling average might be valuable for other measures, as well as for other consumers in the organization. Therefore, we decide to make the investment in creating parameter prompt support for this functionality in the MSAS cube, while we are "under the hood," and to carry this capability into our reporting capabilities in Reporting Services (the same concepts are valid with other compatible reporting solutions, such as Cognos, Business Objects, Crystal Analysis Pro, and others). We do not develop this functionality in this article, whose focus is the MSAS calculated measure we have defined, but will delve into the procedures involved in creating these broader business intelligence capabilities in an article in our Reporting Services series.

Regardless of the end applications to which it is made available, a calculated measure that returns the rolling average provides numerous benefits, including easier, more consistent reporting and browsing (as I like to say, such components add a degree of "managed authoring..."). The provision of the measure as a drag-and-drop object frees the intended users from writing calculations, and allows them to focus on mission critical analysis.

Considerations and Comments

For purposes of this exercise, we will create a calculated measure to house the MDX that returns a moving average. We will be working within the Warehouse cube in the FoodMart 2000 sample database, which accompanies the installation of MSAS, along with several other samples. If we want to keep our sample cube in its pristine, original condition, we can simply discard our calculated measure upon the conclusion of our session, or at any convenient time thereafter.

While the calculated measure creation process is simple enough to follow, ensure that you have the authority, access and privileges needed to accomplish the process, and that performing these operations within the FoodMart 2000 database presents no other issues in your environment.

If the sample MSAS database was not installed, or was removed prior to your beginning this article, please see the MSAS documentation, including the Books Online, for the straightforward procedure to restore the database from the archive (.cab) file containing the samples. As of this writing, a copy of the archive can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

Hands-On Procedure

Preparation

Before creating a calculated member in MSAS, be it for a dimension or a measure (as in our present case), I often assemble the MDX involved in a more "direct" environment. While I use many applications for this purpose, the one that works best from the perspective of my articles is the MDX Sample Application that installs along with MSAS, primarily because anyone with access to MSAS probably has access to this tool. The Sample Application is useful as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

We will craft the MDX for a rolling average here, so as to get a focused view of how it works, before installing the MDX into a calculated measure in Analysis Services. To do this, we will take the following steps:

Having received our instructions from the information consumers, we will first create the core MDX query. We will add the rolling average calculated measure into the core query next. The core query might be viewed as a "frame" within which we are casting the rolling average. We want to set that frame up in a way that we can quickly verify operation of our MDX, before we permanently "install" the calculated measure into the Warehouse cube structure.

The business requirement involves quarters (at least in the initial specification), across which the average of Warehouse Sales will move. In addition, the moving average needs to be displayed specifically for U.S. Store States (keeping in mind that we might additionally like to present the rolling average for other dimensions at a later point in time).

Let's construct the core query next.

-- MXAS18-1, Preparation for Creation of Rolling Average Calculated Measure

SELECT

CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales]}) 

      ON COLUMNS,

   {[Warehouse].[All Warehouses].[USA].Children} ON ROWS

FROM

   [Warehouse]

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 2 appears.

We see the four quarters of 1997 and 1998, respectively (the two years in the Warehouse cube), together with the Warehouse Sales measure, populating the columns across, and the U.S. Store States (from the Store dimension) appearing on the row axis. The core query thus provides the "frame" we need to ascertain that the calculated measure we create next 1) accumulates the base measure, Warehouse Sales, correctly, 2) over the window of the quarters.

We will use the Avg() function, in combination with the PeriodsToDate() function, in the definition of the rolling average calculated measure. (For details surrounding the PeriodsToDate() function, see my articles MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions and MDX in Analysis Services: Mastering Time: Period - to - Date Aggregations).

We take the following steps to create the calculated member, and place it within the results dataset "frame" we have prepared.

-- MXAS18-2 Rolling Average Calculated Measure Create and Retrieve

WITH

MEMBER

[Measures].[Rolling Avg] 

AS

'Avg (LastPeriods (4, [Time].CurrentMember), [Measures].[Warehouse Sales])'

CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales]}) 

      ON COLUMNS,

	CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales], 

		[Measures].[Rolling Avg]}) ON COLUMNS,

The Query pane appears as shown in Illustration 3, with our modifications circled.

The Results pane is populated, and the dataset depicted in Illustration 4 appears.

As we can see, the calculated measure appears to be producing the desired results. Through the first Q4 (that is, for 1997), we see that it properly divides the total of the Warehouse Sales values by the number of quarters added together. (There are no quarters in the Warehouse cube prior to Q1 of 1997, so the rolling average cannot "look back" to earlier quarters, and thus cannot divide by a full four quarters until it reaches Q4 of 1997). Taking the California (CA) stores as an example, we can see, however, that, in moving to Q1 of 1998, the total Warehouse Sales of Q2, Q3, and Q4 of 1997 (14,734.32, 19,768.15, and 15,076.37, respectively), taken together with the Warehouse Sales of Q1 of 1998 (9,670.46), divided by four (4), gives us the correct value for the average of four rolling quarters at Q1 of 1998 (14,812.33).

The beauty of the way that our calculated measure retrieves the rolling average, based upon the "current" point in time, is that we can use such a calculated measure in a report, within which we design a parameter / prompt (or other mechanism) to supply the "as of" date. Information consumers can thereby reuse the calculated measure prospectively without having to code the query, or, for that matter, even know how to do so. We can give the calculated measure an intuitive name, and add it to the report authors' toolkits as another component that they can leverage with drag and drop ease.

Let's go into Analysis Manager, where we will add the calculated measure permanently to the cube, so that it can be accessed by any reporting application with connectivity to the cube (and, of course, the capability to "see" calculated members).

Procedure: Building the Rolling Average Calculated Measure in Analysis Services

Procedure

The Cube Editor opens.

The Calculated Member Builder opens.

Rolling Average . 4 Pd

While the name of the calculated member can obviously be anything that is useful in the environment in which we are creating it, we need to make the name intuitive to the users. We used the term "4 Pd" versus "4 Qtr" (or similar) for reasons we will discuss shortly.

Avg (LastPeriods (4, [Time].CurrentMember), 

  [Measures].[Warehouse Sales])

The MDX expression above is identical to that which we assembled in the Sample Application, where we constructed and tested the MDX to meet the business requirement. We simply take the expression in the WITH MEMBER section and remove the single quotes surrounding it. We use a different name for the calculated member than we did in the Sample Application, because we want to differentiate it from additional members we will create in subsequent articles, which will also be designed to return rolling averages.

The Calculated Member Builder appears with our input as depicted in Illustration 7.

A message box appears, as shown in Illustration 8, informing us that the syntax is acceptable.

The new Calculated Measure appears in the tree within the Calculated Members folder, as depicted in Illustration 9.

While no other calculated members appear in the illustration above, this view may differ from your own, depending upon differing activities that have been conducted with the Warehouse cube in your own environment. As another matter, although the actions we have performed to this point alone do not require it, let's process the cube to make sure we are all in a similar "processed" state.

The Process a Cube dialog appears, as depicted in Illustration 12, where we want the processing method set to Full Process. Full processing for the Warehouse cube will be relatively quick, so we will perform it to ensure that all is refreshed.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 13.

Cube data is retrieved.

Verification and Use

With data appearing in the Data view, we will take this opportunity to verify the proper operation of our new calculated measure, before handing it over with instructions for use to the intended audience.

The Data view, with our arrangements, appears as depicted in Illustration 14.

We are able to see the values that appear for all measures (my illustration above is compressed, to focus on Warehouse Sales and the new Rolling Average - 4 Pd, while conserving space). We see that the Rolling Average - 4 value for the California stores is 14,812.33 (circled in Illustration 14 above), the same value that we saw, and verified, in our development of the measure's MDX in the Sample Application earlier.

We note, as well that we gain rollup capabilities, something we might find useful as requirements for the new rolling average functionality grow. Let's look at another consideration, to which we alluded as we were naming our new calculated measure.

The final arrangement should appear as partially depicted in Illustration 16.

Through a verification process similar to the one we applied to the Quarter level values of the Rolling Average - 4 Pd calculated measure earlier, we can verify that the calculation is working at the month level, as well. This is why we chose to insert "4 Pd" in the name of the calculated measure, instead of "4 Qtr," or any other such restrictive designator. We will find that the rolling average works in a manner that is "scope sensitive" based upon its placement.

For instance, if we add the Warehouse Sales values (circled in red in Illustration 17 above) for the months of 3, 4, 5, and 6 of 1998, (2,477.67, 2,449.35, 4,602.99, and 5,637.52, respectively), we obtain a total of 15,167.53. Divided by four (4), this delivers an average of 3,791.88, the value that the Rolling Average - 4 Pd calculated measure displays (I have highlighted it with a red rectangle in Illustration 17 above) for month 6 of 1998.

While there are ways to enforce use of the moving average we have created at one specific level of the time dimension, we will leave the calculated measure as it is; once we explain its use to the information consumers, from whom we obtained the more limited, initial business requirement, we can be confident that the added value our solution provides cannot help but meet with their approval.

Conclusion

In this article, we introduced "rolling averages," a relatively common business requirement. We discussed the nature of these aggregations and the results they are intended to provide, and then discussed a business requirement, within a hypothetical scenario, in which a group of information consumers have requested a particular moving average capability for analysis purposes.

Using the Sample Application that accompanies an installation of MSAS, we constructed the MDX required to support a calculated member, upon which the consumers might rely to produce the rolling average values for which they expressed a need. Having tested the capabilities of the MDX, we created a calculated member in Analysis Manager to provide a permanent means by which the intended audience might retrieve rolling averages, in reports and browses of the cube, and other queries. We verified accuracy of operation once again, from the data view within the cube, demonstrating that the rolling average calculated measure operates in a "contextually sensitive" way, from the perspective of the time dimension levels at which it is used.

In our next article, we will continue our examination of moving averages, and examine an additional approach to achieving the same result.

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