Calculate minutes between workflow steps

The initial request was to provide data to show the amount of time it takes for certain workflows to complete. Obviously, the workflow stages are separate fact records. When presenting initial data profiles, it was noticed that average time was really long. Digging deeper, it was found that there is a lot of weekend, and nighttime hours that was the culprit to a majority of these records. So, in addition to the easy "time to step approval" calculated field, they would like a "time to step approval working time only" calculation.

I am not sure what the best way to go about this. So far I have weekend and holiday flags in the DateDim, and an office hours flag in the TimeOfDayDim. The calculation is really slow. Is there a better way?

TheNJDevil wrote:...Obviously, the workflow stages are separate fact records.

This maybe where you are going wrong. The preferred method of modeling a workflow is using an accumulating snapshot fact table. This puts all of the events on a single row making lag calculations simple.

The problem with accumulating snapshot in this situation is that the workflow is jagged. For example, a step can have 3 activities performed, Approve, Reject, Hold. They would like statistics on time spent separately in a step when first hit and when hit again after a rejection. Rejection can happen multiple times.

For example, if my job is to review new project documentation for accuracy, I will reject it back to "document project" if I found errors. This can happen several times until it is deemed sufficient for Final Document status. Also, depending on certain conditions, it could require work (additional non-standard steps) from experts.

I have seen some get thru in 10 steps, and more complex ones get thru with several rejections in 34 steps. I am also not an expert on dimensional modeling so going off my knowledge gained so far, this situation called for transactional grain. Please enlighten me if I am mistaken.

You may want to deal with it in the ETL process. Calculate the elapsed time when loading the steps and store it as a measure.

Alternately, look at LEAD and LAG windowing functions in SQL. These allow you to look at two rows in the table at the same time, you could possibly perform the calculation there. But considering the complexity, doing it in ETL is more flexible, and queries would perform better.