Understanding how the Team WebAccess burndown chart works, Part 1

Understanding the Team WebAccess burndown chart

A couple of weeks ago I’ve read a blog post by Martin Hinselwood titled What’s in a burndown, in which MrHinsh explains what a burndown chart actually is and how the chart data can be interpreted. It was this article that inspired me to have a look at how Microsoft’s TFS11 Team WebAccess actually implements the burndown chart and to see if the functionality could be replicated using the client TFS API. This blog post series summarizes what I have learned during my explorations.

Introduction

To put it simply, a burndown chart is a graphical representation of remaining work versus time and this proves to be very useful for predicting when all of the work will be completed.  What’s important to note here, as Martin highlights in his post, is that you may want to display various data on the vertical axis of the chart. For example, the remaining work (in hours or story points), the number of remaining or closed tasks and so forth.  The value on the horizontal axis will most often be expressed as either a date or simply as an ‘iteration day’ (the n-th day in the current iteration). Since we are talking about the Team WebAccess burndown chart here, let’s first see what it looks like:

The burndown chart

As you can see, you have three data series on the chart: one of them is the remaining work (in hours) and you also have the ideal and actual trend lines. In case it’s not that obvious, the ideal burndown trend line indicates the rate at which the remaining work should (ideally) decrease with time, in order for us to deliver all the work in time. The actual trend line gives us a projection of how the work is actually decreasing and what it’s value will be by the end of the iteration.

The values on the horizontal axis range between the iteration start and finish date, as defined by the team’s iteration schedule accessible from the team home page.

The basic algorithm

Now that we’ve laid down the foundations, let’s take a high-level overview how the Team WebAccess burndown chart data is collected and processed. The whole process is executed in three phases:

  • Pre-processing: Prepare the query expression which will be used to retrieve a filtered set of work items. The query expression consists of five conditions, which are logically AND-ed together:
    • Include work items which are included in the specified team’s scope.
    • Include work items in the specified iteration.
    • Include work item types from the Tasks work item type category.
    • Include work items that are in the InProgress or Proposed metastate.
    • Include work items where the remaining work is greater than or equal zero.
  • Processing: Retrieve the burndown data (see Part 2)
    • Iterate through all the days in the specified iteration
      • If the current date is less than or equal today, use the current date to execute a historical query, using the query expression prepared in phase 1. Then, sum up all the remaining work for the current date and create a data-point to store the information.
      • If the current date is after today, set the remaining work to null, which indicates that the data does not exist. If you are wondering why the value is not set to zero instead of null, have a look at these blog posts by Eric Lippert: null is not false and null is not false, part two.
  • Post-processing: Calculate the trend lines (see Part 3)
    • Calculate the ideal burndown trend line
    • Calculate the actual burndown trend line

Now that we’ve broken everything down into manageable pieces, let’s examine each of the steps in more details.

Pre-processing

As I’ve already pointed out, this step is all about creating a query expression that allows us to filter out and retrieve only the work items assigned to the specified team and that belong to the specified iteration. Here is a code snippet that does all the work:

static string GetQueryText(TfsTeamProjectCollection tpc, Project project, string iterationPath, out string remainingWorkField)
{
    // Retrieve the common process configuration settings.
    // The work item fields, states and types are stored here.
    ProjectProcessConfigurationService pps = tpc.GetService<ProjectProcessConfigurationService>();
    string projectUri = project.Uri.AbsoluteUri;
    CommonProjectConfiguration commonCfg = pps.GetCommonConfiguration(projectUri);

    // The type fields tell us what fields are the RemainingWork and Team fields.
    TypeField[] fields = commonCfg.TypeFields;

    // The name of the work item field that represents the remaining work.
    remainingWorkField = fields.FirstOrDefault(fld => fld.Type == FieldTypeEnum.RemainingWork).Name;

    // The work item fields that specified which team the work item belongs to.
    string teamField = fields.FirstOrDefault(fld => fld.Type == FieldTypeEnum.Team).Name;

    StringBuilder sb = new StringBuilder();
    sb.AppendFormat("SELECT [System.Id], [{0}] ", remainingWorkField);
    sb.Append("FROM WorkItems WHERE (");

    // Retrieve the default team for the team project.
    TfsTeamService tfsTeamService = tpc.GetService<TfsTeamService>();
    Guid defaultTeamId = tfsTeamService.GetDefaultTeamId(projectUri);

    // Retrieve the configuration settings for the team.
    TeamSettingsConfigurationService cfg = tpc.GetService<TeamSettingsConfigurationService>();
    var configs = cfg.GetTeamConfigurationsForUser(new[] { projectUri });
    TeamSettings teamSettings = configs.FirstOrDefault(c => c.TeamId == defaultTeamId).TeamSettings;

    const string or = " OR ";
    List<string> conditions = new List<string>();

    // Condition #1: team scopes / work areas.
    var grouped = teamSettings.TeamFieldValues.GroupBy(tfv => tfv.IncludeChildren);
    StringBuilder scopes = new StringBuilder();

    foreach (var group in grouped)
    {
        if (group.Key)
        {
            foreach (TeamFieldValue tfv in group)
                scopes.AppendFormat("[{0}] UNDER '{1}'{2}", teamField, tfv.Value, or);
        }
        else
        {
            string values = string.Join("', '", group.Select(tfv => tfv.Value));
            scopes.AppendFormat("[{0}] IN ('{1}')", teamField, values);
        }
    }

    // Make sure that the condition is complete.
    if (scopes.ToString().EndsWith(or))
        scopes.Remove(scopes.Length - or.Length, or.Length);

    conditions.Add(scopes.ToString());

    // Condition #2: the iteration path.
    conditions.Add(string.Format("[System.IterationPath] = '{0}'", iterationPath));

    // Condition #3: task work item types.
    string taskWorkItemsCategoryName = commonCfg.TaskWorkItems.CategoryName;
    Category taskCategory = project.Categories.FirstOrDefault(c => c.ReferenceName.Equals(taskWorkItemsCategoryName));
    if (taskCategory != null && taskCategory.WorkItemTypes.Any())
    {
        var joinedTasks = string.Join("', '", taskCategory.WorkItemTypes.Select(wit => wit.Name));
        conditions.Add(string.Format("[System.WorkItemType] IN ('{0}')", joinedTasks));
    }

    var states = new[] { StateTypeEnum.Proposed, StateTypeEnum.InProgress };

    // Condition #4: work items that are not done.
    var inProgressStates = commonCfg.TaskWorkItems.States.Where(s => states.Contains(s.Type)).Select(s => s.Value);
    var joinedStates = string.Join("', '", inProgressStates);
    conditions.Add(string.Format("[System.State] IN ('{0}')", joinedStates));

    // Condition #5: the remaining work.
    conditions.Add(string.Format("[{0}] >= 0", remainingWorkField));

    string allConditions = string.Join(") AND (", conditions);
    sb.AppendFormat("({0}))", allConditions);

    return sb.ToString();
}

I know this is quite some amount of code, so we’ll break it down into smaller chunks. To begin with, let’s discuss the method parameters. We pass in the server and project objects, because we’ll need them to retrieve instances to specific services and configuration settings later on. We also pass in an iteration path, for which we want to draw the burndown chart. For simplicity, we are not passing any information that would define which team the chart is for and the default team working on the project will be used. The final parameter, defined with the out modifier, is the remaining work field reference name. We’ll need this field later in the second phase of the algorithm and that’s exactly why we’re returning it, rather then making more trips to the server to read it again later.

Here’s what the code does. We start of by retrieving an instance of the CommonProjectConfiguration object for our team project. This lets us know which work item field is the RemainingWork field, and which one is the Team field. It should be pretty obvious that we need the first field so we can retrieve the amount of work that still needs to be done. The team field, on the other hand, enables us to filter out only the work items that are in the specified teams scope (or work area). The value of this field is by default System.AreaPath.

Knowing the remaining work field allows us to construct the SELECT and FROM clauses of the query expression. The WHERE clause will consist of five separate conditions which we will add together. These conditions will be added as separate strings into a list and will be concatenated later with the SELECT and FROM clauses to produce the complete query expression.

The first condition is the Team filter. Since we can have multiple team field values, and they can either include or not include child items, I’ve opted to first group all the team field values by their IncludeChildren property (it’s a bool) and then construct the actual condition string. When a team field is specified with IncludeChildren = true, we translate it into the query expression using the UNDER operator. Similarly, if I have a group of team fields specified with IncludeChildren = false, we can translate them into a single expression using the IN operator (as opposed to using multiple conditions which are OR-ed together). You can see this on lines 40 through 50 in the above snippet.

The second condition is the iteration path filter and this is a simple one. We just want to filter out any work items that are not in the specified iteration, since we are calculating the burndown only for a single iteration.

The third condition is the work item type filter. Since we are only interested in work items that contain the information about remaining work, we want to include only work items that are in the Task work item type category. Just as a side-note, work item types from this category are also displayed on the task board.

The fourth condition is the work item state filter and it is used to include only work items that are either in-progress or haven’t been started yet. We have no interest in work items that are already done or have been rejected. This makes sense, because only proposed and in-progress work items should have remaining work.

And finally, the fifth condition is used to filter out any work items that either don’t have a remaining work field value set, or the value is equal to zero. This is done merely as an additional optimization, since processing these work items would not affect the actual remaining work values returned in the second phase.

Whoa, let’s see that in action!

Alright, you’ve asked for it! Here is a snippet I’ve used for testing this out on a copy of Brian Keller’s TFS11 virtual machine. Here’s how:

static void Main(string[] args)
{
    #region Input parameters

    Uri tpcUri = new Uri("http://vsalm:8080/tfs/defaultcollection");
    string projectName = "FabrikamFiber";
    string iterationPath = "FabrikamFiber\\Release 1\\Sprint 3";

    #endregion

    var tpc = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(tpcUri);
    tpc.EnsureAuthenticated();

    WorkItemStore wiStore = new WorkItemStore(tpc);
    string rwField;
    Project project = wiStore.Projects[projectName];

    string queryText = GetQueryText(tpc, project, iterationPath, out rwField);
    Console.WriteLine(queryText);
}

And finally, here’s the query expression that gets output to the console. Please note that I’ve added additional line breaks and some whitespace, to make it a bit easier to read:

SELECT [System.Id], [Microsoft.VSTS.Scheduling.RemainingWork] 
FROM WorkItems WHERE 
(
    ([System.AreaPath] IN ('FabrikamFiber')) AND 
    ([System.IterationPath] = 'FabrikamFiber\Release 1\Sprint 3') AND 
    ([System.WorkItemType] IN ('Task')) AND 
    ([System.State] IN ('To Do', 'In Progress')) AND 
    ([Microsoft.VSTS.Scheduling.RemainingWork] >= 0)
)

Pretty sweet, right?

Coming up next…

This is the end of part one. Stay tuned for parts two and three, where we will see how to use the query expression to actually retrieve and process the data needed to draw the burndown chart. Also, we’ll have a look at how the trend lines are calculated and why, all coming up in part three. In the meantime, please do check out the other TFS11 blog posts out there. See you soon!

Be Sociable, Share!
  • Liat

    Again I find those posts very useful. Thanks for posting it.
    I have two questions for this part:
    1. Condition #3: task work item types – Why can’t we just hard code the word ‘Task’? What other alternative can the code that you wrote get?
    2. Can I change this section by creating a store procedure with the query that you presented as the output of your code and execute it?
    Thanks again,
    Liat.

    • http://blog.johnsworkshop.net/ Ivan Popek

      Hi Liat,

      In condition #3, we use work item types from the ‘Task’ category, because you can have multiple WI types that represent a task in your project. You can even localize or rename the existing default Task WI type and this is why hard-coding the type name is not a good idea.

      I don’t believe that you could execute the query “as is” against the TFS database, since I don’t know how the data is “physically” laid out. The problem is that you usually don’t have direct access to the database (and shouldn’t have), so the queries should be executed either using Team Explorer or using the C# client object model. Hope this helps :)

  • Liat

    Hi again,
    I’m trying to implement this code in my box.
    Everything looks good, it find the project, the tpc and the wiStore, but when trying to get the ProjectProcessConfigurationService service it’s empty – please see the attached picture.
    Did I missed something? Is it a settings that needs to be changed on the TFS server?
    Thanks,
    Liat.

    • Liat

      I think I know the reason why the ProjectProcessConfigurationService service is empty – the TFS that I use is TFS2010 and not TFS11. – can this be the issue?
      Thanks.

      • http://blog.johnsworkshop.net/ Ivan Popek

        Hi Liat, sorry for the delay in getting back to you.
        You are correct – you need to have a TFS11 (that is, TFS2012) in order to be able to use the ProjectProcessConfigurationService.

  • http://www.thoughtresults.com Saeed Neamati

    Does this mean that I have to code to create burndown chart? Or is it there, as an out-of-the-box functionality?

  • http://www.facebook.com/martin.varghese.90 Martin Varghese

    I have been goggling the net for examples using TFS api’s for long, but your site is one of the best resource for TFS/TFS-api related stuff. Kudos and keep up the good work.
    Your article on Burndown chart is simply awesome.

    Thank you

    Cheers
    Martin

  • reza

    Hi i can’t see where you use the query retrieved above here, i looked to the next page but i can’t find it there also. Could you please let me know where do you use the query created here above. thanks