A Different Approach to Implementing Time Calculations in SSAS (By David Shroyer)

Summary: Analysis Services 2005 now includes built-in time intelligence enhancements which make it quick and easy to add time calculations to OLAP cubes. This paper will discuss the problems associated with the built-in enhancements and suggest a different approach for implementing global time calculations. Included with this paper is a .zip file called TimeCalculations.zip which includes the sample data mart and example SSAS project files.

Contents

Introduction
The Business Problem
The Built-In Time Intelligence Wizard
Built-In Time Calculations Seen From a User Perspective
Advantages to Using Built-In Time Intelligence
Disadvantages to Using Built-In Time Intelligence
How Does the Time Calculation Dimension Work?
Creating a Shell Dimension
Adding the Time Calculation Dimension to the Cube
Adding the Time Calculations to the Cube
Verifying the Prior Year Calculation
Verifying the Year to Date Calculations
Adding a Different Type of User Defined Hierarchy
Adding the Attributes to the Dimension
Adding the Calculations
Conclusion

Introduction

AS2K5 offers developers a built-in wizard which will automatically add global time calculations to SSAS cubes. These built-in MDX scripts are very limited in their calculation scope and prove to be cumbersome for end-users to use. Fortunately it is very easy to create your own time calculation dimension which can then be customized to the application’s requirements.

Read more...