Search This Blog

Saturday, July 25, 2009

How to create time attribute relationship (2)

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


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
DayType (eg. Holiday) key IsHoliday
Day Short Name Year
DayShortName (e.g. Sa) key Days
Day Name Year
DayName (eg. Saturday) key Days
Fiscal Month Of Year FiscalYear (eg. 2009)
MonthOfFiscalYear (1 to 12)
MonthOfYear (eg. 7) key
Fiscal Month Short Name FiscalYear (eg. 2009)
MonthOfFiscalYear (1 to 12)
MonthShortName (eg. Jul) key
Fiscal Month Name FiscalYear (eg. 2009)
MonthOfFiscalYear (1 to 12)
MonthName (eg. July) key FiscalMonths
Fiscal Quarter Name FiscalYear
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:


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.

Saturday, July 11, 2009

How to create a time attribute relationship (1)

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:

with member monthname as [Date].[Month of Year]
select monthname on 0,
[Date].[Day of Year].[Day of Year]
on 1
from [Adventure Works]

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.