Connect to Analytics with Power BI Data Connector
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019
The Power BI Data Connector uses Analytics Views.
Important
Analytics views only support Azure Boards data (work items). Analytics views doesn't support other data types, such as Pipelines. Views provide a flat-list of work items, and don't support work item hierarchies. At this point, we have no plans to update the connector to support other types of data. For information on other approaches, read Power BI Integration Overview.
Prerequisites
Follow the steps below to get started using the Azure DevOps Power BI Data Connector:
- If you aren't familiar with Analytics, read "what is Analytics" before continuing.
- Install the Power BI Desktop October 2018 Update or a newer version. You can download it from the official Power BI Desktop download page.
- If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
- Boards must be enabled. If it's disabled, Analytics views won't be displayed. To re-enable Boards, see Turn an Azure DevOps service on or off.
- Make sure you have permissions required to access Analytics. If you're a member of the project Contributors group, you have permission.
- Create an Analytics view that you want to create a Power BI report.
- Review the knowledge base of Power BI articles.
- If you aren't familiar with Analytics, read what is Analytics? before continuing.
- Install the Power BI Desktop October 2018 Update or a newer version. You can download it from the official Power BI Desktop download page.
- You must be a member of a project with Basic access or higher. If you don't have a project yet, create one.
- If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
- Verify that Analytics] is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
- Boards must be enabled. If it's disabled, Analytics views won't be displayed. To re-enable Boards, see Turn an Azure DevOps service on or off.
- Make sure you have permissions required to access Analytics. If you're a member of the project Contributors group, you have permission.
- Create an Analytics view that you want to create a Power BI report.
- Review the knowledge base of Power BI articles.
Important
Azure DevOps no longer supports Alternate Credentials authentication since the beginning of March 2, 2020. If you're still using Alternate Credentials, we strongly encourage you to switch to a more secure authentication method (for example, personal access tokens). Learn more.
Connect to an Analytics view
Open Power BI Desktop.
Sign in to the service. Upon first-time access, you're required to sign in and have your credentials authenticated. Power BI Desktop saves your credentials so you only have to do this once.
Choose between these two authentication options:
Select Windows if you want to sign in using Windows.
Select Personal Access Token if you want to use a personal access token.
Choose Connect upon verification of your credentials.
Choose (1) Get Data, (2) Online Services, and (3) Azure DevOps (Boards only) for cloud services or Azure DevOps Server (Boards only) for on-premises. Then, choose Connect. Analytics views only support queries against work items and test cases.
Specify the basic parameters to connect to your data.
- For Collection URL, enter the URL where your Azure DevOps Server instance is hosted. For example, an example URL is "
http://fabrikam-server/AzureDevOpsServer/fabrikam-collection
". - For Team project, enter just the project name (e.g. use
Fabrikam-Fiber
if the URL of your Azure DevOps Server instance is "http://fabrikam-server/AzureDevOpsServer/fabrikam-collection/Fabrikam-Fiber
").
Important
Don't confuse the team name with the project name, a common mistake. If the URL you use on the daily basis is "
http://fabrikam-server/AzureDevOpsServer/fabrikam-collection/Fabrikam-Fiber-Git/Device
", thenFabrikam-Fiber-Git
is the project name, whereasDevice
is the team name.Upon successful sign in, Power BI verifies if your user credentials have the right permissions to access the specified project. If any errors arise, refer to the Q & A section as it covers the most common problems.
- For Collection URL, enter the URL where your Azure DevOps Server instance is hosted. For example, an example URL is "
Select the Analytics view
Expand the Shared Views folder, choose an Analytics view, and then choose Load. The Data Connector presents a list of available Analytics Views. Each view represents a set of data that you can pull into Power BI. You can also create custom Analytics Views.
The image below shows the default Analytics views. Your list of views may differ based on the process model used to created your project. All views listed, except those appended with "Today", will provide historical trend data.
Here we choose Stories - Last 30 days. This view filters for product backlog items.
Note
Because you verified the view in the previous section, the view should load. However, if the view won't load, it is most likely because the dataset is too large. Return to the view under the Analytics view in the web portal and adjust the filters to decrease the size of the dataset.
Select the view.
Note
The preview shown for any selected views in the navigator can be truncated depending on the number of fields selected and the size of the dataset. The data is truncated only for the preview and won't impact the full data refresh.
Choose Work Items - Today table and wait for the preview to load. It represents the current state of all Work Items in the project.
Select the checkbox next to Work Items - Today table and choose Load.
Note
Analytics Views do not apply filters that have defined using Power BI on the server. Any filters applied in Power BI will limit the data shown to end users but will not reduce the amount of data retrieved from Analytics. If the filter is intended to reduce the size of the dataset, it should be applied by customizing the view.
Wait for the data to load. You can observe its progress by looking at the status messages, which appear directly under the table name. Should you see any errors, refer to the Q & A section as it covers the most common problems.
Review the Data model. While the model loads, review the Dataset design for the Power BI Data Connector.
Create your report
Create reports based on the loaded data. After the load operation finishes, you can explore the data by creating custom reports. For examples of common reports, see Example Reports - using the Data Connector.
Q & A
Q: How should I read error messages coming from the connector?
A: You may feel challenged reading error messages coming from the connector because they typically contain extra contextual information. If the load operation fails, then the message starts with Failed to save modifications to the server.... When you see it, then search for [DataSource.Error] Azure DevOps...:. The following text is the real error message returned from the connector.
In the example below, the most important part of the error message is:
Query result exceeds maximum size. Please reduce the number of records by applying additional filters.
Q: How do I resolve the Query result exceeds maximum size. Please reduce the number of records by applying additional filters error?
A: You'll get the following error if the number of records retrieved from Analytics exceeds 250,000.
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] VSTS: Request failed: The remote server returned an error: (400) Bad Request. (Query result exceeds maximum size. Please reduce the number of records by applying additional filters).'.
This error typically occurs when your project has a large number of work items. The size of the dataset must be reduced by customizing the view. Do so before using it in Power BI.
Q: How do I resolve the The user wasn't authorized error?
The user was not authorized.
A: You can come across this error if you try to access a project that you don't have permissions to. You'll also see it if your credentials aren't recognized by Power BI. To check your permissions, see Set permissions to access Analytics and Analytics views. If it's a credential problem, you should work with your administrator to see if they can help resolve the problem. To learn more, see Power BI Security, User Authentication and Troubleshooting sign-in issues for Power BI.
Note
Power BI uses Azure Active Directory (AAD) to authenticate users who sign in to the Power BI service, and in turn, uses the Power BI login credentials whenever a user attempts to access resources that require authentication. Users sign in to the Power BI service using the email address used to establish their Power BI account; Power BI uses that login email as the effective username, which is passed to resources whenever a user attempts to connect to data. The effective username is then mapped to a User Principal Name (UPN) and resolved to the associated Windows domain account, against which authentication is applied.
Q: How do I resolve the Access to the resource is forbidden error?
Access to the resource is forbidden.
A: You can come across this error if you have access to a given project but you don't have the View analytics permissions. Work with your project administrator to get these permissions. For more information about the security model, see Analytics security.
Q: How do I resolve the Project "X" does not exist or you do not have permissions to access it error?
We encountered an error while trying to connect.
Details: Invalid "Project" parameter. Project "X" doesn't exist or you do not have permissions to access it..
A: This error indicates that the value you entered for Project name doesn't correspond to any project. A common mistake users can make is to confuse the project name with a team name.
If the URL you use every day is "https://dev.azure.com/fabrikam-fiber-inc/Fabrikam-Fiber-Git/Device
", then Fabrikam-Fiber-Git
is the project name and it should be used in the parameter. Device
is the team name.
Related articles
Feedback
Submit and view feedback for