Team Foundation Server OData Feed via Power Query

Did you know that you can pull data from your Team Foundation Server through an OData project.  Yep.  Here's the link to get started:


I inherited a project to do just that.  The client was using the Excel plug-in for TFS, yet the process was manual.  My task was to pull data from the OData feed through Power Query, to build a Power Pivot Model and have reports and dashboards based on that.

After connecting to TFS, there were three objects we were going after, Features, User Story and Task.  So we found code on the web to recursively pull the data.  Because the Feature has a Links column, which you can expand, which provides links to other Parent records, Child records, and others.

So once you have that Id, you call a function to grab that record, and do the same to get the Task.

However, there were performance issues, taking many hours to run before timing out.

So after rearchitecting the Power Query, there were now 3 separate calls to TFS, one for Feature, one for User Story and one for Task.  Then doing a Left Outer Join to pull them all together.  The trick was to only go after the Child records.  And that ran in about 11 minutes.

But then there was another problem. It turns out that the query they used in the TFS Plug in for Excel had fields not contained in the WorkItems object.

After scanning the web for a long time, determined it was not possible, yet I needed someone to agree.  So I took a chance and emailed someone from Microsoft, and believe it or not, they responded within an hour.  And yes the fields we needed are not exposed and they do not support the product or have the resources to expand the functionality.

So after letting the Project Manager and my boss know, it was decided to build out the Visual Studio project ourselves and add the fields.  So a .net developer was brought in, got up to speed, and we worked together to add the fields, which took less than a day.

So as things go in programming, there's always one issues after another.  And the next issue was, I pulled a report from the Excel TFS Plug in, the computer hung, shut down and never came back.  Logging in as admin, was able to pull the needed files to a thumb drive, drove to the office, picked up another laptop.  Was down for about 7 hours getting the new laptop in working condition.

The next problem was the OData Visual Studio project would not compile on the new laptop.  The error was a conflict in DLL, as there were 2 TFS dlls with the same method.  After researching, changed the reference in the VS 2013 project to point to the GAC instance of the DLL and now the project compiled.  The next problem was the localhost page would not render from the VS iis, but recreating the Virtual Web solved that issue.

At 10:30 last night, the Power Query was pulling data, through VPN client, from the local Visual Studio project, pointing at the client's TFS server and pulling data within 30 minutes.  And that includes the 25 new fields added to the VS project that aren't included in the WorkItems object.

Now, all that's left is to build the 8 Pivot table reports.  Validate the data.  And ship.

This project offered many challenges in addition to doing .net again after years layoff as well as writing complex code in Power Query "M" language.

Things are looking up~!

No comments:

Post a Comment