Pipeline duration trend sample report
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
This article shows you how to create a report that shows how long your pipeline typically takes to complete successfully. The daily trend of pipeline duration report is similar to the Pipeline rate trend chart of the Pipeline pass rate report.
The following image shows an example of a duration trend report.
Important
Power BI integration and access to the OData feed of the Analytics Service are generally available for Azure DevOps Services and Azure DevOps Server 2020 and later versions. The sample queries provided in this article are valid only against Azure DevOps Server 2020 and later versions, and depend on v3.0-preview or later version. We encourage you to use these queries and provide us feedback.
Prerequisites
- To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
- To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.
Note
This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.
Sample queries
You can use the following queries of the PipelineRuns entity set to create different but similar pipeline duration trend reports.
Note
To determine available properties for filter or report purposes, see Metadata reference for Azure Pipelines. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType.
Get 80th percentile duration trend for a specified pipeline
You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRuns?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.8,CompletedDateSK) as Duration80thPercentileInSeconds) "
&"/groupby( "
&"(Duration80thPercentileInSeconds, CompletedOn/Date)) "
&"&$orderby=CompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Substitution strings and query breakdown
Substitute the following strings with your values. Don't include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.
{organization}- Your organization name{project}- Your team project name{pipelinename}- Your pipeline name. Example:Fabrikam hourly build pipeline{startdate}- The date to start your report. Format: YYYY-MM-DDZ. Example:2021-09-01Zrepresents September 1, 2021. Don't enclose in quotes or brackets and use two digits for both, month and date.
Query breakdown
The following table describes each part of the query.
Query part
Description
$apply=filter(
Start filter() clause.
Pipeline/PipelineName eq '{pipelinename}'
Return pipeline runs for the specified pipeline.
and CompletedDate ge {startdate}
Return pipeline runs on or after the specified date.
and (SucceededCount eq 1 or PartiallySucceededCount eq 1)
Return only the successful or partially successful runs.
)
Close filter() clause.
/compute(
Start compute() clause.
percentile_cont(TotalDurationSeconds, 0.8,CompletedDateSK) as Duration80thPercentileInSeconds)
Compute 80th percentile of pipeline durations of all pipeline runs that match the filter criteria.
/groupby(
Start groupby()
(Duration80thPercentileInSeconds, CompletedOn/Date))
Group by date of completion of pipeline run and calculated day wise 80th percentile pipeline duration.
&$orderby=CompletedOn/Date asc
Order the response by completed date.
Filter by pipeline ID, rather than Pipeline Name
Pipelines can be renamed. To ensure that the Power BI reports don't break when the pipeline name is changed, use pipeline ID rather than pipeline name. You can obtain the pipeline ID from the URL of the pipeline runs page.
https://dev.azure.com/{organization}/{project}/_build?definitionId= `{pipelineid}`
You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRuns?"
&"$apply=filter( "
&"PipelineId eq {pipelineId} "
&"and CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.8,CompletedDateSK) as Duration80thPercentileInSeconds) "
&"/groupby( "
&"(Duration80thPercentileInSeconds, CompletedOn/Date)) "
&"&$orderby=CompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Get 50th and 90th percentile, along with 80th percentile duration trend
You may want to view the duration trend calculated using other percentile value. The following queries provide 50th and 90th percentile pipeline duration along with 80th percentile.
You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRuns?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.5,CompletedDateSK) as Duration50thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.8,CompletedDateSK) as Duration80thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.90,CompletedDateSK) as Duration90thPercentileInSeconds) "
&"/groupby( "
&"(Duration50thPercentileInSeconds, Duration80thPercentileInSeconds, Duration90thPercentileInSeconds, CompletedOn/Date)) "
&"&$orderby=CompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Filter by branch
To view the duration trend of a pipeline for a particular branch only, use the following queries. To create the report, do the following extra steps along with what is outlined in the Change column data type and Create the Line chart report sections.
- Expand
BranchintoBranch.BranchName. - Select Power BI Visualization Slicer and add
Branch.BranchNameto the slicer's Field. - Select the pipeline from the slicer for which you need to see the pipeline duration trend.
You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRuns?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.8,BranchSK, CompletedDateSK) as Duration80thPercentileInSeconds) "
&"/groupby( "
&"(Duration80thPercentileInSeconds, Branch/BranchName, CompletedOn/Date)) "
&"&$orderby=CompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Duration trend for all project pipelines
You may want to view the duration trend for all the pipelines of the project in a single report. To create the report, do the following extra steps along with what is outlined in the Change column data type and Create the Line chart report sections.
- Expand
PipelineintoPipeline.PipelineName. - Select Slicer from the Visualizations pane and add the
Pipeline.PipelineNamto the slicer's Field. - Select the pipeline from the slicer for which you need to see the trend of pipeline pass rate.
You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRuns?"
&"$apply=filter( "
&"CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.8,PipelineId, CompletedDateSK) as Duration80thPercentileInSeconds) "
&"/groupby( "
&"(Duration80thPercentileInSeconds, Pipeline/PipelineName, CompletedOn/Date)) "
&"&$orderby=CompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Expand columns in Power Query Editor
Prior to creating the report, you'll need to expand columns that return records containing several fields. In this instance, you'll want to expand the CompletedOn column to flatten it to CompletedOn.Date.
To learn how to expand work items, see Transform Analytics data to generate Power BI reports.
Change column data type
From the Transform menu change the data type for the Duration80thPercentileInSeconds to Decimal Number. To learn how, see Transform a column data type.
(Optional) Rename column fields
You can rename column fields. For example, you can rename the column Pipeline.PipelineName to Pipeline Name, or TotalCount to Total Count. To learn how, see Rename column fields.
Close the query and apply your changes
Once you've completed all your data transformations, choose Close & Apply from the Home menu to save the query and return to the Report tab in Power BI.
Create the Line chart report
In Power BI, under Visualizations, choose the Line chart report.
Add
CompletedOn.Dateto X-Axis, right-click it, and select CompletedOn.Date, rather than Date Hierarchy.Add
Duration80thPercentileInSecondsto Y-Axis right-click it, and ensure Sum is selected.
The report displayed should look similar to the following image.
Related articles
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for