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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 20, 2002

Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions - Page 2

By William Pearson

Introducing Time Dimensions

While I have encountered numerous instances of OLAP cubes that do not require a time dimension over my years of Business Intelligence consulting, I tend to agree with most experts and business users alike that the time dimension is a pervasive fixture in the typical OLAP database. As much of OLAP reporting surrounds financial data, the element of time is consistently relevant in a big way, with a wide range of levels of detail occurring among different business environments. While it is not unheard of to find the occasional activity-based OLAP cube, for example, that completely ignores time as a factor, this is certainly the exception, and not the common rule. And while a lowest level of months, with groupings of quarters and years, seems to be the standard design for corporate financial time dimensions, I have come across situations (for example a cube that reports on turbine operating metrics for a large machinery manufacturing organization) where the lowest levels of detail stored for reporting purposes are captured in minutes, and even seconds.

Because time measurement follows relatively common patterns and parameters, the wizard-guided creation of the time dimension can often suffice as adequate in most material respects. And even when variations exist within the desired presentation of the data in its end reporting incarnations, we can often simply modify the work of the wizard to customize the time dimension to fit the needs of organizational Information Consumers with only a small amount of manipulation. Later in our lesson, we will discuss some of the situations where customization might enhance the "off the shelf" time dimension, and discuss options that Analysis Services provides us in making those alterations.

Where Do You Find the Time...?

Over my consulting career, I have seen the time/date data required to populate a cube's time dimension taken from one of two main sources in the data warehouse: the fact table or a separate time dimension table. While this is done in various ways, most of which are dependent upon schema design and many of which are beyond my influence, (as they exist upon my arrival), there are many reasons for selecting a separate time dimension table, given the choice. The most compelling of these reasons are the advantages that are imparted in terms of space savings and processing efficiency when a separate dimension table, joined by integer keys to the fact table, provides the date/time data to populate the date dimension. Other factors that favor a separate dimension table, such as the capability to store other-than-typical information about the assorted levels and members (such as seasonal and other groupings that cannot be readily derived from a single date/time field alone by the dimension wizard), as well as the capacity of the dimension table to be shared among multiple fact tables, add fuel to the argument for a separate table.

However, as it is often seems to be the case that we "inherit" the designs of others (the designer(s) are often long gone by the time I arrive on the site -- or at least no one will admit having had the final say in the design of the warehouse), we are fortunate to be able to derive time dimension hierarchies from a wide variety of sources, particularly from variations of the two main sources I mentioned above. In our example, we will work with time_by_day, a separate dimension table, in the FoodMart 2000 database that is deployed as a sample within a Typical installation of Analysis Services, to explore the creation of both a calendar and a fiscal time dimension as a central part of our lesson.

Page 3: Preparation for Creating a Dimension from the FoodMart 2000 Database

MS SQL Archives

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