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

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


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.