Time Dimension Options in SQL Server 2005 Analysis Services

In my previous post, I covered the new dimension wizard and mentioned there were options for creating time dimensions that I would cover later. Now I'll explain those options further.

Let’s start with a quick review of what happens in SQL Server 2005 (referred to as Yukon hereafter). On the Select the Dimension Type page of the dimension wizard, you can choose Standard, Time Dimension, and Server Time Dimension.

If you select Time Dimension, you identify the time table in your DSV and then you map your time columns to the Analysis Services time properties. For example, you map a CalendarYear column in your time table to the Year property. This association of a table column to a property helps your MDX queries how to handle time-related functions like YTD or PeriodToDate.  I admit I find this mapping process tedious, but necessary. When you use a time dimension table, you have to manage the processing of the Analysis Services dimension to add new time members if you incrementally add members to the table (instead of populating it well into the future as some people prefer to do).  The benefit of this approach is the ability to include time attributes that mean something to your industry, such as a flag for a holiday or weekend versus weekday. You also can confirm inclusion of hierarchies based on the columns you map to time properties. I never liked the inability to change the hierarchy names here, but that’s just a nit. You can, of course, add your own hierarchy or modify the hierarchy name later in the dimension editor.

Read more...