Calculate minutes between workflow steps

View previous topic View next topic Go down

Calculate minutes between workflow steps

Post  TheNJDevil on Fri Apr 06, 2012 12:16 pm

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

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Calculate minutes between workflow steps

Post  BoxesAndLines on Fri Apr 06, 2012 2:10 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Calculate minutes between workflow steps

Post  TheNJDevil on Fri Apr 06, 2012 4:08 pm

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.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Calculate minutes between workflow steps

Post  ngalemmo on Sat Apr 07, 2012 12:13 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Calculate minutes between workflow steps

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum