Home  >  Power BI / DevOps

Get DevOps Data into Power BI

Published date

Last modified date

DevOps icon connecting to a Power BI icon

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 Series

This post is part of a series:

YouTube Version

Microsoft References for DevOps Data into Power BI

OData Queries

We get DevOps Data into Power BI 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.

Example OData query to get DevOps Data into Power BI. Scroll down to find a code version

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

Copy Code
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.

Screen shots of the Transform data button, the Manage Parameters button and then the dialog showing the list of Parameters and the form to fill in

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.

Copy Code
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.

Table of data from DevOps showing various 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

Copy Code
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.

Table of data just showing the 8 columns selected.

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

Copy Code
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.

Copy Code
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

TableColumnRelationshipTableColumn
UsersUserSK1 to ManyWorkItemsAssignedToUserSK
CalendarDate1 to ManyWorkItemsTargetDate
Screen grab from the relationships screen

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

Copy Code
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.

Screen grab of a table listing the users with # Work Items and Total Hours and then below a treemap showing Total Hours by State

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.

More Power BI Posts

Power BI Series

Categories:Power BI​DevOps

Tags: