How to create a time attribute relationship (1)

SQL Server 2005 | SQL Server 2008

Thanks to Google Analytics I saw that  many page hits on my blog result from keyword searches for „create time attribute relationship“. And in fact, it seems to be more complicated to do a proper attribute relationship modeling with the time dimension than with most other dimensions. The reason for that is simple: The time dimension involves many related attributes (e.g. months can be written as number, short- and longname) and parallel hierarchies (like calender and fiscal view or calender weeks).

In this blog I had two posts about attribute relationship so far (here and here ). These two posts are the background for the current post as they provide information why you should set proper attribute relationship and how you can achieve this even if your data doesn’t really reflect such a relationship (as with the month number of the year in relationship to the year number). So if you haven’t read those two posts, I recommend you start with them.

When discussing with other consultants about the date attribute relationship there are many different favors and dislikes. For example: Do you want the month as a number (so you can easily use in pivot tables) or is it better to only use the month’s name and put the month’s number in the key? Actually the latter is how AdventureWorks handles the topic, so let’s take a look at that model:

The diagram in AdventureWorks looks pretty simple as many attributes have been hidden from the user. Keep in mind, however, that every design of the attribute relationship has it’s consequences for usage in pivot tables or queries. For example, think of a need to display the 365 days of the year as rows and showing what month they belong to as a second column.

One approach for that could be to use the following MDX query:

But since the attributes „Day of Year“ (1, 2, 3, … 365) and „Month of Year“ (January, February etc.) have no direct relationship, the query shows an output like this:

In the diagram above attributes are displayed in their own box if they act as a hierarchy level or if they have other attribute relationships associated with them. As in SQL 2005 the arrow points to the less detailed level (e.g. arrow from month to year because the year is less detailed).

Anyway, there is nothing wrong with the attribute hierarchies above. It’s just something you need to be aware of and if you start building your time dimension, Adventure Works might be a good approach.

As you can easily explore all the settings (keys, names, hierarchies) in the Adventure Works sample database, I’ll give another example of how to set up a time dimension in the next post.