7 different dates to seconds precision

[anusha.jalla@gmail.com]

We store call center data and each call has 7 different dates associated to it. Example is CallTime, connectTime, FinishTime etc which may be just seconds apart making it important to store the dates till seconds and if possible milli seconds precision. Does these dates go into dimension table or a fact table?
If its a role playing dimension, do I need 7 dates and 7 time id's in fact table?

[hkandpal]

Hi,

one approach that you can have is create two dimension , one will store the dates and onw will store the time dimension ( that could be at the hour, minute or secound level).
In the fact table you will have one FK key to date and the other to time.
thanks

Himanshu

[ngalemmo]

Does the time of day have attributes that you wish to associate with it? If no, then just store the timestamp as a degenerate dimension in the fact.
If there are attributes, then create a dimension. However, you may still wind up storing the timestamp as a degenerate dimension if the precision requirements for the value exceed the precision of the dimension.

[anusha.jalla@gmail.com]

Thanks for the quick reply. I think degenerate dimension will suit my use case more due to precision level uncertainty at this point.

[TheNJDevil]

I don't see how millisecond data is useful from a reporting standpoint. I've done reporting on call center data and 1/10th of a second is about all that is meaningful. Also, timestamps are great, but for analysis purposes, they will want the ability to break down by time of day. A time of day table with 1/10th second data for an entire day is not that large. I also precalculated time differences because they were always doing analysis by how long certain steps took (like hold time, time to disconnect, ect) and it wasn't very fast calculating on the fly.

[Sponsored content]