Monday, December 27, 2010

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.

 

image Cycle Time Per Sprint
image

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:  )

Wednesday, December 22, 2010

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

image

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.

image

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