MDX in Analysis Services: Mastering Time: Introduction to Moving Averages - Page 2
August 23, 2004
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.