Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube - Page 4

June 13, 2005

The PeriodsToDate() component of our calculated member's expression, PeriodsToDate([Time].[Year]), is therefore specifying the set of all periods from the beginning of the year to the current period. This will allow the calculation to be applied at various points in time, and, based upon the current period, to supply the set of "all periods through that period."

The YTD Warehouse Sales measure is being defined, via the Sum() function, as the "sum of a numeric expression evaluated over a set." The syntax of the Sum() function is relatively straightforward, and can be represented as follows:

`Sum((<<Set>>[, <<Numeric Expression>>]))`

The Warehouse Sales measure inhabits the Numeric Expression portion of the Sum() function. The Set portion of the Sum() function consists of PeriodsToDate([Time].[Year]) within the syntax we have constructed inside the calculated member. Its effect is to ask for a set of members to be returned - a set of members that we can use in the Sum() function as a basis for accumulation. The end result is that we sum the Warehouse Sales from each of the periods, from the beginning of the Year to the current period (ergo Year-to-Date Warehouse Sales).

NOTE: For more information on the PeriodsToDate() function, see my article MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions in the MDX Essentials series at Database Journal. The article details "shorthand" versions of the PeriodsToDate() function, which include a "YTD" version.

The Calculated Member Builder appears with our input as depicted in Illustration 5.

Illustration 5: Calculated Member Builder with Complete MDX Expression (Compressed View)

12.  Click the Check button to perform a syntax check.

A message box appears, as shown in Illustration 6, informing us that the syntax is acceptable.

Illustration 6: Testing Positive for Syntax Correctness

13.  Click OK to close the Calculated Member Builder, and to save our new calculated member.

The new Calculated Member appears in the tree within the Calculated Members folder, as depicted in Illustration 7.

Illustration 7: The New Calculated Member in the Calculated Members Folder

NOTE: This view may not be the same as your own, depending upon differing activities that have been conducted with the Warehouse cube in your 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.

Illustration 8: Click "Yes" to Save the Cube

16.  Click No on the dialog that appears next, as depicted in Illustration 9; we will not design aggregations at present.

Illustration 9: Click "No" to Designing Aggregations

The Process a Cube dialog appears, as shown in Illustration 10, where we want the processing method set to Full Process. (It may be the only option, depending upon the status of the cube at this stage). Full processing for the Warehouse cube will be relatively quick, so we will perform it to ensure that all is refreshed.

Illustration 10: Full Process Selected in the Process a Cube Dialog

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 depicted in Illustration 11.

Illustration 11: Indication of Successful Processing Appears (Compact View)

19.  Click Close to dismiss the viewer.

20.  Click the Data tab in the Cube Editor, if necessary.

Cube data is retrieved.