Home » Metrics
Category Archives: Metrics
This is part 2 of a series of posts that show how we can model virtual kanban systems on TFS 201x.
If you didn’t read part 1, I would recommend that you start there.
Big gotcha with regard to this blog post
You cannot access the TFS data warehouse (which we will need to do) using Visual Studio Online and as far as I know, there is no way to access the data stored there in any way yet. As such, this post will make the assumption that you are using TFS 201x on-premise and can access the TFS Data Warehouse.
Same assumptions from part 1 apply and I’ll re-iterate them here along with a few other assumptions.
- You are a TFS user and will understand the features that I’m using.
- You have a basic understanding of TFS Process Templates
- You have a basic understanding of the Iteration Path hierarchical work item field that is present in the various Process Templates
- A basic understanding of virtual kanban systems as they pertain to software development
- NEW – You have a basic understand of how TFS Work Item States work
- NEW – You have a working knowledge of Excel 2010+
- We’ll be connecting to databases and programming in VBA
Just as a quick review, here is the current state of our virtual kanban system implementation.
Remember that we are using iteration paths to represent the backlog (uncommitted work) and the kanban system and the only work item state we will be (really) interested in is “Done”.
Calculating Basic Lead Time Metrics
Using the movement of a work item into the kanban system iteration path, until it gets to a “Done” state, we can now calculate the lead time for that item. The easiest way I’ve seen to do these analysis, after your kanban system has been setup and running for a while, is to use Excel to consume and analyze TFS data warehouse data about work item changes.
Developer Ribbon in Excel
The first thing we need to do is turn on the Developer Ribbon in Excel. After you’ve started Excel (I’m using 2013), right click on the ribbon and select the Customize the Ribbon option.
Turn on the Developer tab by checking the box and pressing OK.
Prepping the Spreadsheet
For this exercise, I like to create three sheets in my Excel workbook.
We will refer to the names of these sheets in our Excel VBA code.
Getting Data From TFS
Excel has the functionality to connect to easily connect to SQL Server databases and that is exactly where TFS stores all of it’s data. TFS stores the data that we are interested in in a database that is called Tfs_Warehouse.
Navigate to the Data tab on your spreadsheet and put the caret on cell A1.
Now create a connection to Tfs_Warehouse by visiting the Data tab, clicking the From Other Sources button and selecting From SQL Server.
Enter the name of the database server that is the backend for your TFS installation and use the appropriate credentials (SSO or SQL Server account) in the connection dialog and click Next.
Note – You will need to able to access the server with either your domain account permissions or a SQL Server account that has permissions to access Tfs_Warehouse database.
Select the Tfs_Warehouse database on the server and the DimWorkItem table and click Next.
Change the name of the connection to something more friendly like LeadTimeConnection.odc and set the friendly name to something more descriptive and click Finish.
An Import Dialog will pop open once you have hit Finish. Select the Properties button on this dialog.
On the Properties dialog, click the Definition tab and then change the Command Type to SQL and enter the SQL statement below in the Command Text textbox and press OK.
Now press OK on the Import dialog and you should now see the Data sheet fill up with data from your TFS database.
VBA Magic to the Rescue
Now that we have our work item data coming, we need to do a couple things:
- Narrow it down to the time period under review
- Determine what started and finished within that time period
- Calculate the Lead Time for the items that completed during this period
Caveat – I’m not a VBA Script writer by trade! I’m fairly certain there could be better ways to write this code, but since writing elegant VBA code is not my objective, I’ll just get you to the lead times. If you have any suggestions on how to better code this out, I’d love to hear from you.
Create a VBA Module
On the Developer tab, click the Visual Basic button to get to the VBA IDE.
Once you are in the Visual Basic editor, create a new module for holding your code by right-clicking on the Module node of the tree and inserting a new Module.
Now we can just start entering all of the code from below.
Declare String Constants in the Module
These strings (global) allow us to do set our sheet names and change the iteration paths that we use to model our kanban system.
Determine Vertical Range of Data
This code needs to discover the length of the data set that we’re processing. This function does that.
Create Lead Time Generation Function
This code is pretty messy but isn’t actually very complicated. What happens in this function is that we go through each row of the source data set and look for the work item entering the kanban system iteration path. We then look for the work item getting to the done state.
If a Start Date and and End Date are discovered for the work item, we make an entry in the LeadTimeReport sheet with the calculated Lead Time. If we do not find those dates, no entry is made.
You can step through the code in the IDE using F8 to step through each line of the function.
Create Public Access and Initialization Function
This code is the function that is going to be invoked by the button we put on our parameters sheet. It sets all of the global values for the module, acquires the start and end date of the period under observation, modifies the command text of the SQL query to use the dates in the query,
Now that the module has been created that does all of the data transformation work, we need a simple UI that allows us to pick a Start Date and an End Date for the period under observation and a button to kick the whole thing off.
After you have selected the Parameters worksheet, in cell A4 type out “Start Date”. In cell A6 type out “End Date”. And finally on B3 put “Period under Observation”. These are labels for our UI.
Now on the Developer tab, click the Design Mode icon on the ribbon. Now we can Insert 2 Date Picker controls onto the worksheet. Click the Insert icon on the ribbon and then click the the very last icon on the bottom right.
In the More Controls dialog that comes up, select the Microsoft Date and Time Picker Control 6.0 (SP4) and then click OK. You will now be able to “draw” out the new control on the worksheet. Draw out the first control roughly on cell B4. In the Excel Name Box, name the control startDatePicker.
Repeat the process for the second date time control and draw the second control roughly out on B6. In the Excel Name Box, name the control endDatePicker. These names are used in the script to find the range parameters.
Now we need to add a button. Click the Insert icon again and select the first icon on the top left and draw out a button roughly on cell Here is what it should look like when complete.
Generating Lead Time Data
We should have completed all of the steps required to generate lead time data for the kanban system that we’ve model in Part 1 of this series of blog posts!
If you press the buttons and everything has been typed in correctly, you should see data populate the LeadTimeReport worksheet. This will look roughly like this.
You can format the first row of the LeadTimeReport sheet however you see fit. If you want to add more rows of labels, you will need to edit the scripts as they start adding lead time entries on row 2.
I’d like to point out that in this case, I’m using the Brian Keller ALM VM for Visual Studio 2013.4 and the Fabrikam Team Project inside of that VM for data. You will need to change the script to reflect the iteration paths that you are using your kanban system in TFS.
This has been a fairly long and detailed post and I hope that you’ve been able to follow it and get the lead time data generation working in your environment.
There are more sophisticated ways of generating this lead time data (programmatically more advanced applications). The reason I wanted to use Excel and Visual Basic was to allow anyone to generate lead time data with the basic tools that are available in a normal IT shop that uses TFS. If you’d like to expand on this approach and develop an application that does this (which I have already done elsewhere), I hope that you can use this example as a guide to what you’ll need to do to write your application.
Thanks and I hope to hear from you in the comments soon. Let me know if this worked for you or how I can make this example better.
I received a great question from one of my webinar attendees and this question has also received a bunch of attention from the Kanban community since this visualization first surfaced in the summer of 2012 ago at the Kanban Leadership Retreat in Austria.
How do we measure our maturity using the Kanban method?
I have used the technique that came out of Mayrhofen at #KLRAT as the basis for how I work with teams to monitor Kanban maturity. There isn’t a “standard” set of questions that the Kanban community uses in the creation of the kiveat chart. In general, it is being suggested that each team/coach comes up with a context-specific way of measuring and ranking maturity within the 6 practice groups that applies to “that team”. That visualization can then be used to monitor the anticipated growth for that team over the course of time. One thing that the Kanban community is cautious about though is “comparisons” between kiveat charts and assessments. Since each assessment is relatively subjective, comparisons should be avoided as it would be hard to compare and may potentially be misleading to the team.
That said, here is how I use the chart.
I have a set of questions I ask per category. On a scale of 1 to 5, 1 being “We do not do that”, 3 being “We do that”, 5 being “We could teach someone to do that”, I look for activities that would allow me to select one of those values for one of my questions. As an example:
Question: Identifying types of work
Observation: Does the team identify different types of work? Do they have User Stories, Bugs, Improvement Tasks, etc. described within their process? If they do not, they would be a 1. If they do, they would be a 3. I’d then look for evidence that they are capable of teaching work item type defining to another team, or that they could do so if required. If they could, they would be a 5.
I go through all of my questions for an axis of the chart and give them these rankings. I then take the average value of all of the answers and that is my data point on the kiveat chart. My visualization category currently has 10 questions in it, so if I get 35 total points/10 questions, I get a 3.5 visualization score on the kiveat chart.
My Limit WIP category currently has 5 questions, so 15 points/5 questions would give me a 3 Limit WIP ranking on the chart.
Following this pattern, I eventually end up with 6 axis on the kiveat chart, all ranked from 1 to 5 and this “coverage” can be used to describe the team’s Kanban maturity from my perspective.
David Anderson, in some of the slides I’ve seen him use and in talking to him, describes a progression of novice to experienced tactics within each category and each of his axis has different scales to represent the increasing maturity on that scale. He does arrive at the same kind of coverage visual and describes that coverage as an indicator of maturity from his perspective. My kiveat chart and David’s would not be comparable though and this is completely OK and encouraged. As I mentioned above, the current thoughts within the Kanban community are to discourage direct comparisons between these kiveat charts.
What kinds of questions would you use to measure you’re companies Kanban maturity?
If you’ve built a kanban system, or you’ve tried to put a WIP (work in progress) limit on a phase in your workflow, you’ve probably asked or been asked this question. And very often, then answer is “I don’t know. How about we try n.” where n is a guess. Usually an educated guess like:
- 2 x the number of developers
- 1.5 x the number of people on the team
- Number of people involved + 1
And these are all ok places to start if you have no data, but with a little data, we can stop guessing and set our WIP limits with some empirical information and at the same time start building a system that will satisfy one of the assumptions required for us to use Little’s Law properly. There are two things that we need to have in order to use this super easy method:
- Data about average time in state for work items
- CFD (cumulative flow diagram)
Ok, I guess we don’t need the CFD if we have the data, but it sure is nice to visualize this information. 😉 We do need to have some data about the way that work passes through our system and we need the data that would be required to create a CFD. For the purposes of this post, lets assume that we are capture the time in state for each work item. Entire time in the system is often called lead time. Time in between any two phases in the system can be cycle time but we’re interested in cycle times for a single state at a time as our objective is to determine the WIP limits for each column in our kanban system.
Let’s use a simple approach to measuring average time in state in days. On our simple kanban system above, we have a Ready State, Development state and a Done state. Each day, we count the # of items that have cross a state boundary and put those numbers on our CFD chart. After several weeks, we have enough data to start calculating a couple new metrics from our CFD.
With even just a couple weeks of data that visualizes how work moves through our system, we can now start measuring Average Arrival Rate (AAR) and Average Departure Rate (ADR) between any two states in our system. AAR and ADR are simply represented as the slope of a line. If we calculate the rise (x-axis) over the run (y-axis) values, we get the slope.
It is the relationship between the two values that is interesting to us and will allow us to more empirically set the WIP Limit values in the system. Based on our understanding of Little’s Law, we are striving for a average rate of divergence between the two of near 0.
A negative divergence suggests the WIP limit is to low and that we are under utilized.
A positive divergence rate suggests the WIP is too high and we are overburdened.
Since ADR (the rate at which we finish work) represents our current capability, the value of ADR should be considered a great candidate for the WIP limit for this state. With the the right WIP limit in place, AAR should match ADR and we will find an average divergence rate of 0. As your team’s capability changes, our divergence will go either positive or negative and will provide an indication of when our WIP limits should change and what they should change to.
And there you have it! When the rate of divergence between AAR and ADR is near zero, we know that our WIP limit is right and that we’re satisfying one of the assumptions required to make Little’s Law work for us!