Agile Ramblings

Home » 2014 » January

Monthly Archives: January 2014

Calculating Lead Time for Work Items on TFS 201x

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.

Assumptions

Same assumptions from part 1 apply and I’ll re-iterate them here along with a few other assumptions.

  1. You are a TFS user and will understand the features that I’m using.
  2. You have a basic understanding of TFS Process Templates
  3. You have a basic understanding of the Iteration Path hierarchical work item field that is present in the various Process Templates
  4. A basic understanding of virtual kanban systems as they pertain to software development
  5. NEW – You have a basic understand of how TFS Work Item States work
  6. NEW – You have a working knowledge of Excel 2010+
    1. We’ll be connecting to databases and programming in VBA

Quick Review

Just as a quick review, here is the current state of our virtual kanban system implementation.

image

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.

image

Turn on the Developer  tab by checking the box and pressing OK.

image

Prepping the Spreadsheet

For this exercise, I like to create three sheets in my Excel workbook.

  1. Parameters
  2. LeadTimeReport
  3. Data

image

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.

image

 

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.

image

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.

image

 

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.

image

 

An Import Dialog will pop open once you have hit Finish. Select the Properties button on this dialog.

image

 

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.

image

 

SELECT system_id, system_title, system_state, di.iterationpath, system_changeddate, System_WorkItemType
FROM dimworkitem dw
INNER JOIN dimiteration di
ON dw.iterationsk = di.iterationsk

 

Now press OK on the Import dialog and you should now see the Data sheet fill up with data from your TFS database.

image

VBA Magic to the Rescue

Now that we have our work item data coming, we need to do a couple things:

  1. Narrow it down to the time period under review
  2. Determine what started and finished within that time period
  3. 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.

image

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.

image

Now we can just start entering all of the code from below.

Declare String Constants in the Module
Public paramSheet As String
Public reportSheet As String
Public dataSheet As String
Public connectionName As String
Public backlogIterationPath As String
Public kanbanSystemIterationPath As String
Public startState As String
Public endState As String

 

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
Private Function VerticalRange()

Sheets(dataSheet).Activate
Range("B1").Select

Dim height As Integer
height = 0

Do While ActiveCell.Value <> ""
    height = height + 1
    ActiveCell.Offset(1, 0).Activate
Loop
VerticalRange = height

End Function

This code needs to discover the length of the data set that we’re processing. This function does that.

Create Lead Time Generation Function
Private Sub GetLeadTime(ByVal rows)

Dim sourceRowCount As Integer
Dim targetRowCount As Integer
targetRowCount = 2

Dim sws As Worksheet
Dim tws As Worksheet
Set sws = ThisWorkbook.Sheets(dataSheet)
Set tws = ThisWorkbook.Sheets(reportSheet)

Dim workitemid, firstRow, lastRow, nextFirstRow As Integer
firstRow = 2

For sourceRowCount = 3 To rows
     workitemid = sws.Range("A" + CStr(firstRow)).Value
     Dim wiD As String
     wiD = sws.Range("A" + CStr(sourceRowCount)).Value
     
     If wiD = workitemid Then
        'skip if true – we are looking for the last row for the current work item
     Else
        lastRow = sourceRowCount - 1
        nextFirstRow = sourceRowCount
    
        Dim isUncommittedBacklog, isKanbanSystemBacklog, isUncommittedNew, isKanbanSystemNew, isDone As Boolean
        Dim uncommittedBacklog, kanbanSystemBacklog As String
        Dim uncommittedBacklogFound, kanbanSystemBacklogFound As Boolean
        Dim startDate, endDate As String

        For rowNum = firstRow To lastRow
            isUncommittedBacklog = InStr(1, sws.Range("D" + CStr(rowNum)).Value, backlogIterationPath, vbTextCompare) > 0
                If isUncommittedBacklog = True Then
                    isUncommittedNew = InStr(1, sws.Range("C" + CStr(rowNum)).Value, startState, vbTextCompare) > 0
                    
                    If isUncommittedNew = True Then
                       uncommittedBacklogFound = True
                    End If
                End If
            
            isKanbanSystemBacklog = InStr(1, sws.Range("D" + CStr(rowNum)).Value, kanbanSystemIterationPath, vbTextCompare) > 0
            
            If isKanbanSystemBacklog = True And uncommittedBacklogFound = True Then
                isKanbanSystemNew = InStr(1, sws.Range("C" + CStr(rowNum)).Value, startState, vbTextCompare) > 0
            
                If isKanbanSystemNew = True Then
                   kanbanSystemBacklogFound = True
                   startDate = sws.Range("E" + CStr(rowNum)).Value
                End If
            End If
                
            If kanbanSystemBacklogFound = True And uncommittedBacklogFound = True Then
                isDone = InStr(1, sws.Range("C" + CStr(rowNum)).Value, endState, vbTextCompare) > 0
                If isDone = True Then
                    endDate = sws.Range("E" + CStr(rowNum)).Value
                End If
            End If
        Next rowNum

        'Calculate Lead Time
        If IsDate(startDate) And IsDate(endDate) Then
            Dim leadTime As Integer
            leadTime = DateDiff("D", startDate, endDate)
            tws.Range("A" + CStr(targetRowCount)).Value = sws.Range("A" + CStr(lastRow)).Value
            tws.Range("B" + CStr(targetRowCount)).Value = sws.Range("B" + CStr(lastRow)).Value
            tws.Range("C" + CStr(targetRowCount)).Value = leadTime
            targetRowCount = targetRowCount + 1
            startDate = ""
            endDate = ""
        End If
        
        firstRow = nextFirstRow
        nextFirstRow = 0
    End If
Next sourceRowCount

End Sub

 

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
Public Sub GenerateReport()

'constants in module
paramSheet = "Parameters"
reportSheet = "LeadTimeReport"
dataSheet = "Data"
connectionName = "LeadTimeConnection"
backlogIterationPath = "\Tailspin Toys\Iteration 1"
kanbanSystemIterationPath = "\Tailspin Toys\Iteration 1"
startState = "Active"
endState = "Closed"

Dim startPeriod, endPeriod As Date
startPeriod = ThisWorkbook.Sheets(paramSheet).startDatePicker.Value
endPeriod = ThisWorkbook.Sheets(paramSheet).endDatePicker.Value

Dim cmdText, periodDates As String
cmdText = ThisWorkbook.Connections(connectionName).OLEDBConnection.CommandText
periodDates = " Where system_changeddate between '" + CStr(startPeriod) + "' and '" + CStr(endPeriod) + "' and System_WorkItemType = 'User Story' Order by system_id , system_changedDate asc"

Dim location As Integer
location = InStr(1, cmdText, "where", vbTextCompare)

If location > 0 Then
    cmdText = Left(cmdText, location - 1)
End If
    
ThisWorkbook.Connections(connectionName).OLEDBConnection.CommandText = cmdText + periodDates
ThisWorkbook.Connections(connectionName).Refresh

Call GetLeadTime(VerticalRange)

End Sub

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, 

Create UI

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.

image

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.

image

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.

image

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.

image

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.

Final Thoughts

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.

Modeling your kanban system on TFS 201x using Iteration Paths

Microsoft has been making some great strides in adding features to Team Foundation Server (TFS) that allow for the modeling of the virtual kanban system that your teams might be using to manage their work. The web access kanban boards and the ability to manage WIP limits on the boards are two such features.

Kanban tooling is still relatively new to Microsoft and they are trying to adapt a product/approach that has historically been very well suited to Scrum and Traditional work management techniques, and so it isn’t much of a surprise that we need to tweak the way that we use TFS 201x to help us correctly model our virtual kanban systems. To that end, we need to take advantage of a few of the features in TFS to allow our teams to be more effective when using kanban.

This is the first in a series of blog posts detailing how we can model kanban systems on TFS.

Assumptions

I have several TFS related assumptions that I’m making as you read this post:

  1. You are a TFS user and will understand the features that I’m using.
  2. You have a basic understanding of TFS Process Templates
  3. You have a basic understanding of the Iteration Path hierarchical work item field that is present in the various Process Templates
  4. A basic understanding of virtual kanban systems as they pertain to software development

Iterations vs. Iteration-less Planning

The first challenge that we’ll encounter when using TFS is that there is no specific kanban process template, which is actually the right approach in my opinion. It would be tremendously difficult to create a one-size-fits-all kanban process template and I think we need to get into the habit of customizing our process templates if we’re going to be using kanban work management techniques. That said, we still need to be able to have an ability to plan in an iteration-less environment.

This is where we get a bit confusing with the features we use in TFS vs. our intent, but it will work for us.

TFS uses the Iteration Path structure to determine what is on the Product Backlog board in web access. The Product Backlog board is the current feature in TFS to visualize our kanban system so we would like it to represent things in a more traditional kanban way. In order to do this, we need to modify the Iteration Path structure and communicate with our team how it is intended to be used. This means that we need to communicate that we are using the TFS feature named Iteration Path for something other than an iteration as it would be commonly understood if we were using a Scrum work management approach.

Here we find a model of a simple kanban system.

image

We have an uncommitted backlog of options or ideas that we may or may not eventually work on. We have a Ready Queue which is the work we have committed to doing next, we have a Doing column that represents the actual development work and a Done column that represents that we have finished some piece of work.

Understanding Lead Time

One of the primary reasons that we need to doing all of this is so that we can calculate a lead time for our work items, which TFS 201x currently does not do for us and the way that the kanban boards are currently implemented, we cannot access the data that is captured by the kanban boards. (Product Group call out here: We want access to that data!) Lead-time is a basic and fundamental metric for measuring the performance of work through a kanban system and so we kind of need to be able to generate that information.

When you want to capture lead time data for work items, we need to identify the start and finish line for the work. Done is fairly universally recognized as the finish line, but the start line is a little harder to define sometimes. In this kanban system example, we are going to state that Uncommitted work is not a part of the lead time calculation but when we move work into the Ready Queue for the team to pull from next, the lead time period has been determined to have started.

image

One of the current challenges with TFS is that lead-time is perceived to have started when a card appears on the board and the cards appear on the board as soon as it is placed into the Product Backlog iteration.

Another challenge is that all work items (uncommitted or committed) may appear on the kanban boards if the work item is placed in the Iteration Path that has been identified as the Product Backlog in TFS.

To this end, with this simple workflow, we are going to use the Iteration Path feature and create an Uncommitted backlog and a Kanban System ‘backlog’.

Creating an Iteration Path that is our Uncommitted Backlog

One of the great aspects of TFS is the ease with which you can create or modify Iteration Paths. I’m going to be using Visual Studio Online for demonstration purposes. The Visual Studio Online web interface is the same as the on-premise Team Foundation Server web access interface, so if you have TFS on-premise, you can follow along just the same.

First, we need to go configure our Iteration Path structure. Once you have arrived at your Team Project page on Web Access, you can find the link to make those modifications on the right-hand side of the page.

image

For simplicity sake, I’ve hidden a couple iteration paths in the Released branch. For now, let’s look at the root node of our Iteration Path structure. Remember that the Iteration Path work item field is a hierarchical data type.

image

Let’s create an Uncommitted iteration path. Go a head and select the root node in your environment and create a path called “Uncommitted Backlog”.  Make sure you are on the root node of your Iteration Path.

image

image

In our situation, the dates don’t mean anything to us, but TFS 2013 presents the iterations in this screen ordered by Start Date so we’ll use the dates to “order” our Iteration Paths visually.

Note – In drop-downs on the work items and in Excel, the Iteration Path values will be ordered alphabetically.

Now let’s create our Kanban System Iteration Path. It should also be a child of the root node and the start date should be 1/2/2012 or one day after the uncommitted backlog’s start date if you chose a different date than I did.

image

You’re Iteration Path structure should now look like the image above.

There is one more Iteration Path node that we need to create in order for the Web Access Task board to work correctly as well.

On the Kanban System node, create a new child called “Committed”.

image

Awesome! We have now created an Iteration Path structure that models our virtual kanban system.

Hooking up the Web Access Boards

Now there are two more things that we need to do on the Iterations management screen and that is hook up the two boards we’ll be using in web access to the correct Iteration Path nodes.

First, you need to right-click on the Kanban System node and select the Set as team’s backlog iteration option.

image

This connects the Kanban System  node to the Backlog Kanban board.

image

Next, click the check-box on the Committed node.

image

This connects the Committed node to the Task Board in web access.

image

You might be asking yourself why we have to have the Committed node under the Kanban System node. Well, give it a try if you’d like, but currently, the team’s backlog iteration cannot be the checked iteration that will be shown as the task board.

Reviewing What We’ve Done

Now that we have our kanban system model and a Iteration Path structure that represents that system, we can show how they are intended to map together.

image

Because the current crop of TFS process templates don’t naturally support modeling virtual kanban systems, we are able to use process template agnostic features of TFS to model our kanban system.

Advantages

So why are we doing all of this you ask? There are several advantages to this particular approach that I like.

  1. Process Template Agnostic
    You can use this technique with any process template that uses the TFS Iteration Path work item field.
  2. Iteration Path based Lead Time boundaries
    Currently, most teams will try (want) to use the kanban board columns but because the data is not accessible, we can’t. Other teams will model their kanban system with Work Item States, but this tends to become a mess as Work Item Template modifications need to be done on a lot of work items and frequently. This is just a PITA, especially if you don’t have TFS administration skills in-house and rely on consultants to help with process template modifications.By using membership in an Iteration Path as the means to determine when something has entered (and/or left) a lead time boundary, we free ourselves from having to use work item states as a means of creating these lead time boundaries.
  3. Clean Up Our Queues
    One of the things we want is concise, useful queues (and queue visualizations) in any tool, including Excel, Visual Studio or the web access. Currently, web access will limit the # of work items it presents in the queues, but users of Excel or Team Explorer will get potentially long and confusing lists of things to review.  While our Uncommitted queue may be (and usually is) quite long, the daily working queues and the kanban system can be made smaller and more concise. This aids planning and decision making about what to prioritize and pull daily.
  4. Works on TFS 2010
    This technique will work on TFS 2010. We won’t get the nice web access boards, but as we’ll see in follow-up posts in this series, there is still value in using this technique to capture lead time for work items.

Disadvantages

There are a couple disadvantages. They include:

  1. Can’t determine cycle time easily
    Cycle time is the time between two arbitrary states in your kanban system. Using our example kanban system, I would say that cycle time of the Doing column might be 2 days, but the lead time of the entire system is 4 days. I don’t think that I would encourage my teams to use this Iteration Path technique to model individual columns.
  2. Non-Intuitive
    I don’t think that this technique is natural to the VS community at large. I think it works and is a novel way of modeling kanban on TFS, but there needs to be education about how the team (and upstream and downstream partners) are using TFS.

Final Thoughts

Hopefully this have given you some ideas on how to better model virtual kanban systems in TFS. Virtual kanban systems are a tremendously valuable means to manage work, and even though TFS doesn’t currently support them in a intuitive manner, we can still use many of the features in TFS to achieve our goals. You could also use these techniques in similar fashion in non-kanban environments, if you simply want to clean up or better manager your queues.

In the next part of this series, I will show how we take advantage of this technique to calculate lead-times from your work item data.

As always, I would love to hear your thoughts or answer your questions.