Hilmar Buchta

SQL Server 2005 | SQL Server 2008

Building your time attribute relationship you have to consider the following questions:

  • How will I normally navigate the date hierarchies?
  • What formatting options do you usually use in your reports, eg. for the month: fullname (January, …), shortname (Jan, …), number (1, …)
  • What other information do you need (eg. holidays, day number of year)?

When building up your time dimension, you should

  • Set up proper hierarchies giving the commonly used navigation routes
  • Remove attribute hierarchies of attributes that are also appear as a level in a hierarchy
  • Use proper keys for your attributes and set up the attribute relationship accordingly
  • Use rigid instead of flexible relationship whenever possible (usually this is true for date attributes…)
  • Name your attributes using a defined naming schema. The attribute type property gives a good advice. For example you can use names like DayOfMonth (1 to 31), DayOfWeek (1 to 7), DayOfYear (1 to 365) etc. Don’t get confused with the many options available. Usually you will only need few of them. There is a good overview at http://technet.microsoft.com/en-us/library/ms175662.aspx.
  • Set the proper attribute type for each of your attributes
  • Set the dimension type property to "time" (important if you’re also using semiadditive measures as they require a time dimension)

 

Below is an example of a date dimension I recently used. Keep in mind though that this is just an example. You might not need an ISO week or quarters (I usually try to avoid them…) or you may even want to see semesters or incorporate a manufacturing calendar.

 

image

 

Attribute Key Name Sort by Type
Date DateID (eg. 20090711)     Date
Day Format DateID DayFormat (localized formatted date like 07/11) key Date
Day of Month DateID DayOfMonth (1 to 31) key DayOfMonth
Day of Year DayOfYear (computed) DayOfYear (1 to 365) key DayOfYear
Day Type Year
Month
DayOfMonth
DayType (eg. Holiday) key IsHoliday
Day Short Name Year
Month
DayOfMonth
DayShortName (e.g. Sa) key Days
Day Name Year
Month
DayOfMonth
DayName (eg. Saturday) key Days
Fiscal Month Of Year FiscalYear (eg. 2009)
MonthOfFiscalYear (1 to 12)
MonthOfYear (eg. 7) key FiscalMonthOfYear
Fiscal Month Short Name FiscalYear (eg. 2009)
MonthOfFiscalYear (1 to 12)
MonthShortName (eg. Jul) key FiscalMonthOfYear
Fiscal Month Name FiscalYear (eg. 2009)
MonthOfFiscalYear (1 to 12)
MonthName (eg. July) key FiscalMonths
Fiscal Quarter Name FiscalYear
QuarterOfFiscalYear
QuarterName (eg. Q2) key FiscalQuarters
Fiscal Year FiscalYear FiscalYear (eg. 2009) name FiscalYears
ISO Week ISOYear
ISOWeek (1 to 53)
ISOWeekName (29/09) key Iso8601WeekOfYear
ISO Year ISOYear ISOYear name Iso8601Years
Month Of Year Year
MonthOfYear (1 to 12)
MonthOfYear (1 to 12) key MonthOfYear
Month Short Name Year
MonthOfYear (1 to 12)
MonthShortName key Months
Month Name Year
MonthOfYear (1 to 12)
MonthName key Months
Quarter Name Year
QuarterOfYear (1 to 4)
QuarterName (eg. Q2) key Quarters
Week Of Year Year
WeekOfYear (1 to 52)
WeekOfYear (1 to 52) key WeekOfYear
Year Year Year name Years

 

Of course this is just one way of doing it and you might start using this approach and finetune it to your needs later. You may notice that I used the same source columns as names for fiscal month and month (and other related attributes). This is done in order to provide different keys for the fiscal periods in order to sort for example the months accordingly. For the standard calendar you want to see your months as January, February, …, December while for a fiscal calendar with a fiscal year start of April 1 you want to see the fiscal months in the correct order April, May, June, … December, January, February, March. The separate attributes allow different keys and so the sorting is not complicated.

You will also notice the ISO Week / ISO Year attributes which I’ve included for the European countries. I’ll give some more information about this in a later post.

In this situation you could build natural hierarchies like the following ones:

image

The following screenshot demonstrates how you can leverage the atttribute relations for example in Microsoft Excel 2007:

image

Here, we simple display some more attributes alongside with the day information.

The result may then look like this

image

 

If you’ve struggled with date dimension attributes and relationship too like I did, I’m very interested in the ideas you developed for setting up your date dimension and I encourage you to leave me a comment.