Average Cycle Time of Workitems

Find below a query that shows the average Cycle Time per state, you can store the results in a temporary table and join it to provide averages to help in value stream mapping.

 

Cycle Time Per Sprint

The bulk of the work is done by the query below

   1:  WITH cteStateChange(rowId, tpId, wiId, [State], PrevState, UpdTime) AS(

   2:      SELECT Row_Number() OVER(Order by wi.TeamProjectSK, wi.System_id, wi.System_Rev) 

   3:          , wi.TeamProjectSK

   4:          , wi.System_Id

   5:          , wi.System_State

   6:          , wi.PreviousState 

   7:          , wi.System_ChangedDate

   8:      FROM dbo.DimWorkItem wi

   9:      INNER JOIN dbo.FactWorkItemHistory wih

  10:          ON wih.WorkItemSK = wi.WorkItemSK

  11:          AND wih.TeamProjectSK = wi.TeamProjectSK

  12:      WHERE 1=1

  13:      AND (wih.StateChangeCount = 1)

  14:  )

Visualize your workflow with Team Foundation Server

I created a custom report for Team Foundation Sever 2010 to visualize your workflow in order to identify bottlenecks

 

Identify bottle necks

This indicates a bottleneck in testing there is work stacking up for the QA department.

This report will tell you at a glance where to take action.

Inspect Flow

As you see in the picture above no clear “steps” for committed are noticeable in the above chart. The chart below shows a more healthy example.

You also notice that there is an inventory of untested items building up.