Reporting on DevOps build pipelines with PowerBi

The latest v3.0-preview version of the DevOps Analytics API supports querying for more objects than just Work Items. Crucially for me, i can pull out a list of all my builds, to have a nice overview of whether or not they’ve succeeded. I’ll likely create a similar report on the Stages for some of my more complex Pipelines.

Lets start with my end result, which still needs a little polish to make it look really good but crucially gives me the data i need in one place.

I created it in Power Bi Desktop, using an OData feed from Azure DevOps Analytics.
The most helpful link i can steer you to is this one: https://docs.microsoft.com/en-us/azure/devops/report/powerbi/sample-odata-overview?view=azure-devops. It walks you through accessing Work Items using PowerBi Desktop.

The part of the documentation that tells you about the new entities in the v3.0-preview API is here: https://docs.microsoft.com/en-us/azure/devops/report/extend-analytics/data-model-analytics-service?view=azure-devops#branch-pipelines-and-test-entitysets

You put these two things together, and you have a good starter for cutting the right query to access the Build entities.

Here’s what i used to generate the dataset above.

let
    Source = OData.Feed(
	"https://analytics.dev.azure.com/gordonbyers/Ham/_odata/v3.0-preview/Builds?"
	&"$select=BuildId,BuildNumber,BuildOutcome,CompletedDate,TotalDurationSeconds,FailedCount&"
	&"$expand=Project($select=ProjectName),BuildPipeline($select=BuildPipelineName)"
    ),
    #"Expanded Project" = Table.ExpandRecordColumn(Source, "Project", {"ProjectName"}, {"ProjectName"}),
    #"Expanded BuildPipeline" = Table.ExpandRecordColumn(#"Expanded Project", "BuildPipeline", {"BuildPipelineName"}, {"BuildPipelineName"}) 
	  
in
    #"Expanded BuildPipeline"

You’ll need to choose the Authentication option for PowerBi to connect.
I opted for Basic Auth, and provided an Azure DevOps PAT token that was scoped just to access the Analytics.

Once the data was loaded into PowerBi, i then threw the right columns onto the report, ordered by date and set conditional formatting on the FailedCount value to change the colour of BuildOutcome.