surrogate key on time dimension 2

[avjoshi]

What is the advantage of creating surrogate key on time dimension? Especially, if the scope of the warehouse application is confined to a limited geographic area in one time zone, like the state and local governments agencies, would it make sense to create surrogate key on time dimension?

Anand

 

[MDXer]

My first answer would be performance. It's faster to join a 1:N than it is to group on many records. Another factor could be the level of time reported and the format the date is stored. If your reporting is done at the mm/dd/yy but the date in the fact records is stamped in mm/dd/yy hh:mm:ss then you have to process your reports for a day by using a date range, so then usability may become an issue.

Granted you can argue the overhead required to key your fact records with the surogate key but this can be overcome with a bit of creativity, the process I use to create my surrogate key does not even require a join to my date dimension but then my level of granularity for these facts is the day grain.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[teccum]

Using surrogate key for your time dimension allows consistency among the keys used between different dimensions. It is easy to maintain surogate keys than maintaining date as a key or any other sequence.

If your going to keep your history less then it is more suitable. I have used surrogate keys and have not found any problem with that. In fact in couple of schemas I have used surrogate key for the time dimension.

Since in your case it is a govt. agency you might have to face different periods of time and in which case it is still more adaptable.

thanks
teccum

 

[johnherman]

Agree. Performance. Integer joins are faster than any other kind of join and surrogate keys are integers.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[avjoshi]

Agreed, but I always felt that the primary reason for the surrogate keys was to record the changing context of the dimension attributes. The reason for them being integer is because integer joins are faster. Unlike other dimensions, however, time is constant (within the narrow perspective of application serving one and only time zone. This is the first time I am working on a project that has this limited scope; and hence the question.)

Grain of the fact tables is either day of hour within the day. I intend to create time dimension with record for every hour of the day.

In my experience, I have seen majority of the queries are related to either 'period comparisons' or aggregations.
These results can be easily achieved without an additional join to the time dimension. In rare cases where additional time attributes are required we can always join the fact table to the time dimension using natural key.

Am I wrong?


Anand

 

[MDXer]

To begin I am unsure as to what difference it makes if it is 1 time zone or multiple time zones 7/2/05 is the same date regardless of the timezone. Granted this may affect how you view the Time period bas3ed upon zone.

As for right and wrong I am of the belief that if it works in an acceptable manner then it is right, however there are better ways of doing something.


Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[johnherman]

The primary purpose of integer surrogate keys is increased performance, thus we even use them in Type-1 Slowly Changing Dimensions, where changes to dimension attributes are not important, and hence, overwritten during update.

Star schemas work even with natural keys only, so feel free to streamline the design as much as you desire.

However, another reason to use surrogate keys has to do with the impossibility of guaranteeing that the source system will *always* provide a unique natural key. For example, consider acquisitions and the consequent merger of systems or simply the migration to new transaction system.

In your example, it is probably not significant, since it is unlikely that there are very many business systems on this planet using non-standard calendars. However, remember that there are other calendars, such as fiscal and religious calendars.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[avjoshi]

Thanks John and MDXer. I just wanted to bounce my idea with experts. I appreciate your valuable comments.

Anand.

Anand