MDX in Analysis Services: Mastering Time: Introduction to Moving Averages

Monday Aug 23rd 2004 by William Pearson
Share:

Provide support for rolling averages for information consumers within Analysis Services. Join author Bill Pearson in the hands-on construction and testing of the MDX involved, and the creation of a calculated member to generate moving averages.

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.

Note: At the time of writing, Service Pack 3 / 3a updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, with respect to any MS Office components presented in a given article.

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.


Illustration 1: S&P 500 Annual Yield 12 Month Rolling Average %, 1947 to Present

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:

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

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.

5.  Type the following query into the Query pane:


-- 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]

6.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 2: Core Query Populates the Results Pane

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.

7.  Select File -> Save As, name the file MXAS18-1, and place it in a meaningful location.

8.  Leave the query open for the next section.

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.

9.  Within the query we have saved as MXAS18-1, replace the top comment line of the query with the following:

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

10.  Save the query as MXAS18-2, to prevent damaging MXAS18-1.

11.  Type the following into the Query pane, between the top comment line and the SELECT statement already in place:


WITH
MEMBER
[Measures].[Rolling Avg] 
AS
'Avg (LastPeriods (4, [Time].CurrentMember), [Measures].[Warehouse Sales])'

12.  Append the calculated measure created in the WITH statement above, [Measures].[Rolling Avg], to the first line of the SELECT statement, changing the following line:


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

to the following


	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.


Illustration 3: The Query with Our Modifications

13.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 4: Result Dataset - The Rolling Average 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).

14.  Re-save the query, and close the Sample Application, as desired.

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

1.  Open Analysis Manager.

2.  Expand the Analysis Server folder in the management console.

3.  Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder inside the FoodMart 2000 database.

6.  Right-click the Warehouse cube

7.  Select Edit ... from the context menu that appears, as depicted in Illustration 5.


Illustration 5: Select Edit from the Context Menu

The Cube Editor opens.

8.  Right-click the Calculated Members folder within Cube Editor.

9.  Select New Calculated Member ... from the context menu that appears, as depicted in Illustration 6.


Illustration 6: Select New Calculated Member from the Context Menu

The Calculated Member Builder opens.

10.  Type the following into the Member name box:

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.

11.  Type the following MDX into the Value Expression section of the Calculated Member Builder:

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.


Illustration 7: Calculated Member Builder with Complete MDX Expression (Compressed View)

12.  Click the Check button to perform a syntax check.

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


Illustration 8: Testing Positive for Syntax Correctness

13.  Click OK to close the Calculated Member Builder, and to save our new calculated measure.

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


Illustration 9: The New Calculated Measure in the Calculated Members Folder

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.

14.  Select Tools --> Process Cube to process the Sales cube.

15.  Click Yes on the Save the Cube dialog that appears next, as shown in Illustration 8.


Illustration 10: Click "Yes" to Save the Cube

16.  Click No on the dialog that appears next, as shown in Illustration 11; we will not design aggregations at present.


Illustration 11: Click "No" to Designing Aggregations

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.


Illustration 12: Full Process Selected in the Process a Cube Dialog

17.  Ensure that the Full Process radio button is selected on the Process a Cube dialog.

18.  Click OK to begin processing.

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


Illustration 13: Indication of Successful Processing Appears (Compact View)

19.  Click Close to dismiss the viewer.

20.  Click the Data tab in the Cube Editor, if necessary.

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.

21.  Position the Measures as the column axis.

22.  Position the Store dimension as the row axis.

23.  Drill down to display the U.S. Store States.

24.  Select 1998 - Q1 in the selector to the right of the Time dimension, in the upper half of the Data view.

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

Click for larger image

Illustration 14: The Data View with Specified Arrangements - Compressed View

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.

25.  Drag the Time dimension to the immediate right of the newly placed Store dimension columns, to effect a "crossjoin." When the cursor appears as shown in Illustration 15, drop the Time dimension.


Illustration 15: Cursor Indicates Appropriate Drop Point

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


Illustration 16: Arrangement in the Data View - Partial Row Axis

26.  Double-click the Year column heading to drill down to the member quarters of years 1997 and 1998.

27.  Double-click the Quarter column heading to drill down to the member months of each

28.  Scroll to focus upon the Warehouse Sales measure and the new Rolling Average - 4 Pd calculated measure, juxtaposed against California stores (CA) and 1998 in the row axis, as shown in Illustration 17.

NOTE: I have removed the measures columns, between the Warehouse Sales measure and the Rolling Average - 4 Pd calculated measure, to display the measures side-by-side in a more compact illustration.


Illustration 17: Arrangement in the Data View - Partial, Composite View

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.

29.  Select File --> Exit to close the Cube Editor, saving as requested, if desired.

30.  Exit Analysis Services, as desired.

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

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