Endjin - Home

Querying the VSTS Work Items API directly from Power BI

by James Broome

Power-BI-VSTS

As Partners for Power BI, endjin is doing more and more interesting things with data and visualisations every week. Recently we were asked to develop some custom visualisations based on work item KPI data from inside Visual Studio Team Services (VSTS), which required us to get to grips with the underlying VSTS REST API. This post walks through that process, explaining how to connect to and query VSTS from within Power BI, use Power BI functions to create re-usable sub-queries that can be composed into larger queries and will hopefully leave you with a solid base for developing your own custom VSTS-based Power BI charts and visualisations.

Before we go any further, I should highlight that if you’re just starting out with surfacing your ALM data inside Power BI, there’s already an number of pre-packaged content packs including one for VSTS. This provides a number of incredibly useful visualisations over your Git commits and work items. However, although new reports can be built on top of the underlying data sets that a content pack provides, if there’s data that you need that isn’t exposed in the content pack, there’s not a lot you can do – the content packs are by definition non editable, so what goes on under the covers is a black box.

This was exactly the situation we were in, the data that we needed to support the custom KPIs was not retrieved by the content pack, so we had no choice but to build custom queries ourselves. Another reason for going down this route is that the VSTS content pack is only available in the online version of Power BI – if you want to develop locally with Power BI Desktop, then you’d also be restricted to this approach. Either way, this post will show you that creating custom queries over the VSTS REST API is not only possible, but straightforward, and it will give you a totally flexible solution for retrieving any data that is exposed through the VSTS API.

Overview

The solution described here relies on calling various methods in the VSTS REST API to retrieve detailed work item data, however the same principles could be applied to any of the entities retrievable from the API. Due to the endpoints and operations that are exposed, the following set of steps are followed to surface the data:

  1. Execute a VSTS stored query using the work item query API to retrieve a list of work item IDs
  2. Split the list of work item IDs into groups of 200, which is the maximum batch size that the work items API supports
  3. Call the work items API for each list of 200 work item IDs to get the work item field data
  4. Combine into a single dataset for use in charts and visualisations

Set up the VSTS query

The VSTS API does include an endpoint that allows you to execute “work item query language” as part of the request, meaning dynamic queries could be composed and executed directly from your client application. However, Power BI doesn’t support this – it requires POSTing to an authenticated URI with body parameters, which it deems insecure, as this scenario in REST would typically be used to update or delete data. However, all is not lost, as the work item query endpoint also allows a stored query (i.e. an existing saved VSTS query) to be executed by specifying its ID in the request.

So, the first step is to create, or identify an existing query that you wish to use to retrieve the set of work item IDs that you care about. This query will be the backbone of everything else that happens in Power BI – we’ll be hard coding the query ID into the Power BI query – so all subsequent steps will work on that set of work item IDs. If you intend to create charts or visualisations across a number of iterations, or projects, or areas, then it would be advisable to make this query as wide as possible – grab as much data as you care about in this query so you can then apply the filtering dynamically inside Power BI. However, bear in mind that some VSTS endpoints will only accept a single ID at once, rather than a batch request – for example, if you wanted to retrieve the update history for each work item – and these would require a subsequent request for every ID returned from the original query, which will have a direct impact on performance. The key is finding the right balance of flexibility (by bringing back lots of data into Power BI for filtering), and performance (by only bringing back the data you actually need to limit the number of API calls required) and this will totally depend on your own specific circumstances and data sets.

Creating dynamic VSTS queries

VSTS queryAn alternative way to approach the VSTS query could be to use the dynamic macros inside the VSTS query so that the query itself always returns up to date data – e.g. filtering with Iteration Path = @CurrentIteration means that as you move into new iterations, the query will always return current work item data. Running the VSTS query inside VSTS will show you the data set that you’ll have inside Power BI to use. Once you’ve been through the following steps and wired up Power BI to this query, any subsequent changes to this query will automatically affect the data set retrieved in Power BI when it is refreshed.

Get the VSTS query ID

Whether you create a new query, or use an existing one, you’ll need to obtain the GUID for this query so that it can be used from inside the Power BI query. This can be retrieved from the VSTS API by executing the following request in a browser, or HTTP Client (e.g. Postman):

https://[instance-name].visualstudio.com/DefaultCollection/[project-name]/_apis/wit/queries/[path-to-query]/

Where:

  • [instance-name] is the VSTS instance name
  • [project-name] is the VSTS project name
  • [path-to-query] is the path to the query e.g. “Shared Queries/Current Sprint/Product Backlog

e.g. https://endjin.visualstudio.com/DefaultCollection/PowerBI/_apis/wit/queries/Shared Queries/Current Sprint/Product Backlog/

The response will look something like this, and the value you’re interested in is the first “id” value – in this example it is AC9C8A69-A593-4E7F-BB63-1B93930FEEAC. Make a note of this GUID as you’ll need it in the next step.

Execute the VSTS query from a custom Power BI query

Now that you have a query inside VSTS that returns a list of work item IDs, you can create a Power BI query to execute it. Open Power BI Desktop and add a new custom query through the Get Data | Blank Query option. Name the query “GetWorkItemIds” – (this is important as the subsequent code examples will refer to this query by name) and open the Advanced Editor to edit the contents of the query.

Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code). It creates a Power BI function – i.e. a reusable query that can be called from inside other queries – which calls the VSTS work item query API, specifying the query ID and returning the list of work item IDs.

You’ll need to update the following values in order to execute the function:

  • [instance-name] is the VSTS instance name
  • [project-name] is the VSTS project name
  • [query-guid] the GUID value of the VSTS saved query in the previous step

Close the Advanced Editor to apply the changes, and the query should show as a function inside Power BI with an “Invoke” button – a bit like this:

Invoke Function

Clicking the Invoke button will do just that, and, if all is well you should see a list returned of the IDs of the same work items returned by the VSTS query.

Note – “invoking” a Power BI function inside the query editor adds an extra Applied Step to the query. This causes problems when other queries depend on it, so make sure you remove the Applied Step afterwards each time by clicking the X next to the Invoked FunctionGetWorkItemIds step.

Invoked Function

Authenticating with the VSTS service

When the function is invoked, you will need to authenticate against the VSTS service, using either Basic, Organisational Account, or oAuth authentication, depending on what is supported in your VSTS service.

Two Factor authentication is not supported in Power BI yet for this type of data source, but Basic authentication can be used by setting up Alternative Authentication Credentials inside VSTS Security Profile and specifying the username and password.

Which ever authenticated method you choose to use, make sure that you set the authentication credentials to apply at the top level domain level, so that all other VSTS queries against the same API will be automatically authenticated.

Use the list of IDs to get the work item details

Now that you’re retrieving the list of work item IDs, the following steps use that list to call additional VSTS APIs to retrieve specific pieces of data about the work item. In this scenario, we’re retrieving all the primary work item fields available, but deeper API calls can be made to retrieve collections of child data about a specific work item – e.g. all the history records as the work item has changed over time, or all the linked work items. In those cases, you’d be calling the API once per work item ID but if you’re querying the work items API, you have the ability to request the details for a list of up to 200 work item IDs at a time.

Paging

The next query to create is another function that handles this requirement to page the list of work items IDs into groups of 200. Use Get Data | Blank Query to create a new query and open the Advanced Editor. Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code) and name the function “GetWorkItemIdsPages” – again, the name is important as the functions are referenced by name from other queries.

Calling work items API

Once you have the paging function in place, you can add a third function to actually call into the work items API to retrieve the details – for every work item ID in a list, batched into pages of 200. Use Get Data | Blank Query again and open the Advanced Editor. Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code) and name the function “GetWorkItems” – again, the name is important as the functions are referenced by name from other queries.

You’ll need to update the following values in order to execute the function:

  • [instance-name] is the VSTS instance name

Combining the queries to return the work item data

Finally, you now need to add the last query that ties everything together – using the GetWorkItemIds function to retrieve the master list of IDs, passing that into GetWorkItems, which subsequently uses GetWorkItemsIdsPages to slice up the list of IDs into pages of 200 before calling into the work items API to retrieve the work item data fields.

Use Get Data | Blank Query again and open the Advanced Editor. Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code) and name the query “WorkItems”. This query isn’t created as a function – it’s a regular Power BI query that will return and display data that can be used in charts and visualisations in the reports view.

At this point, you should be able to see the work item field data in the query view, as below. This data set can now be used in the report builder view to create charts and visualisations over the work item data. The code used in the above sample includes all the “System.X” fields from the VSTS API, but there will be additional fields available depending on the work item template you use and any customisations you may have made. Now that you have the data in the WorkItems query, formatting the values, renaming the columns, adding calculated fields is all possible as with any Power BI data source.

Work Items

Next steps

The steps above were deliberately broken down into reusable functions so that this solution can be extended further according to your own requirements. The root of it all – the GetWorkItemsIds function- can be reused to retrieve the list of work item IDs, which could then be passed into any of the other VSTS APIs to retrieve different data attributes of the work item either individually or in batches using additional functions.

At a higher level, the same approach – querying for IDs, batching into pages and executing subsequent API calls – can be applied to retrieve other data types via the other entity API endpoints in VSTS, or entirely different REST APIs that follow a similar pattern.

 

 

About the author

James has spent the last 15 years delivering high quality software solutions addressing global business problems, with teams and clients across 3 continents. He now leads the engineering team at endjin, which provides technology strategy, insight and engineering services to its clients who are seeking to take advantage of Microsoft Azure and the Cloud.