DevOps Data into Power BI

Azure DevOps is full of data that management want to to report on. Adding a Power BI report is an obvious way to do this. There are plenty of sample reports and Microsoft posts giving you guidance how to get DevOps data into Power BI. This post is my take on how I would build the reports. My focus is making them easy to extend and easy to point at a different organisation and project.

Power BI and DevOps

This post is part of a series

• Getting DevOps Data into Power BI
• Adding Parent Child Hierarchy using DAX Patterns
• Inheriting Values in the Parent Child pattern
• Adding conditional formatting icons the easy way

YouTube Version

Microsoft References for DevOps Data into Power BI

• https://learn.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect
  Great post showing you to use Visual Studio Code to write the query, test it and then use it in Power BI
• https://learn.microsoft.com/en-us/azure/devops/report/powerbi/sample-odata-overview
  Lots of sample reports
  

OData Queries

Power BI connects to DevOps using an OData query. From the first Microsoft link above it shows the query is made up of different parts. The first part is a path and then extras that select columns, filter the data and sort the data.

The simplest form though is just the URL, that will bring through all the columns and all the rows of data. We will start with that.

Simplest Query for Work Items

https://analytics.dev.azure.com/{Organisation}/{Project}/_odata/v3.0-preview/WorkItems?

The path includes the organisation and the project names. So we start in Power Query, by clicking on Transform data on the Home ribbon in Power BI. Then click on Manage Parameters to open a dialog. You need to add 2 parameters, Organisation and Project. Click on New to start a new parameter, enter in a name and description (yes add that description, future you will thank you), select type text and enter in the current value for the Organisation and the Project.

We could now use Get Data and select OData and build the string… But that is hard work and not easy to read! So I’ve written a template to walk you through. Click on New Source and select blank query. Click on Advanced Editor and paste in the code below.

let
    // Path using 2 parameters
    Path = "https://analytics.dev.azure.com/" & Organisation & "/" & Project & "/_odata/v3.0-preview/WorkItems?",
    
    // URL made up of parts
    URL = Path,
    
    // Do the OData query
    Source = OData.Feed(URL, null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
    Source

The Path line uses the 2 parameters and points to the WorkItems. The URL line looks redundant now but will make more sense when the query has more parts. The Source line uses the URL and includes the flags recommended by the link at the top of this post to prevent throttling.

When you click OK and set up the connection if required you should get a table of data with lots of columns.

Selecting the columns

The above query gives us more columns than we want. Rather than removing the columns in Power Query we can add a select line to the query which will make the OData query faster. So we need to pick the columns we want while we can see them all. I selected and put them into a comma separated string to make up a $select string to add to the Power Query

let
    // Path using 2 parameters and select columns
    Path = "https://analytics.dev.azure.com/" & Organisation & "/" & Project & "/_odata/v3.0-preview/WorkItems?",
    Select = "$select=WorkItemID,Title,WorkItemType,State,TargetDate,ParentWorkItemID,OriginalEstimate,AssignedToUserSK",

    // URL made up of parts
    URL = Path & Select,
    
    // Do the OData query
    Source = OData.Feed(URL, null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
    Source

When you finish editing the query you will get a table of the work items from DevOps.

You could add more parts to the query for filtering and sorting if required.

User and Calendar Table

DevOps has other tables including a user list and a calendar. These can be pulled through to Power BI using the same pattern.

Users

let
    // Path using 2 parameters and select columns
    Path = "https://analytics.dev.azure.com/" & Organisation & "/" & Project & "/_odata/v3.0-preview/Users?",
    Select = "$select=UserSK,UserName,UserEmail",

    // URL made up of parts    
    URL = Path & Select,

    // Do the OData query
    Source = OData.Feed(URL, null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
    Source

Calendar

This includes a filter for the Year to be greater than or equal to, ge, 2023.

let
    // Path using 2 parameters and select columns
    Path = "https://analytics.dev.azure.com/" & Organisation & "/" & Project & "/_odata/v3.0-preview/Dates?",
    Select = "$select=Date,MonthName,MonthOfYear,Year",
    Filter = "&$filter=Year ge 2023",

    // URL made up of parts    
    URL = Path & Select & Filter,

    // Do the OData query
    Source = OData.Feed(URL, null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
    Source

Some Modelling

Once the queries are written and loaded into Power BI desktop we are ready to do some modelling. I’m keeping it very light as I have another post coming that will implement the Parent Child hierarchy needed.

Relationships

The relationship model is only three tables, fact table WorkItems and the 2 dimension tables Users and Calendar. The relationships are

Measures

I only created two measures. The first was for the total hours in the original estimate column. If we had included completed and remaining hours I would have created measures for them as well. The second one was for the count of items

Total Hours = SUM(WorkItems[OriginalEstimate])

# Work Items = COUNTROWS(WorkItems)

Create Visuals

I kept it simple. Not all the tasks had Target dates so we could not do much via dates. So I had total hours and number of tasks and Users and Item States. I created a table and a tree map visual.

Conclusion on DevOps Data into Power BI

The above model is limited, I can’t see the structure of the project, I can’t rollup the tasks into user stories etc. It does have the data in it to get there though. The next post on adding the Parent-Child hierarchy will help.