Working with "Time" in MSTR

[talenx]

Hello all,
I have been working on this project off and on for the last couple months and I seem to be missing something here.
The project I’m working on lowest level of dimensionally is time hh:mm:ss (well really is a day but... I’ll explain)
-------
Think of an employee clocking in to start work in and (x) hours later the employee clocks out.
What I need to be able to do is track a running sum of those hours by employee.

I have been able to create an attribute that contains the start time and end time (that just gives me a time –no calculation)
I have attempted to create a metric with a date time data type (unfortunate this won't unless the analytical engine knows how to perform the correct calculations on the date time field.)

I have a fact table on a SQL 2000 server that contains the data

START_TIME is datetime datatype
END_TIME is datetime datatype
START_LUNCH_TIME is datetime datatype
END_LUCH_TIME is datetime datatype
TOTAL_LUNCH_TIME is datetime datatype
TOTAL_TIME is datetime datatype

I have been able to convert the dates to INT and perform the math extraction with SQL but I haven't been able to figure out how to make MSTR do it . I would really hate to have to store the calculations but will if I have to.

Now this would work if the report was always displayed at the day level ( of course that’s not going to be very efficient) I need to be able to roll this hours up by other variables like week month and year.

Any thoughts?
Any help would be greatly appreciated

Thanks TalenX

 

[nlim]

One way would be to put your expression for time difference in minutes as a fact. Just as an example, say your expression is (INT(start_time) - int(end_time)) / 60,

Then you should create a new fact called Time_difference_minutes, point to your fact table and type in your expression here. Then you can create a metric on top of this fact.

If your expression requires functions not in MSTR, then you should use the applysimple function.

 

[FLB]

Usually, the database will do the time/date arithmetic for you (i.e. Date2 - Date1), you might want to investigate that before you do all kind of crazy stuff! ;-)

HTH,
FLB