Last July I posted an article on how to use a histogram to gauge how accurately you or your team members estimate. I’ve had a few people ask me about this recently so I thought I’d post on how to create these histograms in TFS 2010. For a quick recap on what we want to accomplish with these histograms, take a look at my July article. You will need a process template that allows you to capture and Original Estimate and Complete Work values. (Such as the MSF for Agile v5.0 template) Assuming you have Excel 2007 and Team Explorer 2010 installed, go ahead and open Excel and follow the steps below:
Click on the Data Ribbon and Select Existing Connections.
You should see TfsOlapReport which is a data connection to the Tfs_Analysis cube. Select it and click Open. (If you don’t see the connection, go here.)
You should the Import Data Dialog. Change the location of the data to $A$2 as show below and click OK.
Drag the field “Completed Work” into the Values box as shown below:
Drag the fields “Assigned To” and “State” into the Filter box as shown below:
Drag the field “ID” into the Row Labels box as shown below:
Select the team member you want to look at(in this case Bob Smith) and select the State to be closed in the filter list above the pivot table. This is show here:
Click the dropdown next to Row Labels, select Value Filter and click on Equals.
Setup the Original Estimate value you want to estimate as shown below. (In this case we will look at the original estimate being 16 hours.) Click OK.
At this point you are ready to build your histogram. You can use Excel’s Data Analysis pack to build one for you or you can build you own. I like to build my own since the Data Analysis pack charts are kinda crappy, so this is the method I will show. Start by clicking the top-left corner of the worksheet to select the entire worksheet. Press Ctrl-C to copy it.
Select Sheet 2 and press Ctrl-V to paste a copy of the pivot table into the new worksheet.
Select the cell directly to the right of the “Completed Work” column header.
Select the Data ribbon and click the Advanced Filter button.
Set the List Range to the all the data in the Completed Work column and select the "Unique records only” check box. Click OK.
You should have a list that resembles the following:
Copy the values from the filter Completed Work column and paste them back into Sheet 1. This should resemble the following:
Label the column data you just pasted in as “Bin” and label the column to the right of it “Count”
In the first data cell of the Count column add the following formula: =COUNTIFS($B$5:$B$100,"=" &D5)
You will need to modify the first argument of the formula added in Step 18 to be the full range of the Completed Work column and the second argument to point the value in the Bin column.
Copy this formula down in the the empty cells.
Total you Count column.
Select your count column and click a bar chart on the Insert ribbon.
Rick click on your chart and click Select Data.
Select Edit under the Horizontal (Category) Axis Labels text.
Select all the values in your Bin column for the Axis label range. Click OK all the way back to the worksheet.
Select your Bin/Count table and then click the Sort Smallest to Largest button in the Data ribbon.
This data should be used to help you and your team get better at estimating. As the goal of this type of exercise is to increase our skills, I would advise against using this as a means of rating individual performance. This can backfire by creating resistance to entering real data significantly skewing the results.
Good luck and enjoy!