Connect to Analytics data by using the Power BI OData feed

Article
02/24/2023

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

You can access Analytics data through the Power BI Desktop OData feed.

Important

The method demonstrated in this article works for small organizations, because it always pulls in all the data into Power BI. Most of the filtering that's specified in the Power Query Editor is done client-side. For information about other approaches, see Power BI integration Overview.

Prerequisites

To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

Access the Analytics OData feed

Important

Make sure that you've enabled or installed Analytics.

Make sure that you've configured the permissions required to access Analytics.
Open Power BI Desktop. If you haven't already done so, install Power BI Desktop now.
On the Power BI Desktop welcome page or home ribbon, select Get Data.
In the left pane, select Other > OData Feed, and then select Connect.
In a supported browser, enter the URL in the format https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/.

For example, if {OrganizationName} is fabrikam and {version} is v1.0, the URL is https://analytics.dev.azure.com/fabrikam/_odata/v1.0/.

Note

Alternatively, you can enter the URL with the ProjectName specified, as shown in the following example:
https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/ Using this format trims the results by the specified project across all entities related to that project.
At the prompt, authenticate against the service. If you haven't already done so, see Client Authentication Options.
Select the check boxes next to the entities whose data you want to retrieve.

Important

Do not select entities whose name includes Snapshot. Such entities contain the state of every work item on each day since the work item was created. For repositories of any size, selecting these entities will cause tens to hundreds of millions of work items to fail to load. Snapshot tables are intended only for aggregation queries.
Select Transform Data. Don't select Load.
Important

For each entity that you've selected, Power Query creates a query. Each query must be updated manually, because doing so prevents throttling errors. Power Query attempts to resolve null values as errors by generating an additional query for every null value it encounters. This can result in thousands of queries, which will quickly exceed your usage threshold, beyond which your user account will be throttled.

To prevent this issue:
- Instruct Power BI to reference OData v4.
- Instruct the Analytics service to omit any values that are null, which improves query performance.
For each entity that you've selected in the preceding steps, carry out these steps:

a. In the Queries list, select an entity whose query you want to. In this example, Areas is selected.

b. In the ribbon, select Advanced Editor.

c. In the Query pane, scroll horizontally to view the [Implementation="2.0"] parameter.

d. Replace [Implementation="2.0"] with the following string:
[Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]
Select Close & Apply.