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.
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.
Illustration
15: Cursor Indicates Appropriate Drop Point
The final arrangement should appear as partially depicted in
Illustration 16.
Illustration
16: Arrangement in the Data View - Partial Row Axis
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.
Illustration
17: Arrangement in the Data View - Partial, Composite View
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.
Conclusion
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.
»
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute