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

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.