How to create time attribute relationship (2)

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 c .
• 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.

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:

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

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

The result may then look like this

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.