Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 13, 2005

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

By William Pearson

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.

Click for larger image

Illustration 12: The Data View with Specified Arrangements - Compressed View

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM