Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 19, 2002

Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions

By William Pearson

About the Series

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.


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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM