Connect to Analytics data by using the Power BI OData feed

Azure DevOps Services | Azure DevOps Server 2020 | 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.

Access the Analytics OData feed

Important

Make sure that you've enabled or installed Analytics.

  1. Make sure that you've configured the permissions required to access Analytics.

  2. Open Power BI Desktop. If you haven't already done so, install Power BI Desktop now.

  3. On the Power BI Desktop welcome page or home ribbon, select Get Data.

    Power BI Desktop splash screen "Get data" button

  4. In the left pane, select Other > OData Feed, and then select Connect.

    The OData Feed button

  5. 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/.

    Enter OData Feed URL

    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.

  6. At the prompt, authenticate against the service. If you have not already done so, see Client Authentication Options.

  7. 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.

    The Entities list on the Navigator page

  8. Select Transform Data. Do not 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.
  9. For each entity that you've selected in the preceding steps, do the following:

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

    The Power BI OData Feed Queries list

    b. In the ribbon, select Advanced Editor.

    The Power BI OData Feed Advanced Editor button

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

    The Advanced Editor Query pane

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

    Replaced string.

  10. Select Close & Apply.