Modeling cube for monitoring a process

I am working on modeling a cube to track the effectiveness of steps in a process. I have two main questions:

- how to track the duration of the current step, basically how long has it been in progress
- how to record the duration once the step is complete

My current idea is to have one record in the fact table for each step in the process. Each fact would have a days to accept, days to deny, and days in progress. A step is always in one of these three statuses. Thus, if a step is in progress then during the nightly data load I would update the days in progress on the existing fact. Once the step is accepted, then I would change the days in progress to zero and do a one time update of the existing fact to record the days to accept. Once a step is accepted or denied, it cannot be reopened, so I would not need to update the fact again.

Is there a better way to design this model?

Yes, use an accumulating snapshot if you have well defined steps.

Thanks for the quick response. An accumulating fact appears to be what I am looking for in the BI world.

One caveat for cubes, you generally have to rebuild the entire cube for accumulating snapshots.