Events
Mar 17, 9 PM - Mar 21, 10 AM
Join the meetup series to build scalable AI solutions based on real-world use cases with fellow developers and experts.
Register nowThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
Test duration reports, similar to the one shown in the following image, provide insight into the number of times a test is run and the average time it takes for a particular test to execute during a pipeline run.
Use the queries provided in this article to generate the following reports:
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.
Category | Requirements |
---|---|
Access levels | - Project member. - 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.
You can use the following queries of the TestResultsDaily
entity set to create different but similar test duration reports. The TestResultsDaily
entity set provides a daily snapshot aggregate of TestResult
executions, grouped by test.
Note
To determine available properties for filter or report purposes, see Metadata reference for Test Plans Analytics and 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
.
Use the following queries to view the test duration report for a pipeline with a Build workflow.
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/v4.0-preview/TestResultsDaily?"
&"$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate} "
&"And Workflow eq 'Build'"
&")/groupby("
&"(TestSK, Test/TestName),"
&"aggregate("
&"ResultCount with sum as TotalCount, "
&"ResultDurationSeconds with sum as TotalDuration"
&"))/compute("
&"TotalDuration div TotalCount as AvgDuration"
&")",
null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
)
in
Source
Use the following queries to view the test duration report for a pipeline with a Release workflow.
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/v4.0-preview/TestResultsDaily?"
&"$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate}"
&")/groupby("
&"(TestSK, Test/TestName, Workflow),"
&"aggregate("
&"ResultCount with sum as TotalCount, "
&"ResultDurationSeconds with sum as TotalDuration"
&"))/compute("
&"TotalDuration div TotalCount as AvgDuration"
&")",
null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
)
in
Source
To view the test duration of pipeline tests for a particular branch, use the following queries. To create the report, carry out the following extra steps along with what is specified later in this article.
Branch
into Branch.BranchName
Branch.BranchName
to the slicer's FieldFor more information about using slicers, see Slicers in Power BI.
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/v4.0-preview/TestResultsDaily?"
&"$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate} "
&"And Workflow eq 'Build'"
&")/groupby("
&"(TestSK, Test/TestName, Branch/BranchName),"
&"aggregate("
&"ResultCount with sum as TotalCount, "
&"ResultDurationSeconds with sum as TotalDuration"
&"))/compute("
&"TotalDuration div TotalCount as AvgDuration"
&")",
null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
)
in
Source
To view the test duration of a pipeline for tests owned by a particular test owner, use the following queries. To create the report, carry out the following extra steps along with what is defined later in this article.
Test
into Test.ContainerName
Test.ContainerName
to the slicer's FieldCopy 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/v4.0-preview/TestResultsDaily?"
&"$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate} "
&"And Workflow eq 'Build'"
&")/groupby("
&"(TestSK, Test/TestName, Test/ContainerName),"
&"aggregate("
&"ResultCount with sum as TotalCount, "
&"ResultDurationSeconds with sum as TotalDuration"
&"))/compute("
&"TotalDuration div TotalCount as AvgDuration"
&")",
null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
)
in
Source
To view the test duration of a pipeline for tests owned by a particular test owner, use the following queries. To create the report, carry out the following extra steps along with what is defined later in this article.
Test
into Test.TestOwner
Test.TestOwner
to the slicer's FieldCopy 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/v4.0-preview/TestResultsDaily?"
&"$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate} "
&"And Workflow eq 'Build'"
&")/groupby("
&"(TestSK, Test/TestName, Test/TestOwner),"
&"aggregate("
&"ResultCount with sum as TotalCount, "
&"ResultDurationSeconds with sum as TotalDuration"
&"))/compute("
&"TotalDuration div TotalCount as AvgDuration"
&")",
null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
)
in
Source
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.The following table describes each part of the query.
Query part
Description
$apply=filter(
Start filter()
clause.
Pipeline/PipelineName eq '{pipelineName}'
Return test runs for the named pipeline.
And Date/Date ge {startdate}
Return test runs on or after the specified date.
and Workflow eq 'Build'
Return test runs for a Build
workflow.
)
Close filter()
clause.
/groupby(
Start groupby()
clause.
(TestSK, Test/TestName),
Group by the test name.
aggregate(
Start aggregate
clause to sum different test run outcomes matching the filter criteria.
ResultCount with sum as TotalCount,
Count the total number of test runs as TotalCount
.
ResultDurationSeconds with sum as TotalDuration
Sum the total duration of all the runs as TotalDuration
.
))
Close aggregate()
and groupby()
clauses.
/compute(
Start compute()
clause.
TotalDuration div TotalCount as AvgDuration
For all the tests, calculate the average duration by dividing the total duration by total number of runs.
)
Close compute()
clause.
You can rename the default query label, Query1, to something more meaningful. Simply enter a new name from the Query Settings pane.
Expand the Test
column to show the expanded entity Test.TestName
. Expanding the column flattens the record into specific fields. To learn how, see Transform Analytics data to generate Power BI reports, Expand columns.
From the Power Query Editor, select the TotalCount
column; select Data Type from the Transform menu; and then choose Whole Number.
Select the TotalDuration
and AvgDuration
columns; select Data Type from the Transform menu; and then choose Decimal Number.
For more information about changing the data type, see Transform Analytics data to generate Power BI reports, Transform a column data type.
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.
In Power BI, under Visualizations, choose **Table, and drag and drop the fields onto the Columns area.
Add the following fields to the Columns section in the order listed.
Test.TestName
TotalCount
AvgDuration
Right select the AvgDuration
and choose Average instead of Sum.
Your report should look similar to the following image.
Events
Mar 17, 9 PM - Mar 21, 10 AM
Join the meetup series to build scalable AI solutions based on real-world use cases with fellow developers and experts.
Register nowTraining
Learning path
Use advance techniques in canvas apps to perform custom updates and optimization - Training
Use advance techniques in canvas apps to perform custom updates and optimization
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.
Documentation
Pipeline test summary trend sample Power BI reports - Azure DevOps
Learn how to generate a test summary trend Power BI report for a given pipeline in the project.
Pipeline test duration trend sample Power BI reports - Azure DevOps
Learn how to generate a test duration trend Power BI report for a given pipeline in the project.
Pipeline pass rate trend of a test sample Power BI report - Azure DevOps
Learn how to generate a pass rate trend Power BI report for a given test of a pipeline in the project.