How to use Azure Devops data within PowerBI

Florian CAILLAUD
Published by Florian CAILLAUD
Category : Azure / DevOps
06/07/2021

Introduction

 

When using Azure DevOps, we generate a lot of data. This can include scheduling data as well as data on the team or the various pipelines set up.

It would be a shame not to use this information to improve the management of our Azure DevOps project. In this article, we suggest using Power BI software to format this data.

 

Creating a Power BI report

 

The very first thing to do is obviously to create a Power BI report using Power BI Desktop. This link describes how to start: https://docs.microsoft.com/en-us/power-bi/create-reports/.

To put graphs describing your data into this report, you must define one or more data sources. Azure DevOps allows you to retrieve the information in 3 different ways:

  • Analytics Views
  • OData queries
  • Azure DevOps API

In this article, we will explain how to create and use each of these 3 sources.

 

Retrieval via Analytics Views

This type of source allows you to retrieve the data image on the work elements in regular time slices (and not the whole history).

To use the Analytics Views part, the first step is to create a view by going to the desired Azure DevOps project in “Overview/Analytics Views*”.

 

AnalyticsViews

 

Views for standard requirements are already available. If necessary, you can create a new view by defining filters (on work elements, areas, etc.), the time period to be applied, and the desired fields. Note: for a view to be used in Power BI, it must be defined as “Shared”.

When the view is available in Power BI, simply declare a new data source via the Azure DevOps connector (by going in to the “Home” ribbon and then “Get Data”).

 

PowerBiADOConnector

 

To configure the connector, you only need the name of the organization and the name of the Azure DevOps project. However, you have to log in (either with Microsoft identifiers or a Personal Access Token). After authentication, Power BI lists the available views.

 

PowerBiAnalyticsViews

 

In our case, I choose the “Work Items – All history by month” view. I then click “Load” to see the new data source displayed with the desired fields:

 

PowerBiDataSource

Retrieval via OData

The range of data is wider for data retrieval via OData queries. It will be possible to access information on work items (in regular time slices or the exact history), as well as all the metadata concerning the Board part of Azure DevOps. This link describes well the elements being processed and their relationships.

To create the Power BI data source, this time, we will choose the “OData Feed” connector.

 

PowerBiODataConnector

 

We will have to start by giving it the query URL: https://analytics.dev.azure.com/{organization_name}/{project_name}/_odata/v2.0. This is version 2.0 (version 3.0, which allows even wider access, is still in the preview phase).

Now comes the authentication part. Authentication using the Microsoft identifiers should normally work, but an error can sometimes occur. In that case, you must create a PAT (https://docs.microsoft.com/en-us/azure/devops/organizations/accounts/use-personal-access-tokens-to-authenticate?view=azure-devops&tabs=preview-page), go into the “Basic” authentication section and paste the PAT as your password (the user name can be left blank).

Power BI then lists the tables that can be defined as data source:

 

PowerBiODataViews

Retrieval via API

If data filtering/transformation is too complex, one last option remains: the use of the Azure DevOps API. You can find documentation on this API here: https://docs.microsoft.com/en-us/rest/api/azure/devops/?view=azure-devops-rest-6.1.

The Azure DevOps API allows you to query almost all the information available in our Azure DevOps organization.

Power BI’s web connector can be used to create our data source. You must then enter the URL of the desired API method and log in. However, if complex processes (filters, transformations, etc.) need to be performed but are not possible in Power BI, this can be done in Azure resources. Typically, the use of a Logic App with the shape “Send an HTTP request to Azure DevOps” using the desired API method provides easy access to the desired data.

 

LogicAppShape

 

The insertion of the reprocessed data in an Azure SQL Table or an Azure Storage Table then lets you use Power BI to create the data source thanks to an Azure SQL Database or Azure Table Storage connector.