I have a fact table where task duration (in minutes) is a measure, and time
is a dimension, e.g.

FactTable
----------
Dimension1_ID
Dimension2_ID
TimeDimension
TaskDuration

So, it might look like this with data:

Dim1_ID, Dim2_ID, TimeDim, TaskDuration
12,23,01/01/2003 10:00 PM, 130
12,25,01/01/2003 10:05 PM, 50
15,26,01/01/2003 11:12 PM, 15

What I am hoping for is when you look at a certain time period, or
aggregation of time period I get the sum task duration DURING that time
period or aggregation. For example, if my TimeDimension's hierarchy
included say, hours,days, months, etc and I looked at an aggreagtion by
hour, from 10:00 PM to 11:00 PM, the first record above would contribute 60
minutes to the total, the second record all 50 minutes and the third record
none...from 11:00 PM to 12:00 AM the first record would contribute 60
minutes to the total, the second record none and the third record 15
minutes. From 12:00 AM to 1:00AM, only the first record would make a
contribution of 10 minutes.

The only solution I have come up with is to predefine a period length (e.g.
60 minutes) and run a stored procedure to create another fact table that
generates one record for each record above for each of these periods with
the portion of task duration for that period as the measure. The down side
of this approach is I have to run the stored procedure and regenerate this
new "fact table" everytime I want to look at a different period length.

Is there any way I can organize my data, and/or use OLAP to report in the
fashion I desire?

Thanks,
John