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.
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.
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
You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.
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
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-01Z represents 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.
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.
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
You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.
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
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.
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
You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.
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
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:
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
You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.
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
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:
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
You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.
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
(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.Date to X-Axis. Right-click the field and choose PipelineRunCompletedOn.Date in place of Date Hierarchy.
Add TaskDuration80thPercentileInSeconds to 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.
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.