Three ways to query Azure DevOps using Power BI

Gathering and analysing data from Azure DevOps and presenting it in different, visual, ways within Power BI can assist scrum masters, project managers and development teams in planning and monitoring project progress.

What are some of the tools available to analyse and visualise Azure DevOps data in Power BI?

Built-in Power BI connector

Power BI offers two built-in connectors for Azure DevOps: Azure DevOps (Boards Only) and Azure DevOps Server (Boards Only). These services, formerly called Visual Studio Team Services (VSTS) and Team Foundation Server (TFS) respectively offer the simplest method to import data into Power BI for reporting.

This connector requires the Organization name and Team project name to connect to the correct boards for import. The data returned is dependent on the default analytics views built into Azure DevOps, so there may be some pre-work required.

The default Analytics data returned is high-level per Bug, Story, Task or Work item, so while this approach is the quickest and easiest to get Azure DevOps data into Power BI, it may not be the beast approach to develop rich analytical reports on your project as is.

More information on connecting and importing the data using this approach can be found on these Microsoft learn pages:

OData Feed connector

Azure DevOps contains a built-in reporting platform called Analytics, providing fast read-access and server based integrations to answer questions about your project data. This is the same tool providing the data to the Built-in Power BI connector mentioned above.

To import this data into Power BI you use the OData Feed connector. Have the name of your organization and project as they appear in DevOps on hand to connect to your DevOps boards, Pipelines, Test plans, Repos and Artifacts.

The query is slightly different between the cloud-based Azure DevOps and the on-premises (Azure DevOps Server) instances. Below is the breakdown of the Azure DevOps Service (cloud) structure.

While this data does use the same Analytics views as the built-in Power BI connector, it does offer a more customizability in the initial URL query to get a finer grain of data to develop your DevOps report in Power BI. This added customisability does come with a slight drawback in the slightly more technical skill required in data extraction.

Additional information on connecting and importing the data using the OData connector can be found on these Microsoft learn pages:

REST API

As a brief introduction, an API is an intermediate software agent that allows two applications to communicate with each other. APIs provide a set of protocols, rules, and developer tools that enable software developers to extract and share information and create applications that can interact with each other.

Using the Azure DevOps REST API in Power BI is the most technical of the approaches to extract data out of Azure DevOps, but it can provide a level of detail not available through other methods.

The Azure DevOps API supports Microsoft Authentication Library, OAuth, and Session tokens as ways of authentication to accommodate various developer languages and scenarios. The simplest approach for Power BI is to create a Personal Access Token (PAT) in Azure DevOps to use for your report authentication.

Customising the query and making it more dynamic for your requirements can be done once an initial connection has been made and you have confirmed that you can get data. The authentication method in Power BI to the REST API will be Basic, using the PAT you have created as the username and password for the connection.

Additional information on REST API can be found on these Microsoft learn pages:

Which method should you use?

The approaches to connect to your DevOps board data in this article has been listed from simplest to most complex.

For most requirements (and for general Power BI development sanity) using the built in connector or OData feed connector are ideal. Although the API can possibly provide a smidgen more information over the other two approaches, using API’s in Power BI Desktop (and the Power BI service) adds large levels of complexity to your project. Only you can decide if the extra data you can pull is worth the additional effort.

Happy developing.