| title | titleSuffix | description | ms.assetid | ms.subservice | ms.topic | ms.author | author | monikerRange | ms.date |
|---|---|---|---|---|---|---|---|---|---|
Power BI integration and supported connections methods |
Azure DevOps |
Learn about how to access Analytics for Azure DevOps and the different integration options you can use to connect to Power BI. |
8026A5ED-CD58-417A-913F-72A20272E7DC |
azure-devops-analytics |
overview |
chcomley |
chcomley |
>=azure-devops-2019 |
09/23/2024 |
[!INCLUDE version-gt-eq-2019]
Power BI is a suite of business analytics tools. With Power BI, you can pull data from Analytics, generate reports, and customize them to meet your needs. Use Power BI to do impromptu analysis, produce beautiful reports, and publish for enterprise consumption.
The integration of Power BI with Analytics enables you to go beyond the built-in Analytics reports and dashboard widgets to generate fully custom reports.
You can pull data from Analytics into Power BI in three ways, described in the following table.
Note
Open Data Protocol (OData) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming REST APIs. For more information, see OData documentation.
:::row::: :::column span="1"::: Connection Option :::column-end::: :::column span="1"::: Description :::column-end::: :::column span="2"::: Considerations :::column-end::: :::row-end:::
:::row::: :::column span="1"::: Connect using the OData queries :::column-end::: :::column span="1"::: Power BI can execute OData queries. OData queries are powerful and can filter and aggregate data before returning it to Power BI. :::column-end::: :::column span="2"::: We recommend this method, except for simpler reports on Boards data. It requires you to write OData queries, which is similar to writing SQL queries. You can review several sample reports to help you get started. :::column-end::: :::row-end::: :::row::: :::column span="1"::: Connect using the Azure DevOps Data Connector :::column-end::: :::column span="1"::: The Azure DevOps Data connector works with Analytics views. To access Analytics views, you must enable the feature as described in Enable preview features. :::column-end::: :::column span="2"::: This connector only works with Boards data (work items). It doesn't support other data types, such as Pipelines. It provides a flat-list of work items, and doesn't support work item hierarchies. There are no plans to update the connector to support other types of data. We recommend using OData queries unless you have a simpler report on Boards data. :::column-end::: :::row-end::: :::row::: :::column span="1"::: Connect using the Power BI's OData Feed connector :::column-end::: :::column span="1"::: Power BI provides an OData Feed connector that allows you to connect to and browse the Analytic's OData endpoint. It's the typical way Power BI interacts with OData feeds. You can browse and select the entities and use its Query Editor to filter the dataset. :::column-end::: :::column span="2"::: Only use this method if you have a small account. This method doesn't support server-side query folding. All filters are applied client-side. All the data is pulled into Power BI before applying the filters. If you have a small account, it might work well for you. If you have a large account, you might have long refresh times and timeouts. :::column-end::: :::row-end:::
After you connect data from Analytics in Power BI, you can modify the underlying data using Power BI's Power Query Editor and Advanced Editor. Note the following operational constraints:
- When you connect using OData queries or an OData feed, you can specify query filters, data to return, data to aggregate, and more.
- When you connect using an Analytics view, you must edit the Analytics view to modify the query filter and fields that you want returned.
For examples of reports, see sample reports provided later in this article.
After you import data into Power BI, you can use the Power Query Editor Transform, Add Column, and other menu options and tools to change the data as needed. Many of the sample reports provided in this article give instructions on data transformations that you need to make. These instructions include some of the following operations:
- Expand data columns
- Pivot columns
- Transform a column data type
- Replace null values in column data
- Create a custom field and a calculated column
For more information, see Transform Analytics data to generate Power BI reports.
Power BI supports creating new information from data already in your data model using Data Analysis Expressions (DAX). DAX provides a collection of functions, operators, and constants that you can use in a formula to calculate and return one or more values.
For an Analytics sample report that uses DAX, see Add a time-in-state measure to your Power BI report.
For more information, see Learn DAX basics in Power BI Desktop.
After you make any data transformations required for your report, use the Visualizations pane to craft changes in your report. You can drag column fields onto the Visualizations pane. You can then use the Filters pane to filter all or select data based on one or more fields.
To quickly get familiar with these Power BI basic features, see the following Power BI articles:
- Learn about Visualization types in Power BI
- Customize the Visualization pane
- Take a tour of the report Filters pane
Several sample reports show how to generate reports from either an Analytics view or OData query.
- Get active bugs report
- Get a count of work items
- Add a last refresh date
- Filter on teams
- Add a time-in-state measure to your Power BI report
To get started using OData queries in Power BI reports, see Overview of sample reports using OData queries. For specific examples, see one of the following articles:
:::row:::
:::column span="1":::
Azure Boards
:::column-end:::
:::column span="1":::
Azure Test Plans
:::column-end:::
:::row-end:::
:::row::: :::column span="1"::: [!INCLUDE boards samples] :::column-end::: :::column span="1"::: [!INCLUDE test plans samples] :::column-end::: :::row-end:::
[!INCLUDE pipeline samples]
::: moniker-end
::: moniker range="azure-devops-2019"
[!INCLUDE boards samples]
::: moniker-end
All sample report articles provide the following sections and information:
- Sample queries: The Power BI Query and raw OData query used to pull data into Power BI along with other sample queries.
- Transform data in Power BI: Steps to transform the data into a reportable format.
- Create the report: Steps to create a report from the data.
The following Marketplace extensions are available to support Power BI integration with Analytics.
- WIQL to OData translates an Azure DevOps work item query into an OData query for use with Azure DevOps Analytics OData endpoints, which can be useful to as a simple OData query.
- vscode-odata extension adds rich language support to Visual Studio Code for the OData query language.