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 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_thumb2

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_thumb5

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

image_thumb11

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

The result may then look like this

image_thumb10

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.

9 comments:

  1. I googled your SSAS blog looking for help on a time hierarchy. My main problem is how to add weekofyear attribute to a dimension previously using only year, month and date.
    I notice that neither you nor Adventure Works uses some
    Year->month->week of year hierarchy. Could this work, or must one use Year ->week and Year->month hierarchies separately ?

    ReplyDelete
  2. Hi Eric, I would recommend having one-to-many relationship between the levels of your hierarchy and therefore creating two separate hierarchies for this purpose.
    However, if you need to have a hierarchy like Year->Month->Week (which would result in showing the same week for two different months) you will have to include the month as a key to the week in order to model your attribute relationship but again, I would not recommend this as it would result in weeks with a different number of days (which would be confusing in analysis).

    ReplyDelete
  3. Hi, that's right, when 28 et 35 days long months started to appear in derivated measures, I began suspecting something was wrong…
    So I guess we'll stick to Y-W hierarchies for the moment, which is sad for it doesn't seem to allow a clean time reporting such as weekly data on a Y/M/W scale. As a matter of fact when you apply a M-W time hierarchy on our weekly data, those of weeks which are between two months get “sliced”, and since weekly fact data has a daily grain (using first weekday date as the key for relating to the time dimension) it results on error on the second part of the week.
    I guess the solution for this would be to use the Week attribute as the key, but I don’t know how to get that kind of key on the fact table. Is there a “week” data type I should use ? Dunno.
    Thanks anyway for acknowledging my question.

    ReplyDelete
  4. Hi Eric, If your date dimension is at day granularity then all your facts should be at the same granularity (you should only link fact tables to the primary key of your dimension). And if your requirement is to combine data of different granularity (for example actuals per day and plan values per week) all this data should by on day level. So you may link weekly data to the first day of the week but then when you look at the month hierarchy the are not correct for weeks that are sliced by month's end. So one option would be to split the weekly values to days (in ETL) to get both views right.
    Hope this helps a little.

    ReplyDelete
  5. How are you applying Localization to DayFormat?

    ReplyDelete
  6. In order to localize formats and names for months or days you can use the localization features of SSAS (providing different source columns from the data source view). For the month name you would have columns like Monthame_EN (January), Monthname_ES (Enero), Monthname_GE (Januar) etc. In the dimension the client language is then mapped to one of the source columns.

    ReplyDelete
  7. Hi Hilmar, I have read at other places and you also mention that "Remove attribute hierarchies of attributes that also appear as a level in a hierarchy". Why is that? I created some hierarchies for time dimension and defined attribute relationships. I had to use a collection like 'Year + Month' to define key for the Month Attribute, similarly Year+Quarter for quarter and so on. Now I want to display them in Hierarchy as well as standalone attributes. If I do that, the quarter or month attributes would have duplicates. Is it because it is a part of hierarchy and its key is a collection of attributes. Is there a way to display attributes in a user defined hierarchy as well as distinct standalone attributes. By the way, nice Blog. It was really helpful!

    ReplyDelete
  8. Andy, you could create two separate attributes for the month, one for usage as a standalone attribute (you should use the proper name 'Month' here) and one for use in the hierarchy (name could be something like 'MonthByYear', 'MonthForHierarchy') which is then hidden (AttributeHierarchyVisible=false). So the user only sees the standalone attribute while the hierarchy still uses the month in the context of the year (which makes it possible to define proper attribute relationsship).
    Hope this helps.

    ReplyDelete
  9. Thanks Hilmar. That's exactly what I was looking for!

    ReplyDelete