Pipeline task duration trend sample report
Azure DevOps Services | Azure DevOps Server 2020
This article shows you how to get the daily trend report of the time taken to execute a pipeline task.
Important
Power BI integration and access to the OData feed of the Analytics Service are in Preview. The sample queries provided in this article are valid only against Azure DevOps Services and depend on v3.0-preview or later version. We encourage you to use these queries and provide us feedback.
The following image shows an example of such a chart.
Note
This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.
Prerequisites
- You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
- For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
- To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
- Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
- To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.
- You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
- Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
- For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
- To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
- Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
- To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your*View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.
Sample queries
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/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
Each query contains the following strings that you must substitute 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.
- {taskname} - The display name of the task for which the duration trend is needed.
Query breakdown
The following table describes each part of the query.
Query part
Description
$apply=filter(
Start filter()
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 from only the successful or partially successful pipeline runs
and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1)
Omit the pipeline runs that were canceled, skipped, or abandoned
)
Close filter()
/compute(
Start compute()
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()
(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
Query filters
To determine available query filters, query the metadata as described in Explore the Analytics OData metadata. You can filter your queries using any of the NavigationPropertyBinding Path values listed under an EntitySet. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType. Each EntitySet corresponds to the singular value for an EntityType.
For example, the EntitySet Name="PipelineRunActivityResults" corresponds to the EntityType Name="PipelineRunActivityResult". The OData metadata for EntitySet Name="PipelineRunActivityResults" is as shown below for v4.0-preview. You can add filters based on any of the listed NavigationPropertyBinding Path values.
<EntitySet Name="PipelineRunActivityResults" EntityType="Microsoft.VisualStudio.Services.Analytics.Model.PipelineRunActivityResult">
<NavigationPropertyBinding Path="Project" Target="Projects"/>
<NavigationPropertyBinding Path="Pipeline" Target="Pipelines"/>
<NavigationPropertyBinding Path="PipelineTask" Target="PipelineTasks"/>
<NavigationPropertyBinding Path="PipelineJob" Target="PipelineJobs"/>
<NavigationPropertyBinding Path="Branch" Target="Branches"/>
<NavigationPropertyBinding Path="PipelineRunQueuedOn" Target="Dates"/>
<NavigationPropertyBinding Path="PipelineRunStartedOn" Target="Dates"/>
<NavigationPropertyBinding Path="PipelineRunCompletedOn" Target="Dates"/>
<NavigationPropertyBinding Path="ActivityStartedOn" Target="Dates"/>
<NavigationPropertyBinding Path="ActivityCompletedOn" Target="Dates"/>
</EntitySet>
Power BI transforms
Expand PipelineRunCompletedOn column
The query returns some columns that you need to expand and flatten into its fields before you can use them in Power BI. Here in this example, such an entity is PipelineRunCompletedOn.
After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on PipelineRunCompletedOn.
Choose the expand button
Select the checkbox "(Select All Columns)" to expand
The table now contains the expanded entity CompletedOn.Date
Change column type
The query doesn't return all the columns in the format in which you can directly consume them in Power BI reports.
-
- Change the type of column TaskDuration80thPercentileInSeconds to Decimal Number.
Rename fields and query
When finished, you may choose to rename columns.
Right-click a column header and select Rename...
You also may want to rename the query from the default Query1, to something more meaningful.
Once done, choose Close & Apply to save the query and return to Power BI.
Create the report
Power BI shows you the fields you can report on.
Note
The example below assumes that no one renamed any columns.
For a simple report, do the following steps:
Select Power BI Visualization Line Chart.
Add the field PipelineRunCompletedOn.Date to Axis.
- Right-click PipelineRunCompletedOn.Date and select PipelineRunCompletedOn.Date, rather than Date Hierarchy.
Add the field TaskDuration80thPercentileInSeconds to Values.
- Right-click TaskDuration80thPercentileInSeconds field and ensure Sum is selected.
Your report should look like this.
More queries
You can use the following extra queries to create different but similar reports using the same steps defined previously in this article.
Use Pipeline ID, rather than Pipeline Name
You can change your Pipeline name. To ensure that the Power BI reports don't break when the pipeline name is changed, use the 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/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
Get 50th and 90th percentile, along with 80th percentile duration trend
You may want to view the task duration trend calculated using other percentile value. The following query gives 50th and 95th percentile task 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/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
Filter by branch
You may want to view the duration trend of a task for a particular branch only. To create the report, follow the extra steps below along with what is defined previously in this article.
- Expand Branch into Branch.BranchName
- Select Power BI Visualization Slicer and add the field Branch.BranchName to the slicer's Field
- Select the branch from the slicer for which you need to see the task 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/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
You may want to view the task duration trend for all the pipeline tasks in a single report. To create the report, carry out the following extra steps along with those steps defined previously in this article.
Select Power BI Visualization Slicer and add the field TaskDisplayName to the slicer's Field
Select the task from the slicer for which you need to see the 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/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
Full list of Pipelines sample reports
Pipeline
Pipeline and test
Related articles
Povratne informacije
Pošalјite i prikažite povratne informacije za