Connect with data by using Power BI and OData queries
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019
To pull data into Power BI, we recommend that you use Open Data Protocol (OData) queries. OData is an ISO/IEC-approved OASIS standard that defines best practices for building and consuming REST APIs. For more information, see the OData documentation.
To get started quickly, check out the Overview of sample reports that use OData queries. For information about other approaches, see the Power BI integration overview.
Power BI can run OData queries, which can return a filtered or aggregated set of data to Power BI. OData queries have two advantages:
- All filtering is done server-side. Only the data you need is returned, which leads to shorter refresh times.
- You can preaggregate data server-side. An OData query can carry out aggregations such as work item rollup and build failure rates. The aggregations are accomplished server-side, and only the aggregate values are returned to Power BI. With preaggregation, you can carry out aggregations across large datasets, without needing to pull all the detail data into Power BI.
In this article, you learn how to:
- Write and test OData queries.
- Run an OData query from Power BI.
Prerequisites
- Access level: You must be a member of a project with Basic access or higher.
- Permission: 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.
Use Visual Studio Code to write and test OData queries
The easiest way to write and test OData is to use Visual Studio Code with the OData extension. Visual Studio Code is a free code editor available on Windows, Mac, and Linux. The OData extension provides syntax highlighting and other functions that are useful for writing and testing queries.
Install Visual Studio Code and the OData extension
Install Visual Studio Code.
Open Visual Studio Code, select Extensions, and then search for odata. In the results list, select vscode-odata, and then install it.
Create and save an OData file in Visual Studio Code, for example,
filename.odata
. Name it whatever you want, but it must have a.odata
extension to enable the OData extension functionality.
Write the OData query
Write the OData query. For example queries, see the Overview of sample reports that use OData queries.
The following query returns the top 10 work items under a specific area path.
Replace
{organization}
,{project}
, and{area path}
with your values.https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems? $select=WorkItemId,Title,WorkItemType,State,CreatedDate &$filter=startswith(Area/AreaPath,'{area path}') &$orderby=CreatedDate desc &$top=10
To query across projects, omit
/{project}
entirely.
For more information, see OData query quick reference.
After you write the query in Visual Studio Code, you should see syntax highlighting.
Test the OData query
To test the OData query, place your cursor anywhere in the query text and select View > Command Palette.
In the search box, enter odata to bring up all the OData commands.
Select OData: Open. This action combines the multiline query into a one-line URL and opens it in your default browser.
The OData query result set is in JSON format. To view the results, install the JSON Formatter extension for your browser. Several options are available for both Chrome and Microsoft Edge.
If the query has an error, the Analytics service returns an error in JSON format. For example, this error states that the query selected a field that doesn't exist.
After you verify that the query works correctly, you can run it from Power BI.
Run the OData query from Power BI
To run the OData query from Power BI, follow the steps in the next section.
Combine the multiline OData query into a single-line query
Before you use the query in Power BI, you must convert the multiline OData query into a single-line query. The simplest way to do so is to use Visual Studio Code with the OData extension and use the OData: Combine command.
Note
In your filename.odata file, you might want to first create a copy of the multiline query text and then run OData: Combine on the copy. Do this step first because there's no way to convert the single-line query back to a readable multiline query.
In Visual Studio Code, place your query anywhere in the query text, and then select View > Command Palette. In the search box, enter odata. Then in the results list, select OData: Combine.
The multiline query gets converted into a single-line query.
Copy the entire line for use in the next section.
Run the query from Power BI
Select Get Data > OData feed. For more information, see Create a Power BI report with an OData query.
In the OData feed window, in the URL box, paste the OData query that you copied in the preceding section, and then select OK.
Power BI displays a preview page.
Specify query options
Select Edit on the preview page to open Power Query Editor.
Select Advanced Editor on the ribbon.
Scroll horizontally to view the
[Implementation="2.0"]
parameter in the Query pane.Replace
[Implementation="2.0"]
with the following string:[Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]
Note
To prevent throttling errors, do the following actions:
- Instruct Power BI to reference OData v4.
- Instruct the Analytics service to omit any values that are null, which improves query performance. Power Query attempts to resolve null values as errors by generating another query for every null value it encounters. This action can result in thousands of queries, which quickly exceeds your usage threshold, beyond which your user account gets throttled.
The following action is required for Power BI to successfully run an OData query against Azure DevOps Analytics Service.
Select OK to close the Advanced Editor and return to Power BI Power Query Editor. You can use Power Query Editor to perform the following optional actions:
- Rename the
Query1
query as something more specific. - Transform columns to a specific type. Power BI autodetects the type, but you might want to convert columns to a specific data type.
- Add computed columns.
- Remove columns.
- Expand columns into specific fields.
- Rename the
Create a report by using the data
Select Close & Apply to save your settings and pull the data into Power BI. After the data refreshes, you can create a report as you would normally in Power BI.