Behaviour Driven Spark

Spark is a big deal these days, people are using this for all sorts of exciting data wrangling. There’s a huge trend for ease of use within the Spark community and with tools like Apache Zeppelin coming onto the scene the barrier to entry is very low. This is all good stuff: open source projects live and die in the first half an hour of use. New users need to get something cool working quickly or they’ll get bored and wander off…

But for those of us who got past Hello World some time ago and are now using Spark as the basis of large and important projects there’s also the chance to do things right. In fact, since Spark is based on a proper language (Scala, not R or python please!) it’s a great chance to bring some well established best practices into a world where uncontrolled script hackers have held sway for too long!

Check out the source for this article on my GitHub:


Behaviour Driven Development, or BDD, is a bit like unit testing. Like unit testing done by an experienced master craftsman. On the surface they look the same – you write some “test” code which calls your production code with known inputs and checks the outputs are what you want them to be. It can be run from your IDE and automated in your CI build because it uses the same runner as your unit tests under the hood.

For me, TDD and BDD differ in these two critical ways: BDD tests at the right level; because you’re writing “Specifications” in pseudo-English not “Tests” in code you feel less inclined to test every function of every class. You test at the external touch-points of your app (load this data, write to this table, show this on the UI), which makes your tests less brittle and more business oriented. Which leads to the second difference: BDD specs are written in Cucumber, a language easily accessible to less techie folks like testers, product owners and stakeholders. Because Cucumber expresses business concepts in near-natural language, even your Sales team have a fighting chance of understanding it… well, maybe.

Project Setup

Before we can crack on and write some Cucumber, there is some setup to be done in the project. I am using IntelliJ, but these steps should work for command line SBT also.

First job, get build.sbt set up for Spark and BDD:

name := "spark-bdd-example"

version := "1.0"
scalaVersion := "2.10.6"

libraryDependencies ++= Seq(
  "log4j" % "log4j" % "1.2.14",
  "org.apache.spark" %% "spark-core" % "1.6.0",
  "org.apache.spark" %% "spark-sql" % "1.6.0",
  "org.apache.spark" %% "spark-mllib" % "1.6.0",
  "org.json4s" %% "json4s-jackson" % "3.2.7",
  "info.cukes" % "cucumber-core" % "1.2.4" % "test",
  "info.cukes" %% "cucumber-scala" % "1.2.4" % "test",
  "info.cukes" % "cucumber-jvm" % "1.2.4" % "test",
  "info.cukes" % "cucumber-junit" % "1.2.4" % "test",
  "junit" % "junit" % "4.12" % "test",
  "org.scalatest" %% "scalatest" % "2.2.4" % "test"

For this example I am wrapping Spark up in an object to make it globally available and save me mocking it out “properly”. In a production app, where you need tighter control of the options you pass to spark, you might want to mock it out and write a “Given” to spin Spark up. Here’s my simple object in Spark.scala:

object Spark {
  val conf = new SparkConf()
    .setAppName("BDD Test")
    .set("spark.default.parallelism", "8")
    .set("spark.sql.shuffle.partitions", "8")

  val sc = new SparkContext(conf)

  val sqlContext = new SQLContext(
  sqlContext.setConf("spark.sql.shuffle.partitions", "8")

If using IntelliJ, like me, you’ll also need a test class to run your cucumber. Mine’s in Runtests.scala. Right click on this and select “Run tests” from the context menu and it’ll run the tests.

class RunTests extends {

If using the command line, add this line to project/plugins.sbt:

addSbtPlugin("com.waioeka.sbt" % "cucumber-plugin" % "0.0.3")

And these to build.sbt:

CucumberPlugin.glue := ""

First Very Simple Example

Here’s the first bit of actual cucumber. We’re using it for a contrived word-counting example here. The file starts with some furniture, defining the name of the Feature and some information on it’s purpose, usually in the format In order to achieve some business aim, As the user or beneficiary of the feature, I want some feature.

Feature: Basic Spark

  In order to prove you can do simple BDD with spark
  As a developer
  I want some spark tests

  Scenario: Count some words with an RDD
    When I count the words in "the complete works of Shakespeare"
    Then the number of words is '5'

The rest of the file is devoted to a series of Scenarios, these are the important bits. Each scenario should test a very specific behaviour, there’s no limit to the number of scenarios you can define, so take the opportunity to keep them focussed. As well as a descriptive name, each scenario is made of a number of steps. Steps can be Givens, Whens or Thens.

  • Given some precondition“: pre-test setup. Stuff like creating a mock filesystem object, setting up a dummy web server or initialising the Spark context
  • When some action“: call the function you’re testing; make the REST call, whatever
  • Then some test“: test the result is what you expected

Step Definitions

Each step is bound up to a method as shown in the “Steps” class below. When the feature file is “executed” the function bound to each step is executed. You can pass parameters to steps as shown here with the input string and the expected number of words. You can re-use steps in as many scenarios and features as you like. Note that the binding between steps and their corresponding functions is done with regular expressions.

class SparkSteps extends ScalaDsl with EN with Matchers {
  When("""^I count the words in "([^"]*)"$"""){ (input:String) =>
    Context.result =' ')).count().toInt

  Then("""^the number of words is '(\d+)'$"""){ (expected:Int) =>
    Context.result shouldEqual expected

The Context

The Context object here is used to store things… any variables needed by the steps. You could use private fields on the step classes to achieve this, but you’d quickly encounter problems when you began to define steps over multiple classes.

object Context {
  var result = 0

I don’t particularly like using a Context object like this, as it relies on having vars, which isn’t nice. If you know a better way, please do let me know via the comments box below!

Data Tables

So the word counting example above shows how we can do BDD with spark – we pass in some data and check the result. Great! But it’s not very real. The following example uses Spark DataFrames and Cucumber DataTables to do something a bit more realistic:

  Scenario: Joining data from two data frames to create a new data frame of results
    Given a table of data in a temp table called "housePrices"
      | Price:Int  | Postcode:String | HouseType:String |
      | 318000     | NN9 6LS         | D                |
      | 137000     | NN3 8HJ         | T                |
      | 180000     | NN14 6TN        | S                |
      | 249000     | NN14 6TN        | D                |
    And a table of data in a temp table called "postcodes"
      | Postcode:String | Latitude:Double | Longitude:Double |
      | NN9 6LS         | 51.1            | -1.2             |
      | NN3 8HJ         | 51.2            | -1.1             |
      | NN14 6TN        | 51.3            | -1.0             |
    When I join the data
    Then the data in temp table "results" is
      | Price:Int  | Postcode:String | HouseType:String | Latitude:Double | Longitude:Double |
      | 318000     | NN9 6LS         | D                | 51.1            | -1.2             |
      | 137000     | NN3 8HJ         | T                | 51.2            | -1.1             |
      | 180000     | NN14 6TN        | S                | 51.3            | -1.0             |
      | 249000     | NN14 6TN        | D                | 51.3            | -1.0             |

You only need to write the code to translate the data tables defined in your cucumber to data frames once. Here’s my version:

class ComplexSparkSteps extends ScalaDsl with EN with Matchers {
  def dataTableToDataFrame(data: DataTable): DataFrame = {
    val fieldSpec = data
      .map(splits => (splits(0), splits(1).toLowerCase))
      .map {
        case (name, "string") => (name, DataTypes.StringType)
        case (name, "double") => (name, DataTypes.DoubleType)
        case (name, "int") => (name, DataTypes.IntegerType)
        case (name, "integer") => (name, DataTypes.IntegerType)
        case (name, "long") => (name, DataTypes.LongType)
        case (name, "boolean") => (name, DataTypes.BooleanType)
        case (name, "bool") => (name, DataTypes.BooleanType)
        case (name, _) => (name, DataTypes.StringType)

    val schema = StructType(
        .map { case (name, dataType) =>
          StructField(name, dataType, nullable = false)

    val rows = data
      .asMaps(classOf[String], classOf[String])
      .map { row =>
        val values = row
          .map { case (v, (fn, dt)) => (v, dt) }
          .map {
            case (v, DataTypes.IntegerType) => v.toInt
            case (v, DataTypes.DoubleType) => v.toDouble
            case (v, DataTypes.LongType) => v.toLong
            case (v, DataTypes.BooleanType) => v.toBoolean
            case (v, DataTypes.StringType) => v


    val df = Spark.sqlContext.createDataFrame(, schema)

  Given("""^a table of data in a temp table called "([^"]*)"$""") { (tableName: String, data: DataTable) =>
    val df = dataTableToDataFrame(data)


Likewise, you can define a function to compare the output data frame with the “expected” data from the cucumber table. This is a simple implementation, I have seen some much classier versions which report the row and column of the mismatch etc.

  Then("""^the data in temp table "([^"]*)" is$"""){ (tableName: String, expectedData: DataTable) =>
    val expectedDf = dataTableToDataFrame(expectedData)
    val actualDf = Spark.sqlContext.sql(s"select * from $tableName")

    val cols =

    val expected =, cols.tail: _*)
    val actual =, cols.tail: _*)

    println("Comparing DFs (expected, actual):")

    actual.count() shouldEqual expected.count()
    expected.intersect(actual).count() shouldEqual expected.count()

Coverage Reporting

There’s a great coverage plugin for Scala which can easily be added to the project by adding a single line to plugins.sbt:

logLevel := Level.Warn

addSbtPlugin("com.waioeka.sbt" % "cucumber-plugin" % "0.0.3")
addSbtPlugin("org.scoverage" % "sbt-scoverage" % "1.3.5")

The report is generated with the following SBT command and saved to HTML and XML formats for viewing or ingest by a tool (like SonarQube).

$ sbt clean coverage cucumber coverageReport


[info] Written Cobertura report [/Users/DTAYLOR/Development/bdd-spark/target/scala-2.10/coverage-report/cobertura.xml]
[info] Written XML coverage report [/Users/DTAYLOR/Development/bdd-spark/target/scala-2.10/scoverage-report/scoverage.xml]
[info] Written HTML coverage report [/Users/DTAYLOR/Development/bdd-spark/target/scala-2.10/scoverage-report/index.html]
[info] Statement coverage.: 94.69%
[info] Branch coverage....: 100.00%
[info] Coverage reports completed
[info] All done. Coverage was [94.69%]
[success] Total time: 1 s, completed 08-Aug-2016 14:27:17

Screenshot 2016-
08-08 14.29.12


So, hopefully this long and rambling article has made one key point: You can use BDD to develop Spark apps. The fact that you should isn’t something anyone can prove, it’s just something you’ll have to take on faith!


Quick TeamCity Build Status with AngularJS

So, this isn’t supposed to be the ultimate guide to AngularJS or anything like that – I’m not even using the latest version – this is just some notes on my return to The World of the View Model after a couple of years away from WPF. Yeah, that’s right, I just said WPF while talking about Javascript development. They may be different technologies from different eras: one may be the last hurrah of bloated fat-client development and the other may be the latest and greatest addition to the achingly-cool, tie dyed hemp tool belt of the Single Page App hipster, but under the hood they’re very very similar. Put that in your e-pipe and vape it, designer-bearded UX developers!


Anyway, when I started, I knew nothing about SPA development. I’d last done JavaScript several years ago and never really used it as a real language. I still contend that JavaScript isn’t a real language (give me Scala or C# any day of the week) but you can’t ignore the fact that this is how user interfaces are developed these days… so, yeah, I started with a tutorial on YouTube.

I decided to do an Information Radiator to show build status from TeamCity on the web. Information Radiators are my passion – at least they’re one of the few passions I’m allowed to pursue at work – and we use Team City for all our continuous integration, release builds, automated tests and so on. Our old radiators are coded in WPF, which looks awesome on the big TVs dotted around the office, but doesn’t translate well for remote workers.

There is no sunshine and there are no rainbows in this article. I found javascript to be a hateful language, filled with boilerplate and confusion. Likewise, though TeamCity is doubtless the best enterprise CI platform on planet earth, the REST APIs are pretty painful to consume. With that in mind, let’s get into the weeds and see how this thing works…

Enable cross-site scripting (CORS) on your Team City server

You can’t hit a server from a web page unless that server is the server that served the web page you’re hitting the server with… unless of course you tell the server you want to hit that the web page you want to hit it with, served from a different server, is allowed to hit it. Got that? Thought so. This is all because of a really logical thing called “Cross Origin Resource Sharing”, which you can enable pretty easily in TeamCity as long as you have admin permissions.

Check out Administration -> Server Administration -> Diagnostics -> Internal Properties. From there you should be able to edit, or at least get the location of the file. Weirdly, if the file doesn’t exist, there is no option to edit, so you have to go and create the file. Since my TeamCity server is running on a Windows box, I created the new file here:


and added the following:*

You might want to be a little more selective on who you allow to access the server this way – I guess it depends on how secure your network is, how many clients access the dashboard and so on.

Tool Chain

This article is about AngularJS and it’s about TeamCity. It’s not about NPM or Bower or any of that nonsense. I’m not going to minify my code or use to crazy new-fangled pseudo-cosmic CSS. So setting up the build environment for me was pretty easy: create a folder, add a file called “index.html”, fire up the fantastic Fenix Web Server and configure it to serve up the folder we just created. Awesome.

If you’re already confused, or if you just want to play with the code, you can download the lot from GitHib:

I promise to do my best

Hopefully you’ve watched the video I linked above, so you know the basics of an AngularJS app. If not, do so now. Then maybe Google around the subject of promises and http requests in AngularJS. Done that? OK, good.

Web requests take a while to run. In a normal app you might fetch them on another thread but not in JavaScript. JavaScript is all about callbacks. A Promise is basically a callback that promises to get called some time in the future. They are actually pretty cool, and they form the spinal column of the build status app. This is because the TeamCity API is so annoying. Let me explain why. In order to find out the status (OK or broken) and state (running, finished) of each build configuration you need to make roughly six trillion HTTP requests as follows:

  1. Fetch a list of the build configurations in the system. These are called “Build Types” in the API and have properties like “name”, “project” and “id”
  2. For each Build Type, make a REST request to get information on the latest running Build with a matching type ID. This will give you the “name”, “id” and “status” of the last finished build for the given Build Type.
  3. Fetch a list of the currently running builds.
  4. Use the list of finished builds and the list of running builds to create a set of status tiles (more on this later)
  5. Add the tiles to the angular $scope and let the UI render them

Here’s how that looks in code. Hopefully not too much more complicated than above!

	.then(function(responses) {
		$scope.buildResponses = responses
			.filter(function(r) { return (r.status == 200 && > 0)})
			.map(function(r){ return[0] })
	.then(function(data) {
		$scope.runningBuilds = { return row.buildTypeId })
	.then(function() {
		$scope.builds = $ { return buildFactory.decodeBuild(b, $scope.runningBuilds); });
	.then(function() {
		$scope.tiles = buildFactory.generateTiles($scope.builds)
	.then(function() {
		$scope.statusVisible = false;

Most of the REST access has been squirrelled away into a factory. And yes, our build server is called “tc” and guest access is allowed to the REST APIs and I have enabled CORS too… because sometimes productivity is more important than security!

angular.module('buildApp').factory('buildFactory', function($http) {
	var factory = {};
	var getBuildTypes = function() {
		return $http.get('http://tc/guestAuth/app/rest/buildTypes?locator=start:0,count:100');
	var getBuildStatus = function(id) {
		return $http.get('http://tc/guestAuth/app/rest/builds?locator=buildType:' + id + ',start:0,count:1&fields=build(id,status,state,buildType(name,id,projectName))');
	factory.getRunningBuilds = function() {
		return $http.get('http://tc/guestAuth/app/rest/builds?locator=running:true');

// etc

Grouping and Tiles

We have over 100 builds. Good teams have lots of builds. Not too many, just lots. Every product (basically every team) has CI builds, release/packaging builds, continuous deployment builds, continuous test builds, metrics builds… we have a lot of builds. Builds are good.

But a screen with 100+ builds on it means very little. This is an information radiator, not a formal report. So, I use a simple (but messy) algorithm to convert a big list of Builds into a smaller list of Tiles:

  1. Take the broken builds (hopefully not many) and turn each one into a Tile
  2. Take the successful builds and group them by “project” (basically the category, which is basically the team or product name)
  3. Turn each group of successful builds into a Tile, using the “project” as the tile name
  4. Mark any “running” build with a flag so we can give feedback in the UI


Displaying It

Not much very exciting here. I used Bootstrap, well, a derivative of Bootstrap to make the UI look nice. I bound some content to the View Model and that’s about it. Download the code and have a look if you like.

Here’s my index.html (which shows all the libraries I used):

<html ng-app="buildApp">
  <title>Build Status</title>
  <link href="" rel="stylesheet">
  <!--link href="" rel="stylesheet"-->

  <div ng-view>

  <script src=""></script>
  <script src=""></script>
  <script src=""></script>
  <script src=""></script>
  <script src="utils.js"></script>
  <script src="app.js"></script>
  <script src="build-factory.js"></script>

Here’s the “view” HTML for the list (in “templates/list.html”). I love the Angular way of specifying Views and Controllers by the way. Note the cool animated CSS for the “in progress” icon.

	.glyphicon-refresh-animate {
		-animation: spin 1s infinite linear;
		-webkit-animation: spin2 1s infinite linear;

	@-webkit-keyframes spin2 {
		from { -webkit-transform: rotate(0deg);}
		to { -webkit-transform: rotate(360deg);}

	@keyframes spin {
		from { transform: scale(1) rotate(0deg);}
		to { transform: scale(1) rotate(360deg);}
	<div class="page-header">
		<h1>Build Status <small>from TeamCity</small></h1>
    <div class="container-fluid">
		<div class="row">
    		<div class="col-md-3" ng-repeat="tile in tiles | orderBy:'status' | filter:nameFilter">
        		<div ng-class="getPanelClass(tile)">
               <h5><span ng-class="getGlyphClass(tile)" aria-hidden="true"></span>   {{ | limitTo:32 }}{{ > 32 ? '...' : ''}}   {{ tile.buildCount > 0 ? '(' + tile.buildCount + ')' : ''}} </h5>
               <p class="panel-body">{{ tile.project }}</p>
  <nav class="navbar navbar-default navbar-fixed-bottom">
  <div class="container-fluid">
    <p class="navbar-text navbar-left">
		<input type="text" ng-model="nameFilter"/>  <span class="glyphicon glyphicon-filter" aria-hidden="true"></span>  
		<span class="glyphicon glyphicon-refresh glyphicon-refresh-animate" ng-hide="!statusVisible"></span>

That’s about it!

I think I summarized how I feel about this project in the introduction. It looks cool and the MVC MVVM ViewModel vibe is a good one. The data binding is simple and works very well. All my gripes are with JavaScript as a language really. I want Linq-style methods and I want classes and objects with sensible scope. I want less syntactic nonsense, maybe the odd => every now and again. I think some or all of that is possible with libraries and new language specs… but I want it without any effort!

One thing I will say: that whole page is less than 300 lines of code. That’s pretty darned cool.

Feel free to download and use the app however you like – just bung in a link to this page!


Build Status Traffic Lights

Recently I got the time to knock up a set of build status traffic lights for the office. It’s likely that I am the world’s greatest fan of Continuous Integration. I’m not going to bang on about why it’s a good idea here, suffice it to say that anyone who isn’t rabidly devoted to the greenness of the build will surely pay the price in time.

2013-07-21 21.48.54

The lights themselves are from eBay. They were 24v and fitted with huge great bulbs which left no room inside for anything else. I swapped these out for some 12v LED brake light bulbs, which are fitted into some DIY holders made of nylon bar and odds and sods. Looking back, I’d have just soldered a load of LEDs to a circle of stripboard, but I went with what I had at the time.

2013-07-21 21.56.09

The lights are switched by two of these great little relay boards. Each one comes assembled and ready to go – they just need connections for 5v, ground and signal. If I’d have gone with the DIY LEDs-on-stripboard design I guess I could have used a transistor circuit but I do love the loud mechanical clunk that the relays make when the lights change. It adds to the antique feel of the project. I did use stripboard to make a “shield” to connect the relay cables to an old Arduino I had knocking about.

It’s worth noting that you can get an Arduino relay shield (and I do in fact have one in the garage) but it seemed like overkill to use such an expensive board, with twice as many relays as I needed.

Power for the lamps is supplied by a 12v wall adaptor I got from Maplins. Again, a custom LED solution would have allowed me to use the 5v USB supply… but hindsight is richer than I am. I installed a line socket for the power, so when the PAT testing man comes round the office he won’t test the lights, just the wall supply.

2013-07-22 22.27.19

The arduino inside the lights implements a very simple serial protocol. It listens for commands “red”, “green” and “off”, terminated with a newline. There’s a USB connection to the old laptop which drives our Information Radiator TV; the idea with the traffic lights was to keep all the intelligence on the PC end to make upgrades and changes easier. Here’s the arduino code. Told you it was simple!

const int redPin = 2;
const int greenPin = 3;
int redState = LOW;
int greenState = LOW;
long interval = 1000;
String inputString = "";
boolean stringComplete = false;

void setup() {
  pinMode(redPin, OUTPUT);      
  pinMode(greenPin, OUTPUT);    


void loop()
  if(stringComplete) {
    stringComplete = false;

    if (inputString.equalsIgnoreCase("off")) {
      redState = LOW;
      greenState = LOW;
    else if (inputString.equalsIgnoreCase("red")) {
      redState = HIGH;
      greenState = LOW;
    else if(inputString.equalsIgnoreCase("green")) {
      redState = LOW;
      greenState = HIGH;

    inputString = "";

  digitalWrite(redPin, redState);
  digitalWrite(greenPin, greenState);

void serialEvent() {
  while (Serial.available()) {
    char inChar = (char);

    if (inChar == '\n') {
      stringComplete = true;
    else if(inChar != '\r') {
      inputString += inChar;

The code on the PC end is a little more complex, but all the heavy lifting is done by Team City Sharp which connects to our Team City server and get the status of our multitude of builds. The only other complicated thing it does is open a serial port and dump the commands “red” and “green” to show the build status. It also sends “off” at 7 o’clock in the evening… just in case a red light shining from an office window at midnight were to attract the attention of the local constabulary.

2013-08-27 14.15.19

Dan Dan The Kanban Man

Just finished a new page for my Information Radiator. This one shows the progress of user stories through the process. We do scrum and this is a kanban-style board so I’m mixing things up a little bit but I think it’s fine.

Most Salesmen understand waterfall. They promise a feature to a customer within set timescales to get cash. That’s what salesmen will always do. Of course, life gets in the way and something pushes their new feature back. In waterworld they get a nice new Gantt chart which shows what’s been added into the project, they understand why it’s there and accept it. The Kanban board is an agile answer to the same question: “Why are you not working on the trans-galactic hyperdrive I promised? Oh, it’s because you’re fixing a radiation leak in the cosmic flange-o-tron. I agree that’s important and I guess you should fix it before you do my feature”.


The data is pulled from TFS. I don’t differentiate between development and QA on the board because I think it sends the wrong message – we’re just one big team, after all!


Hopefully this board will help those outside of the development and product teams get a better understanding of what we’re working on now, what we’ve finished, what we’re doing next and why. Knowing the answers to those questions gives people a warm and fuzzy glow inside and helps ease inter-departmental tensions.

Tracking Kanban with TFS

Kanban is a great way to manage your bug backlog.  It’s much better than Scrum simply because of the nature of bugs as compared to user stories. Scrum is all about making firm commitments based on estimates but bugs are very hard to estimate up-front. Generally when you’ve looked hard enough into the code to find the problem, you are in a position to fix it very quickly. Bug fixing is essentially a research task – like a spike – so time-boxing the work makes much more sense.

Set up a prioritised backlog and blast off the top as many bugs as possible in the time you’ve set aside – Kanban Style.  This works very well but, as with most agile approaches, it leaves old fashioned managers a bit grumpy.  They want to track your productivity and it’s fair to say that you should too because that’s how you spot impediments (plus it’s always good to show off).

Scrum-style burn downs don’t work with Kanban because they track progress against some committed target.  The answer is the Cumulative Flow Diagram:


So I did some tweaking to my Information Radiator to add a page showing the CFD for the last 60 days of one of our projects.  The data comes out of TFS via the C# API and a WIQL query – which has a very nice historical query feature which I’ll explain below.

Cumulative Flow Diagrams Explained

Cumulative flow diagrams couldn’t be simpler.  Like a burn-up chart they show a running total of the bugs fixed over time.  Since bugs aren’t estimated, the Y axis shows the bug count.  In the chart above the X axis is in days but I guess you could do weeks or even hours if you like.  In addition to the “fixed bugs” series, there are also stacked series for other states: “committed”, “in development” and “in QA”.

The benefit of showing the other issue states is that it gives you a readout on how the process is working.  The QA and development series should generally be the same thickness.  If the QA area gets fatter than the development area then you have a bottleneck in QA.  If the development series gets too fat then you’re spread too thinly – you have an impediment in development or need to think about your Kanban limit.

Note how there are a couple of “steps” on the left of my graph.  Those correspond to the first couple of sprints in which we used TFS. The team weren’t familiar with it, so work item states were generally changed at the end of the sprint.  As time went on we got better at updating the system and the steps turned into a nice looking slope.

Historical Queries in TFS 2012

It’s not every day that I openly applaud Microsoft for doing something brilliant and until now I’ve never been that cheerful about TFS.  But… the historical querying in WIQL (work item query language) is bloody brilliant!

Drawing a CFD chart depends on an ability to get the historical state of any issue in the system at a specified point in time.  In WIQL this is done using the “AsOf” keyword:

Select [ID], [Title], [Effort - Microsoft Visual Studio Scrum 2_0], [Assigned To]
From WorkItems
  [Team Project] = 'Project'
  [Work Item Type] = 'Bug'
  [Iteration Path] under 'Project\Release'
AsOf '21/01/2013'

So the algorithm for drawing the CFD is pretty simple:

  • Grab the sprints for the project in question and use them to get the start and end dates for your chart
  • For each day on the X axis
    • Run a WIQL statement to get the state of all the bugs in the project on that date
    • Use linq to count issues in the various states you’re showing on the graph series
    • Populate a list of view model/data objects (one for each X value)
  • Throw the values at the chart

The only complications were the fact that the WPF Toolkit chart doesn’t support stacked area series (so I had to do it myself in the view model) and that getting data on group membership from TFS is very hard and very slow (so I build a cache of dev and QA group members up front and do comparisons on the display name).