MDX in Analysis Services: Mastering Time: Moving Averages - Another Approach

Monday Sep 27th 2004 by William Pearson
Share:

MSAS Architect Bill Pearson examines another approach for handling moving averages within Analysis Services. In this article, we focus on the hands-on construction and testing of the MDX involved in our alternative approach to generating 'rolling' 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 return to the subject of the previous article in the series, Mastering Time: Introduction to Moving Averages, and examine another approach for achieving "rolling average" aggregations. As we learned in our last article, "rolling," or "moving," averages, form a common business requirement, and involve a the aggregation of an average over a progressively moving window of time periods. While the functions that define the "window" involve the Time dimension in the vast majority of cases, we should be aware that we can apply these functions to members of other dimensional levels, as well.

We stated, in the previous session, that rolling averages are popular in the business community because they have the effect of smoothing values that fluctuate over time; we gave examples of useful applications of moving averages (such as cases where seasonal variations and other volatility factors come into play for a given measure), and then discussed some of their uses. We noted that the assistance moving averages offer in spotting trends (especially helpful in volatile markets, but in many other environments, as well), make moving averages a popular tool among technical analysts, as well as a commonly used contributor to many other technical indicators and overlays.

We broke down the concept of a rolling average, and explained that it is generated simply by calculating the average of the current value, together with the specified number of previous values, typically identified by their placement at time intersects. We noted that MDX affords multiple approaches to generating rolling averages, and we explored one of these through a multi-step practice example.

In this article, we will present a different approach, but will attempt to activate the underlying concept in a similar set of procedural steps. With each step, we will discuss our objectives, as well as the results we obtain. We will:

  • Discuss considerations applicable to our alternative approach, commenting upon the general steps of the practice example;
  • Examine the hypothetical business requirement from our last article, in which a group of information consumers have requested a particular moving average capability for analysis purposes;
  • Construct and test, within the Sample Application, the MDX required to support a rolling average calculated member;
  • Refer to the relevant section in our previous article for guidance in creating a calculated member in Analysis Manager, to provide a basis for verifying accuracy of operation, as well as for "permanent" structural rolling average support in the cube.

Another Approach to Moving Averages

Objective and Business Scenario

Similar to the path we took in our last article, Introduction to Moving Averages, we will perform the steps required to derive the MDX needed to support a calculated measure that generates a simple moving average within a sample cube. Our objective, just as before, is to provide for cube-level support of an organizational analysis requirement for the calculated measure. With the same simple structural addition to MSAS that we practiced in our last session, we can enable the use of the rolling average capability in any OLAP reporting solution that is capable of accessing an MSAS cube and its calculated measures.

Once we add the calculated measure to our cube, the uses to which we put the new moving average calculated measure are limited only by our knowledge of the reporting tools we use, and the manner with which these tools interact with MSAS. As we mentioned in our last session, examples of these uses might include the construction of a report that presents a rolling average of a key performance measure within our organization, online analysis of a rolling average of a critical measure (with variable time granularity and range parameters as a further nuance), and the support of numerous key performance indicators that exist in various organizational responsibility centers. (The reporting side of these concepts will be developed in articles within my Reporting Services series at Database Journal.)

For purposes of our practice procedure, we will assume, once again, 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. We will assume a business requirement that is identical to that of our last article, to enable us to compare the alternative solution that we develop in this article to that of our last.

To restate the requirement, the information consumers 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, again, appreciate somewhat the value of an 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"), and state that such versatility will be "nice to have."

As we noted in the previous article, we discussed the business requirements with the consumers, and confirmed our understanding of their need as follows: the capability to 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 immediately previous 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 - thus the window effectively "rolls" up a quarter in response to the movement of the "current" quarter.

Having recognized, in this scenario just as in the last, that such a rolling average might be valuable for other measures, as well as for other consumers in the organization, we consider making the investment in creating other business intelligence capabilities based upon the calculated measure, which we will assemble in this article. For example, we see that the calculated member can be leveraged with further structural additions in MSAS to support parameter prompts support for the rolling average that we can carry 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 will expose approaches to creating these broader capabilities in articles in our Reporting Services (and other) series. Regardless of the end applications to which it is made available, a calculated measure that returns a 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

Our objective in this article is to create a calculated measure to house the MDX that returns a moving average. The MDX we use will offer an alternative approach to the MDX we offered in our last article to accomplish the same outcome. We will again be working within the FoodMart 2000 sample database, specifically with the Warehouse sample cube, one of the samples that accompany the installation of MSAS. 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.

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

We will again assume, for purposes of accomplishing the steps of the practice exercises, 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.

Hands-On Procedure

Preparation

Before creating our calculated member in MSAS (in our case, it will be for a measure, thus my reference to "calculated measure" throughout the article), we will again assemble the MDX involved using the MDX Sample Application that installs along with MSAS. While it has its limitations, the Sample Application is a good choice for straightforward MDX construction in the context of procedural articles; anyone with access to MSAS probably has access to this tool. The Sample Application will provide us with a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

We will assemble the MDX for a rolling average here, to get a focused view of how it works, before pronouncing the MDX adequate for use in constructing an alternate rolling averages 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, just as we did in our previous session. To this core, we will add the rolling average calculated measure next. Once again, the core query might be viewed as a "frame" within which we are casting the rolling average. Using such a frame, we can quickly verify operation of our MDX, before we consider permanently installing 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. As always, when we receive a specification to construct any such calculated member, we want to keep in mind flexibility and portability. (For example, in this case, we can certainly envision the usefulness of being able to present the rolling average for other dimensions at some other point in time).

Let's construct the core query next.

5.  Type the following query into the Query pane:


-- MXAS19-2, Preparation for Creation of Rolling Average Calculated Measure (Alternate 2)
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 1 appears.


Illustration 1: 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. This query is identical to the core query we constructed in Introduction to Moving Averages. It 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 MXAS19-1, and place it in a meaningful location.

8.  Leave the query open for the next section.

At this stage, while our ultimate objective is to return the same end results dataset, our query will diverge from its form in our earlier examination of rolling averages. We will use the Avg() function once again, within a WITH clause that defines a calculated member, to generate an identical moving average, but, instead of using Avg() in combination with the LastPeriods() function, as we did in our last article, we will use the .Lag() function, within a ranged time window, for definition of the rolling average calculated measure. The .Lag() function returns a "peer" member (a member existing along the same dimension of the member specified in the function) that exists a specified number of positions prior to the specified member. Positions are determined by the natural order of the dimension, and, as is the case of many MDX functions of this variety, the position numbering system is zero-based (meaning position zero is the position of the specified member itself). For details surrounding the .Lag() function, keep an eye out for a subsequent article on the function in my Database Journal MDX Essentials series.

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 MXAS19-1, replace the top comment line of the query with the following:

-- MXAS19-2 Rolling Average Calculated Measure (Alternate 2) Create and Retrieve

10.  Save the query as MXAS19-2, to prevent damaging MXAS19-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 2] 
AS
'Avg ( { [Time].CurrentMember.Lag(3) : [Time].CurrentMember },
    [Measures].[Warehouse Sales])'

12.  Append the calculated measure created in the WITH statement above, [Measures].[Rolling Avg 2], 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 2]}) ON COLUMNS,

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


Illustration 2: 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 partially depicted in Illustration 3 appears (the image has been trimmed due to size).


Illustration 3: Result Dataset - The Rolling Average Appears (Partial View of Dataset)

As we can see, the calculated measure appears to be producing the desired results - results identical to those generated by the rolling average measure we constructed in our last session, as we intended. Just as was the case with our first approach to the rolling average, we see that it properly divides the total of the Warehouse Sales values by the number of quarters added together, through the first Q4 (that is, for 1997). 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, once again, we can see 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).

We have established that he results dataset with the alternate approach appears to be identical. Let's examine the difference in the way we deliver those results. We use the Avg() function once again, but, unlike our first rolling average, where we specified the "last four months" using the LastPeriods() function, as shown below:

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

we have specified the "last four months" through the use of a specified time range, constructed with a combination of the .CurrentMember and Lag() functions, together with the colon (:) operator, as shown below:

'Avg ( { [Time].CurrentMember.Lag(3) : [Time].CurrentMember }, [Measures].[Warehouse Sales])'

Specifically, we are asking that Rolling Average 2 be calculated as the Warehouse Sales within a rolling time window of "the current month and the immediately lagging three months" (for a window range spanning a total of four months). Lag() allows us to specify a "negative offset" from the current member, and serves us well in the creation of moving time windows such as this.

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

We mentioned, at this stage in our previous article, that one of the advantages within 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. This supports the recurring reuse of the calculated measure by information consumers in a manner that bypasses any coding on their part, or even knowledge of how the rolling average works. We can give the calculated measure an intuitive name, and add it to the report authors' toolkits as another component that they can drag and drop into place anytime they need a moving average within a report.

At this point, we can go into Analysis Manager, as we did in our last article, and add the calculated measure permanently to the cube. This way, it can be accessed by any reporting application with connectivity to the cube (and, of course, the capability to "see" calculated members). For the step-by-step procedure, see the section titled Procedure: Building the Rolling Average Calculated Measure in Analysis Services in Mastering Time: Introduction to Moving Averages here at Database Journal. Performing these steps with the current MDX will again confirm accuracy of operation, from the data view within the cube. In addition, creation of the calculated member within the cube structure, where easy browses of the data can be performed with the measure in place, will demonstrate, just as it did with our previous approach, 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.

Conclusion

In this article, we continued our examination of "rolling averages," which are a popular capability in business intelligence and other analysis spheres. Having discussed the nature of these aggregations and the results they are intended to provide in depth in our previous article, we set out to replicate the satisfaction of an identical business requirement, using an alternative approach from the perspective of the MDX behind the moving averages calculated member. To this end, we began with a rehash of the original hypothetical business requirement, in which a group of information consumers have requested a particular moving average capability for analysis purposes.

We then used the Sample Application, which accompanies an installation of MSAS, to construct the MDX required to support a calculated member that fills the business requirement in a manner identical to our initial rolling average. We focused on the differences in this alternative approach, all of which lay inside the Avg() function, within the definition of the calculated member. Finally, we directed the reader to the last section of our previous article for the steps required to create the calculated member in Analysis Manager. Creation of the calculated member in Analysis Services provides developers and consumers alike an opportunity to examine the interaction of the calculated member with the data of the cube within which it resides. Ultimately, it provides a permanent means by which intended audiences might retrieve rolling averages, in reports, queries, and general browses of the cube.

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

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