dcsimg

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

June 13, 2005

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.

Conclusion

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.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers