Verification and Use
With data appearing in the Data view, we will take this opportunity to verify the proper operation of our new calculated member, before handing it over with instructions for use to the intended audience.
1. Position Measures as the column axis, if necessary.
2. Position the Warehouse dimension as the row axis.
3. Drill down to display the U.S. Warehouse States.
The Data view, with our arrangements, appears as shown in Illustration 12.
We are able to see the values that appear for all measures, with the Warehouse Sales measure, and the YTD Warehouse Sales calculated member / measure circled.
4. Drag the Time dimension to the immediate right of the newly placed Warehouse dimension columns, to effect a "crossjoin." When the cursor appears as depicted in Illustration 13, drop the Time dimension.
Illustration 13: Cursor Indicates Appropriate Drop Point
The final arrangement should appear as partially shown in Illustration 14.
Illustration 14: Arrangement in the Data View - Partial Row Axis
5. Double-click the Year column heading to drill down to the member quarters of years 1997 and 1998.
6. Double-click the Quarter column heading to drill down to the member months of each.
7. Scroll to focus upon the Warehouse Sales measure and the new YTD Warehouse Sales calculated measure, juxtaposed against California warehouses (CA) and 1998 in the row axis, as depicted in Illustration 15.
NOTE: I have removed the measures columns, between the Warehouse Sales measure and the YTD Warehouse Sales calculated measure, to display the measures side-by-side in a more compact presentation.
Illustration 15: Arrangement in the Data View - Partial, Composite View
We can readily see that our year-to-date calculation appears to be effective. The YTD Warehouse Sales value is not only incremented for the Warehouse Sales each month, but the same effect is obtained at rollup levels within the Time dimension.
8. Right-click the Quarter column heading, once more.
9. Select Drill Up from the context menu that appears, as shown in Illustration 16.
Illustration 16: Drilling Up to the Quarter Level
The display contracts, hiding the member months. We can see, at the Quarter level, too, that the YTD Warehouse Sales value is incremented for the Warehouse Sales each quarter, just as it was for each month, as (compactly) depicted in Illustration 17.
Illustration 17: Arrangement in the Data View - Partial, Composite View
Thus, we verify that the YTD Warehouse Sales calculated member performs as intended. We can use the calculated member in a report or through any other mechanism for querying the cube, to generate a year-to-date total, at a given point in time, for the Warehouse Sales measure.
10. Select File --> Exit to close the Cube Editor, saving as requested.
11. Exit Analysis Services, as desired.
In our next article, we will evolve our examination of relative time periods a step further, and expose an approach to supply the same capabilities, except within a structure that allows us to juxtapose the calculation with any appropriate measure in our cube. This will contrast with the method we have examined in this article, where we presented a solution that would imply the creation of a "to date" calculated member for each measure, as well as for the "to -date" period (month, quarter, year, etc.) under examination. While our next approach will present slightly more complication in setup, it will afford us relative time capabilities that more closely resemble those that are created as a byproduct, as we have mentioned, of using the Date wizard in Cognos PowerPlay Transformer.
In this article, we introduced relative time periods, discussing their general importance in analysis and reporting. We described how dominant enterprise business intelligence vendor Cognos has provided easy-to-use relative time structures within the Cognos PowerPlay Transformer application for reporting in Cognos PowerPlay. Our primary focus, in response to a request that I receive on a recurring basis, was to address options afforded within Analysis Services for replicating similar capabilities.
In this article, we began with a straightforward approach to meeting the requirement for relative time periods, highlighting differences in operation inherent in the use of a calculated member to achieve the capabilities offered in Cognos PowerPlay Transformer. We then performed a practice exercise whereby we added an example relative time structure using this straightforward method. As a part of constructing a Year-to-Date calculated member for a given measure within our sample cube, we discussed the manner in which the combined PeriodsToDate() and Sum() MDX functions could be used to support our relative time period.
We verified the adequacy of our solution through the use of the Cube Browser in the Analysis Services Cube Editor, discussing the use of the new calculated member in browses of the cube, reports and other queries. We demonstrated that the calculated member operates in a "contextually sensitive" way, from the perspective of the time dimension levels / other dimensional intersects at which it is employed, leveraging the power of OLAP beyond the capabilities of a simple fixed calculation.
Finally, we looked forward to a more sophisticated solution we will propose and build in Part II, to more closely replicate the functionality in Cognos PowerPlay Transformer and to provide a solution even more user friendly for reporting specialists and information consumers.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.