A Problem in time

Hi Guys,

Im having a bit of a conundrum and am seeking some best practise advice.

I have a fact table for phone call periods on any given day with call duration as a measure, calls can go over midnight and my time dimension has a grain of minutes.

Im not sure how to note the calls going over midnight, given that the calls go over midnight the call will have to be split in 2 one running to midnight and the second spanning from midnight to call end.

The problem is that with my time dimension running in minutes this will have to end at 23.59 which will leave my meausre a minute short of accurate.

eg call date,      call start , call end, call duration

     02/04/2012  23:45       23:59         14

     03/04/2012 00:00       00:05          5

giving a misleading 19 minute call duration

alternatively

eg call date,      call start , call end, call duration

     02/04/2012  23:45       00:00         15

     03/04/2012 00:00       00:05          5

this is equally misleading as 00:00 refers to 03/04 instead of the 02/04  and the call will have ended 23hrs 45 minutes before it started.

if i add a call enddate of 03/04/2012 to the fact table that still invalidates the grain of the table of call periods per day.

any advice will be greatly appreciated.

ill also be happy to clarify any ambiguities.

BB

.

social.technet.microsoft.com/Forums