A Different Approach to Implementing Time Calculations in SSAS David Shroyer OLAP Business Solutions April 2007 (Revised) Applies to: Microsoft SQL Server 2005 Analysis Services SP2 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
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.
The Business Problem
The fictitious company used in this example is called Sub Palace. Sub Palace is a small retail sandwich company headquartered in Northern California. The company has small retail outlets which have a limited sandwich and soda menu. The company has had some growth in the past few years and now has 10 retail outlets. The owner, Mr. Hanson, is trying to get a handle on his sales performance for each of his outlets. A “development” SSAS cube was built which contains sales and profitability data for his ten stores. The cube has the following dimensions: • • • • • Products Promotions Stores Time Measures www.obs3.com Page 1 of 22
OLAP Business Solutions
The requirements for the cube include providing users the ability to track the various cube measures by year over year performance and year to date performance. Included in the Timecalculations.zip file is a back up copy of the Sub Palace Data Mart. If you want to follow along with the examples in the paper, you will need to restore this SQL Server 2005 database. There are also three SSAS projects located in the .zip file.
SubPalaceBase – This is the original “starting point” cube. If you want to follow along with the examples, this is the cube that you will want to use. SubPalaceWizard – This is the completed cube which uses the built-in time intelligence wizard. (First portion of the paper) SubPalaceManual – This is the completed cube which contains a manually created time dimension. (Solution suggested)
The Built-In Time Intelligence Wizard
The development SSAS database is set up with two user defined time hierarchies. There is a “calendar” time hierarchy and “fiscal” time hierarchy. Like most other business, SubPalace wants to track performance by both calendar and fiscal time periods. The time dimension was structured so that the fiscal and calendar attribute dimensions are segmented into two separate folders. This makes it a little less confusing for users. They can be instructed to use either the “calendar hierarchy” or the “fiscal hierarchy”. This will insure that users do not accidentally use a fiscal attribute dimension in tandem with a calendar attribute dimension.
This screen shot illustrates how a user interacts with the dimensions in a front end tool such as ProClarity. Note: To segment the attribute dimensions and hierarchies into...
Please join StudyMode to read the full document