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