MDX in Analysis Services: Mastering Time: Introduction to Moving Averages - Page 6
August 23, 2004
Verification and Use
With data appearing in the Data view, we will take this opportunity to verify the proper operation of our new calculated measure, before handing it over with instructions for use to the intended audience.
21. Position the Measures as the column axis.
22. Position the Store dimension as the row axis.
23. Drill down to display the U.S. Store States.
24. Select 1998 - Q1 in the selector to the right of the Time dimension, in the upper half of the Data view.
The Data view, with our arrangements, appears as depicted in Illustration 14.
Click for larger image
We are able to see the values that appear for all measures (my illustration above is compressed, to focus on Warehouse Sales and the new Rolling Average - 4 Pd, while conserving space). We see that the Rolling Average - 4 value for the California stores is 14,812.33 (circled in Illustration 14 above), the same value that we saw, and verified, in our development of the measure's MDX in the Sample Application earlier.
We note, as well that we gain rollup capabilities, something we might find useful as requirements for the new rolling average functionality grow. Let's look at another consideration, to which we alluded as we were naming our new calculated measure.
25. Drag the Time dimension to the immediate right of the newly placed Store dimension columns, to effect a "crossjoin." When the cursor appears as shown in Illustration 15, drop the Time dimension.
The final arrangement should appear as partially depicted in Illustration 16.
26. Double-click the Year column heading to drill down to the member quarters of years 1997 and 1998.
27. Double-click the Quarter column heading to drill down to the member months of each
28. Scroll to focus upon the Warehouse Sales measure and the new Rolling Average - 4 Pd calculated measure, juxtaposed against California stores (CA) and 1998 in the row axis, as shown in Illustration 17.
NOTE: I have removed the measures columns, between the Warehouse Sales measure and the Rolling Average - 4 Pd calculated measure, to display the measures side-by-side in a more compact illustration.
Through a verification process similar to the one we applied to the Quarter level values of the Rolling Average - 4 Pd calculated measure earlier, we can verify that the calculation is working at the month level, as well. This is why we chose to insert "4 Pd" in the name of the calculated measure, instead of "4 Qtr," or any other such restrictive designator. We will find that the rolling average works in a manner that is "scope sensitive" based upon its placement.
For instance, if we add the Warehouse Sales values (circled in red in Illustration 17 above) for the months of 3, 4, 5, and 6 of 1998, (2,477.67, 2,449.35, 4,602.99, and 5,637.52, respectively), we obtain a total of 15,167.53. Divided by four (4), this delivers an average of 3,791.88, the value that the Rolling Average - 4 Pd calculated measure displays (I have highlighted it with a red rectangle in Illustration 17 above) for month 6 of 1998.
While there are ways to enforce use of the moving average we have created at one specific level of the time dimension, we will leave the calculated measure as it is; once we explain its use to the information consumers, from whom we obtained the more limited, initial business requirement, we can be confident that the added value our solution provides cannot help but meet with their approval.
29. Select File --> Exit to close the Cube Editor, saving as requested, if desired.
30. Exit Analysis Services, as desired.
In this article, we introduced "rolling averages," a relatively common business requirement. We discussed the nature of these aggregations and the results they are intended to provide, and then discussed a business requirement, within a hypothetical scenario, in which a group of information consumers have requested a particular moving average capability for analysis purposes.
Using the Sample Application that accompanies an installation of MSAS, we constructed the MDX required to support a calculated member, upon which the consumers might rely to produce the rolling average values for which they expressed a need. Having tested the capabilities of the MDX, we created a calculated member in Analysis Manager to provide a permanent means by which the intended audience might retrieve rolling averages, in reports and browses of the cube, and other queries. We verified accuracy of operation once again, from the data view within the cube, demonstrating that the rolling average calculated measure operates in a "contextually sensitive" way, from the perspective of the time dimension levels at which it is used.
In our next article, we will continue our examination of moving averages, and examine an additional approach to achieving the same result.