MDX in Analysis Services: Mastering Time: Introduction to Moving Averages - Page 4
August 23, 2004
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
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.
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.
The Calculated Member Builder opens.