Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions

This is the third article of the series, Introduction to Microsoft SQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series will be an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features designed to meet specific real-world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article Creating Our First Cube.

Introduction

In the first article of the series, we used the Cube Wizard to build an initial cube with the assistance of the Dimension Wizard. Included in the dimensions we built through this wizard-guided process was a calendar time dimension. In this article we will recreate the calendar time dimension, this time focusing on the process through which the Dimension wizard converts existing time/date fields to a time dimension, along with its hierarchy of levels and members.

Next, we will expose ways to customize the predefined, time-related properties that the wizard establishes in building the time dimension, suggesting options for customization of these properties to enhance the cube, from the dual perspectives of user-friendliness and the reporting needs of the organization. We will create an example of an alternate time dimension for fiscal time reporting, and then we will discuss some of the considerations surrounding the simultaneous housing of both hierarchies in the same OLAP cube structure.

In this article, we will:

• Discuss briefly the sources of time dimension data;
• Recreate the calendar time dimension that we built "automatically" in Lesson One;
• Examine the setpoints of the time--related properties that the Dimension Wizard generates as part of the dimension build process;
• Explore the expressions that the Dimension Wizard puts into place to create hierarchical levels from a date / time column;
• Modify the dimension properties to facilitate ease of use by Information Consumers;
• Customize the time dimension hierarchy to fit the business environment;
• Create a fiscal year time hierarchy;
• Explore considerations with regard to handling multiple time hierarchies in the same cube.

Page 2: Introducing Time Dimensions

See All Articles by Columnist William E. Pearson, III