Create Azure DevOps Analytics Visuals with Power BI OData Queries

By: Kenneth A. Omorodion | Updated: 2021-01-13 | Comments | Related: > DevOps

Problem

In a previous post, I described how to create DevOps Analytics metrics visuals with Power BI using default Analytics Views. However, there are three main ways to integrate or ingest DevOps data to Power BI as seen in this Microsoft document.

This tip focuses on the use of OData Queries to integrate DevOps Analytics data to Power BI.

Solution

Microsoft recommends that when creating DevOps Analytics visuals in Power BI it is best to use the OData queries except when the DevOps Analytics data is on Boards data in which case it might be okay to use DevOps Analytics views. Power BI can easily execute OData queries and thus it is the recommended approach for pulling DevOps data into Power BI as documented here.

To be brief, in this tip I would start by setting-up prerequisites for getting appropriate access to DevOps data, writing and testing OData queries, then connecting to Power BI Desktop and creating visuals in Power BI Desktop. Go through the steps as follows.

Step 1: Setting Up Prerequisites

Please take a look through some of the prerequisites discussed in my other tip on creating Azure DevOps metrics in Power BI here. Besides the prerequisites discussed in my previous tips, it is also essential that you set up appropriate permissions in Azure DevOps for you to be able to access Analytics in DevOps as seen in this document.

Step 2: Writing and Testing OData queries for Azure DevOps

One best tool to write and test OData queries is the use of Visual Studio Code. It provides an easy to read code editor interface which is available for Windows, Mac, and Linus. It requires an OData extension that helps in Syntax highlighting and other useful functions in writing and testing queries.

Let’s assume you have not used Visual Studio Code before and we need to download it first as seen in the snapshot below. If you are downloading for a Windows computer, just click on "Download for Windows", otherwise you can click on the dropdown and select your computer OS type.

Once installed correctly, you should be able to open the Visual Studio Code on your desktop and install the OData extension. To do this, on the tabs on the left side of the interface of the studio, click on the "Extensions" icon and enter "vscode-odata at the search bar on the top.

install extensions in Visual Studio Code

This should allow you to install OData extension as seen in the snapshot below. Please note that I already have this installed, thus the options showing in mine is for uninstall and disable. But yours should be showing install as you are assumed to be installing it for the first time.

install extensions on Visual Studio Code

After installing the extension, create a .odata file because we need to save our query in a file with an extension of .odata to enable the OData Extension functionality. To do this, click on "File", "New File". Once a new file is opened you need to click on "File" again and navigate to "Save As" to save the file as seen in the snapshot below.

Once you select "Save As.." you should get a window to save in a location folder you choose. But you should ensure the file is saved with an .odata extension as seen in the diagram below.

Next, write an OData query using our just created .data extension enabled file. To do this, click on "File" an the top left corner of the Visual Studio Code as seen in the diagram below. You can then select "Open Recent" or "Open File…" to navigate to where the .odata file we created has been saved.

Once the file is opened, we can start writing the queries. For a start, Microsoft has provided sample OData queries which can be found here. These can then be further adapted to our needs. For example, lets create an OData query that shows all Open Bugs in a DevOps project. To start, let's look at the basic structure of an OData query. All OData query you would be writing would usually start with a root URL as seen below.

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/

Obviously, the above root URL contains Substitution Strings, the values within the curly brackets, which must be replaced with your values without the "{}". For example, if your Organisation Name is "OrganisationA", your team Project Name is "AnalyticsProject", and your Version is dependent on the version you are using currently as there are three versions currently in use including v1.0, v2.0 and v3.0-preview. The v3.0-preview is the latest at the time of writing this tip. Please see more information on OData API Versioning here. Thus, if we are using the v3.0-preview version in the root URL above, we have our root URL as seen below.

https://analytics.dev.azure.com/OrganisationA/AnalyticsProject/_odata/v3.0-preview/

Now that we have our root URL set, lets add WorkItems to the end of our root URL to signify that we are checking all Work Items to see which one is a "Bug". We should then have the URL as seen below.

https://analytics.dev.azure.com/OrganisationA/AnalyticsProject/_odata/v3.0-preview/WorkItems?

Please note that you can always copy the sample queries done in the Microsoft documentation here and modify it to your need. For the purpose of this tip, I am not going to explain much on how the syntax works. More information on the OData syntax can be read here. Along with the root URL above, I will copy the rest of the query as seen below.

https://analytics.dev.azure.com/OrganisationA/AnalyticsProject/_odata/v3.0-preview/WorkItems? 
$filter=WorkItemType eq 'Bug' 
            and StateCategory ne 'Completed'
            and startswith(Area/AreaPath,'{areapath}')
        &$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK
        &$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath)

In summary, the query above is saying go to the root URL for the DevOps. For all WorkItems return the ones that are equal to "Bug" and ensure the StateCategory is not equal to "Completed". Where the Area/AreaPath starts with Area Path Name (Area Path name is usually in the format "Project\Level1\Level2"), select the fields to show as WorkItemId, Title, WorkItemType, State, Priority, Severity, TagNames, AreaSK.

Take the query into Visual Studio Code to edit it further as seen below.

edit or write a query on Visual Studio Code

Next, we need to convert the multiline query as seen above into a single line query before it can be used in Power BI. To do this, we need to click on "View" at the top left corner of the Visual Studio Code page and then select "Command Palette" as seen in the diagram below.

Then, we need to search for and select "OData: Combine" as seen in the diagram below.

This would help convert the multi-line OData query into a Power BI readable single line query as seen in the diagram below.

Step 3: Connect to Power BI Desktop

To connect to Power BI, we need to copy the single line Odata query and choose the "OData Feed" as a source connector as seen in the diagram below.

We need to paste the copied query into the connector wizard as seen below and click Ok.

Next, click "Transform Data" in the next window as seen in the diagram below.

This is now loaded into the Power Query Editor in Power BI where we can further carryout some transformations and cleansing as required. The diagram below shows the data in Power Query Editor.

There are two very important transformations needed in this process to ensure the connection works properly and query performance is improved. These are:

Implementing logic to prevent throttling errors by attempting to resolve null values as errors
Setup actions to ensure that Power BI can successfully run OData queries against DevOps Analytics Service.

The first transformation requires that we need to instruct Power BI to reference OData v4, and secondly instruct the Analytics service to omit any null values. To do this, we need to replace the "[Implementation="2.0"]"at the end of the query in the Advanced Editor with "[Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]". This is shown in the diagrams below.

We can see the query in the Advanced Editor window as seen below.

The second transformation includes several actions taken to ensure the queries can continuously run successfully. These may include renaming "Query 1", changing column data types, expanding columns, removing columns, and adding calculated columns.

Step 4: Creating Visuals in Power BI Desktop

After performing all necessary transformations in Power Query Editor, click on "Close & Apply" and load the data into the data model and start creating visuals from the data as seen in the example below. The visual used here is just for illustration. You can enrich yours more as your project demands.

visual created from dataset created from OData query source in Power BI

Summary

In this tip, we have learned how to can set-up prerequisites for getting appropriate access to DevOps data, writing and testing OData queries using Visual Studio Code editor, connecting to Power BI Desktop using the OData feed connector, and creating visuals in Power BI Desktop with the OData query we have created in Visual Studio Code. There are also links in this article to provide you with more resources to learn more and take all we have demonstrated in this article to another level.

Next Steps

See more information on how to manage permissions for DevOps Analytics access here.
You can download Visual Studio Code here.
You can read more information on OData Extension here.
Read my other tip about Creating Azure DevOps Metrics in Power BI here.
Try this tip out in your own data as business requires.

About the author

Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-01-13