Pipeline task duration trend sample report
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
To visualize the time it takes tasks to complete for a specific pipeline, you can create a trend report. For example, the following image shows the 80th percentile in seconds for all tasks completed for a specific pipeline from September 1 to December 15, 2022.
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
- Access: Be a member of a project with at least Basic access.
- Permissions: By default, project members have permission to query Analytics and create views.
- For more information 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 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 PipelineRunActivityResults? entity set to create different but similar task 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. For more information about the data type of each value, review the metadata provided for the corresponding EntityType.
Task duration trend for specified pipeline name
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and TaskDisplayName eq '{taskname}' "
&"and PipelineRunCompletedOn/Date ge {startdate} "
&"and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded') "
&"and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1) "
&" ) "
&"/compute( "
&"percentile_cont(ActivityDurationSeconds, 0.8, PipelineRunCompletedDateSK) as TaskDuration80thPercentileInSeconds) "
&"/groupby( "
&"(TaskDuration80thPercentileInSeconds, PipelineRunCompletedOn/Date)) "
&"&$orderby=PipelineRunCompletedOn/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 task results for a specific pipeline.
and TaskDisplayName eq '{taskname}'
Return task results for a specific task.
and PipelineRunCompletedOn/Date ge {startdate}
Return task results for pipeline runs on or after the specified date.
and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded')
Return task results for only successful or partially successful pipeline runs.
and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1)
Omit pipeline runs that were canceled, skipped, or abandoned.
)
Close filter() clause.
/compute(
Start compute() clause.
percentile_cont(ActivityDurationSeconds, 0.8, PipelineRunCompletedDateSK) as TaskDuration80thPercentileInSeconds)
For each day, compute the 80th percentile of task durations of all tasks that match the filter criteria.
/groupby(
Start groupby() clause.
(TaskDuration80thPercentileInSeconds, PipelineRunCompletedOn/Date))
Group by date of completion of pipeline run and calculated day wise 80th percentile task duration.
&$orderby=PipelineRunCompletedOn/Date asc
Order the response by completed date.
Task duration trend for a pipeline, specify pipeline ID
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 pipelines runs page.
https://dev.azure.com/{organization}/{project}/_build?definitionId={pipelineid}
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
&"$apply=filter( "
&"Pipeline/PipelineId eq {pipelineid} "
&"and TaskDisplayName eq '{taskname}' "
&"and PipelineRunCompletedOn/Date ge {startdate} "
&"and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded') "
&"and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1) "
&") "
&"/compute( "
&"percentile_cont(ActivityDurationSeconds, 0.8, PipelineRunCompletedDateSK) as TaskDuration80thPercentileInSeconds) "
&"/groupby( "
&"(TaskDuration80thPercentileInSeconds, PipelineRunCompletedOn/Date)) "
&"&$orderby=PipelineRunCompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
50th, 80th, and 90th percentile task duration trend for a pipeline
To view the task duration trend calculated using other percentile value, use the following query that gives 50th and 95th percentile task duration along with 80th percentile.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and TaskDisplayName eq '{taskname}' "
&"and PipelineRunCompletedOn/Date ge {startdate} "
&"and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded') "
&"and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1) "
&") "
&"/compute( "
&"percentile_cont(ActivityDurationSeconds, 0.5, PipelineRunCompletedDateSK) as TaskDuration50thPercentileInSeconds, "
&"percentile_cont(ActivityDurationSeconds, 0.8, PipelineRunCompletedDateSK) as TaskDuration80thPercentileInSeconds, "
&"percentile_cont(ActivityDurationSeconds, 0.95, PipelineRunCompletedDateSK) as TaskDuration95thPercentileInSeconds) "
&"/groupby( "
&"(TaskDuration50thPercentileInSeconds, TaskDuration80thPercentileInSeconds, TaskDuration95thPercentileInSeconds, PipelineRunCompletedOn/Date)) "
&"&$orderby=PipelineRunCompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Task duration trend for a pipeline filtered by branch
To view the duration trend of a task for a particular branch, use the following query. To create the report, do the following steps:
- Expand
BranchintoBranch.BranchName - Change column data type
- Create the Line chart report
- Select Slicer from the Visualizations pane and add the
Branch.BranchNameto the slicer's Field - Select the branch from the slicer for which you need to see the task duration trend.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
&"$apply=filter( "
&" Pipeline/PipelineName eq '{pipelinename}' "
&" and TaskDisplayName eq '{taskname}' "
&" and PipelineRunCompletedOn/Date ge {startdate} "
&" and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded') "
&" and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1) "
&" ) "
&"/compute( "
&" percentile_cont(ActivityDurationSeconds, 0.8, BranchSK, PipelineRunCompletedDateSK) as TaskDuration80thPercentileInSeconds) "
&"/groupby( "
&" (TaskDuration80thPercentileInSeconds, Branch/BranchName, PipelineRunCompletedOn/Date)) "
&"&$orderby=PipelineRunCompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Task duration trend for all pipeline tasks
To view the task duration trend for all the pipeline tasks in a single report, use the following query. To create the report, do the following steps:
- Change column data type
- Create the Line chart report
- Select Slicer from the Visualizations pane and add the
TaskDisplayNameto the slicer's Field - Select the task from the slicer for which you need to see the task duration trend.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and PipelineRunCompletedOn/Date ge {startdate} "
&"and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded') "
&"and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1) "
&") "
&"/compute( "
&"percentile_cont(ActivityDurationSeconds, 0.8, TaskDisplayName, PipelineRunCompletedDateSK) as TaskDuration80thPercentileInSeconds) "
&"/groupby( "
&"(TaskDuration80thPercentileInSeconds, TaskDisplayName, PipelineRunCompletedOn/Date)) "
&"&$orderby=PipelineRunCompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
(Optional) Rename query
You can rename the default query label, Query1, to something more meaningful. Simply enter a new name from the Query Settings pane.
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 PipelineRunCompletedOn column to flatten it to PipelineRunCompletedOn.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 TaskDuration80thPercentileInSeconds column 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 TaskDuration80thPercentileInSeconds to 80th Percentile. 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
PipelineRunCompletedOn.Dateto X-Axis. Right-click the field and choose PipelineRunCompletedOn.Date in place of Date Hierarchy.Add
TaskDuration80thPercentileInSecondsto Y-Axis, and right-click it to ensure Sum is selected.To change the report title, select the Format your visual paint-brush icon from the Visualizations pane, select General, expand Title, and replace the existing text.
The following image shows the resulting report.