MDX in Analysis Services: Mastering Time: Introduction to Moving Averages - Page 5
August 23, 2004
10. Type the following into the Member name box:
Rolling Average . 4 Pd
While the name of the calculated member can obviously be anything that is useful in the environment in which we are creating it, we need to make the name intuitive to the users. We used the term "4 Pd" versus "4 Qtr" (or similar) for reasons we will discuss shortly.
11. Type the following MDX into the Value Expression section of the Calculated Member Builder:
Avg (LastPeriods (4, [Time].CurrentMember), [Measures].[Warehouse Sales])
The MDX expression above is identical to that which we assembled in the Sample Application, where we constructed and tested the MDX to meet the business requirement. We simply take the expression in the WITH MEMBER section and remove the single quotes surrounding it. We use a different name for the calculated member than we did in the Sample Application, because we want to differentiate it from additional members we will create in subsequent articles, which will also be designed to return rolling averages.
The Calculated Member Builder appears with our input as depicted in Illustration 7.
12. Click the Check button to perform a syntax check.
A message box appears, as shown in Illustration 8, informing us that the syntax is acceptable.
13. Click OK to close the Calculated Member Builder, and to save our new calculated measure.
The new Calculated Measure appears in the tree within the Calculated Members folder, as depicted in Illustration 9.
While no other calculated members appear in the illustration above, this view may differ from your own, depending upon differing activities that have been conducted with the Warehouse cube in your own environment. As another matter, although the actions we have performed to this point alone do not require it, let's process the cube to make sure we are all in a similar "processed" state.
14. Select Tools --> Process Cube to process the Sales cube.
15. Click Yes on the Save the Cube dialog that appears next, as shown in Illustration 8.
16. Click No on the dialog that appears next, as shown in Illustration 11; we will not design aggregations at present.
The Process a Cube dialog appears, as depicted in Illustration 12, where we want the processing method set to Full Process. Full processing for the Warehouse cube will be relatively quick, so we will perform it to ensure that all is refreshed.
17. Ensure that the Full Process radio button is selected on the Process a Cube dialog.
18. Click OK to begin processing.
Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 13.
19. Click Close to dismiss the viewer.
20. Click the Data tab in the Cube Editor, if necessary.
Cube data is retrieved.