Time in fact or dimension? Accumulating snapshot

[dellsters]

I need to build an accumulating snapshot where not only do I need to track the many dates I need, but also the specific times of when things occur. Role playing and using a date dimension is not enough because it doesn't track the specific time of the day. So for every occurrence, would I have a date FK going back to the date dimension and the actual time of the day stamp in the fact table? I have about 10 different columns, so that would make 20 for 10 dates and 10 times of the day. Is that a good design? To make it more complicated, my company has a global presense that uses 3 different time zones. Would I save different time zones in the fact, making the 10 dates a 60 different column design?

I didn't think I would benefit from a time of the day dimension since there is not much description to it. I was also thinking whether it makes sense to have one column for date and time, and not use a date dimension at all?

[ngalemmo]

If there isn't anything you need to know about the time of day, then just store it in the fact table as a degenerate dimension.

Date, though, is almost always handled as a dimension because there is business meaning attached to it. Attributes, such as month, quarter, week of year, fiscal period, fiscal year are all examples of attributes normally associated with a date dimension.

If time of day does have meaning, such as the need to report morning or 'rush hour' or other such classifications, then a separate time of day dimension would be created to support such analysis.

[dellsters]

Actually, in some cases I need to query amounts by the hour (total by the hour of the day). I guess it would be clean to have a time dimension. The problem is that I have 60 columns with time stamps for this accumulating snapshot. I am tempted to ommit the time dimension and just query this fact table and group it by the hour since the timestamp in the fact provides that. Is it still worth linking all 60 columns to a time dimension (role playing) than to directly query the time stamp in the fact? What is the disadvantage of querying directly the timestamp in the fact and group it by the hour?

[raikarleena]

Looks like space is not an issue in your case (consider ALSO the space in memory when you have 60 columns with datatype time as opposed to integers.

Also, you would need decodes every time you want to report by say "Rush hour" or any attribute that cannot be derieved directly unlike hour, min,sec. When you would want to group by say "Rush hour" (or similar attribute) you would be dealing with a decode on all the time values and group by filtering the ones that suit your criteria.

[ngalemmo]

Why do you have 60 time columns? Are these different type of time or events? Would it make more sense for a vertical design with a dimension indentifying the event or type?

[Sponsored content]