Sddfdsf
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
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.
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
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
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.
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