Aggregate work tracking data using the Analytics service

Azure DevOps Services | Azure DevOps Server 2019

You can get a sum of your work tracking data in one of two ways using the Analytics service with Odata. The first method returns a simple count of work items based on your OData query. The second method returns a JSON formatted result based on your OData query which exercises the OData Aggregation Extension.

In this article you'll learn:

  • About the OData Aggregation Extension
  • How to generate a simple count of work items
  • How to use the Aggregation Extension for OData
  • How to group and filter aggregated results
  • How to aggregate data to generate a Cumulative Flow diagram

Note

The Analytics Service is generally available for all organizations using Azure DevOps Services. It provides several advanced widgets. Power BI integration and access to the OData feed of the Analytics Service is in Preview. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

Note

The Analytics Service is in preview for Azure DevOps Server 2019. While in preview, it is available to everyone free of charge. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

You access the Analytics Service by installing the Analytics Marketplace extension. Installing it provides several advanced widgets, Power BI integration, and access to the OData feed.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

What is the Aggregation Extension for OData?

Analytics relies on OData to author queries over your work tracking data. Aggregations in OData are achieved using an extension that introduces the $apply keyword. We have some examples of how to use this keyword below. Learn more about the extension at OData Extension for Data Aggregation.

Basic root URL

Use the following basic root URL as a prefix for all the examples provided in this article.

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/
https://{servername}:{port}/tfs/{OrganizationName}/{ProjectName}/_odata/{version}/

Note

The examples shown in this document are based on a Azure DevOps Services URL, you will need to substitute in your Azure DevOps Server URL

Simple count aggregations

First, let's look at how to do counts without the aggregation extensions.

Basic counting is done by adding the $count query option to the end of the URL. For example, to find out how many work items are defined in your organization, you add the following to your query:

/WorkItems/$count

Where the full OData query is:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems/$count

Note

The {version} value is formatted as v1.0. The latest supported version is v2.0, and the latest preview version is v3.0-preview. For more information, see OData API versioning.

For comparison, using the OData aggregation extension, you add the following to your query:

/WorkItems?$apply=aggregate($count as Count)

Where the full OData query is:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=aggregate($count as Count)

For simple counts, the non-aggregation approach has a simpler syntax.

Note

Using $count returns a single number; using the OData aggregation extension returns a formatted JSON.

You can also filter what you want to count. For example, if you want to know how many work items are in the "In Progress" state, specify the following in your query:

/WorkItems/$count?$filter=State eq 'In Progress'

Where the full OData query is:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems/$count?
  $filter=State eq 'In Progress'

For comparison, using data aggregations you add the following snippet to your query:

/WorkItems?$apply=filter(State eq 'In Progress')/aggregate($count as Count)

Where the full OData query is:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=
   filter(State eq 'In Progress')/
   aggregate($count as Count)

Aggregate data using the OData aggregation extension

Now that you've seen how to do simple counts, let's review how to trigger aggregations using the $apply token where the basic format at the end of the URL is as follows:

/{entitySetName}?$apply=aggregate({columnToAggregate} with {aggregationType} as {newColumnName})

Where:

  • {entitySetName} is the entity that needs to be queried for
  • {columnToAggregate} is the aggregation column
  • {aggregationType} will specify the type of aggregation used
  • {newColumnName} specifies the name of the column having values after aggregation.

Aggregated data using the apply extension

Using the $apply extension, you can obtain counts, sums, and additional information when you query your work tracking data.

Return the sum of all remaining work

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=aggregate(RemainingWork with sum as SumOfRemainingWork)

Return the last work item identifier

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=aggregate(WorkItemId with max as MaxWorkItemId)

Group results using the groupby clause

The OData aggregation extension also supports a groupby clause which is identical to the SQL GROUP BY clause. You can use this clause to quickly break down numbers in more detail.

For example, the following clause will return a count of work items:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=aggregate($count as Count)

Add the groupby clause to return a count of work items by type:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=groupby((WorkItemType), aggregate($count as Count))

This returns a result similar to the following:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(WorkItemType,Count)","value":[
    {
      "@odata.id":null,"WorkItemType":"Bug","Count":3
    },
    {
      "@odata.id":null,"WorkItemType":"Product Backlog Item","Count":13
    }
  ]
}

You can also group by multiple properties as in the following:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=groupby((WorkItemType, State), aggregate($count as Count))

This returns a result similar to the following:

{
  "@odata.context": "https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(WorkItemType,State,Count)",
  "value": [
    {
      "@odata.id": null,
      "State": "Active",
      "WorkItemType": "Bug",
      "Count": 2
    },
    {
      "@odata.id": null,
      "State": "Committed",
      "WorkItemType": "Bug",
      "Count": 1
    },
    {
      "@odata.id": null,
      "State": "Active",
      "WorkItemType": "Product Backlog Item",
      "Count": 5
    },
    {
      "@odata.id": null,
      "State": "Committed",
      "WorkItemType": "Product Backlog Item",
      "Count": 8
    }
  ]
}

You can also group across entities, however OData grouping differs from how you might normally think about it.

Filter aggregated results

You can also filter aggregated results, however they are applied slightly differently than when you are not using aggregation. The analytics service evaluates filters along a pipe so it's always best to do the most discrete filtering first.

Filters look like the following:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=
    filter(Iteration/IterationName eq 'Sprint 89')/
    filter(WorkItemType eq 'User Story')/
    groupby((State), aggregate($count as Count))

Note

You don't have to provide the groupby clause. You can simply use the aggregate clause to return a single value.

Generate multiple aggregations within a single call

When you want to provide multiple pieces of information, such as the sum of completed work and separately the sum of remaining work, you can accomplish this with separate calls or with a single call as follows:

/WorkItems?$apply=aggregate(CompletedWork with sum as SumOfCompletedWork, RemainingWork with sum as SumOfRemainingWork)

This will return a result that looks like the following:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(SumOfCompletedWork,SumOfRemainingWork)","value":[
    {
      "@odata.id":null,"SumOfCompletedWork":1525841.2900000005,"SumOfRemainingWork":73842.39
    }
  ]
}

Generate calculated properties for use within a single call

When you need to use a mathematical expression to calculate properties for use in a result set, such as the sum of completed work which is divided by the sum of completed work plus the sum of remaining work to calculate the percentage of work completed, you can accomplish this as follows:

/WorkItems?$apply=aggregate(CompletedWork with sum as SumOfCompletedWork, RemainingWork with sum as SumOfRemainingWork)/compute(SumOfCompletedWork div (SumOfCompletedWork add SumOfRemainingWork) as DonePercentage)

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(SumOfCompletedWork,SumOfRemainingWork)","value":[
    {
      "@odata.id":null,"DonePercentage":0.96760221857946638,"SumOfRemainingWork":50715.95,"SumOfCompletedWork":1514698.3400000033
    }
  ]
}

Generate a Cumulative Flow Diagram from aggregate data

Let's say you want to create a cumulative flow diagram in Power BI. You can use a query similar to the one below:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}//WorkItemBoardSnapshot?$apply=filter(DateValue gt 2015-07-16Z and DateValue le 2015-08-16Z)/filter(BoardName eq 'Stories' and Team/TeamName eq '{teamName}')/groupby((DateValue, ColumnName), aggregate(Count with sum as Count))&$orderby=DateValue

This returns a result similar to the following, which you can then use directly within your data visualization of choice.

{
  "@odata.context": "https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}//$metadata#WorkItemBoardSnapshot(DateValue,ColumnName,Count)",
  "value": [
    {
      "@odata.id": null,
      "DateValue": "2015-07-16T00:00:00-07:00",
      "Count": 324,
       "ColumnName": "Completed"
    },
    {
      "@odata.id": null,
      "DateValue": "2015-07-16T00:00:00-07:00",
      "Count": 5,
      "ColumnName": "In Progress"
    }
  ]
}

Let's take a look at what this query actually does:

  • Filters the data to a specific team
  • Filters the data to a specific backlog
  • Returns a count of work items.

When refreshing Power BI or Excel, the fewer rows required, the faster the refresh occurs.

Try this next