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.
» 
Comment and Contribute
