Connect to the Analytics service with Power BI Data Connector

Azure DevOps Services | Azure DevOps Server 2019

The Azure DevOps Power BI Data connector works with Analytics views. For more information, see what are Analytics Views.

Follow the steps below to get started using the Azure DevOps Power BI Data Connector:

  1. Install the Power BI Desktop October 2018 Update or a newer version. You can download it from the official Power BI Desktop download page.
  2. Install the Analytics extension.
  3. Configure the permissions required to access the Analytics service.
  4. Review the knowledge base of Power BI articles.
  5. If you are not familiar with the Analytics Service, we recommend you read "what is the Analytics Service" before continuing.
  6. Launch Power BI Desktop and get your data. Select Get Data using one of the two options to access the Data Connector.

    You can either choose Get Data button in the splash screen that opens up when you launch Power BI.

    Power BI desktop splash screen, Get data button

    Or, you can find a similar button in the ribbon in the Home section.

    Power BI desktop ribbon, Get data button

  1. Choose (1) Online Services, (2) Azure DevOps Server (Beta), and then Connect.

    Get data dialog

  2. Specify the basic parameters to connect to your data.

    Connect to Azure DevOps Services dialog

    • 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", then Fabrikam-Fiber-Git is the project name, whereas Device is the team name.

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

      Sign in dialog

  4. Choose Connect upon verification of your credentials. Upon successful login Power BI will verify 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.

  1. Choose (1) Online Services, (2) Azure DevOps (Beta), and then Connect.

    Get data dialog

  2. Specify the basic parameters to connect to your data.

    Connect to Azure DevOps Services dialog

    • For Account, enter the organization name in Azure DevOps (not the full URL). For example, use fabrikam-fiber-inc if the URL of your organization is "https://dev.azure.com/fabrikam-fiber-inc./".
    • For Team project, enter just the project name (e.g. use Fabrikam-Fiber if the URL of your project is "https://dev.azure.com/fabrikam-fiber-inc./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 "https://dev.azure.com/fabrikam-fiber-inc./Fabrikam-Fiber-Git/Device", then Fabrikam-Fiber-Git is the project name, whereas Device is the team name.

  3. 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 will only have to do this once.

    Choose between these two authentication options:

    • Select Organizational account if your organization is using Azure Active Directory to manage users.
    • Select Personal Access Token if you want to use a personal access token.
    • Select Basic if you are connecting to a personal organization. This option also supports alternate credentials, but it is not recommended as unlike personal access tokens, this login doesn't expire and can't be scoped to limit access to your data.

      Sign in dialog

      If you select Organizational account, enter your username and password into the standard login page.

  4. Choose Connect upon verification of your credentials. Upon successful login Power BI will verify 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.

    Connect dialog

  1. Select the view. The Data Connector will present a list of available Analytics Views. Each view represents a set of data that can be pulled into Power BI.

    Azure DevOps provides a set of default Analytics Views. You can also create custom Analytics Views. The image below shows the default Analytics views. Your list of views may differ.

    Navigator dialog, default views

    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 will not 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 the Analytics service. If the filter is intended to reduce the size of the dataset, it should be applied by customizing the view.

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

    Apply query changes loading dialog

  3. Review the Data model. While the model is being loaded please review the Dataset design for the Power BI Data Connector

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

    Power BI desktop report view

Q & A

Q: How should I read error messages coming from the connector?

A: Reading error messages coming from the connector can be challenging because they typically contain additional contextual information. If the load operation fails, then the message will start with Failed to save modifications to the server.... When you see it, then search for [DataSource.Error] VSTS:. 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.

QueryExceedsPreferredMaxSizeException

Q: What can I do to resolve: Query result exceeds maximum size. Please reduce the number of records by applying additional filters?

A: You will 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 lot of work items. The size of the dataset must be reduced by customizing the view before using it in Power BI.

Query result exceeds maximum size. Please reduce the number of records by applying additional filters

Q: How do I resolve error: The user was not authorized?

The user was not authorized.

A: You can encounter this error if you try to access a project to which you don't have permissions.

The user was not authorized.

Q: How do I resolve error: Access to the resource is forbidden?

Access to the resource is forbidden.

A: You can encounter this error if you have access to a given project but you do not have the View analytics permissions. Please work with your project administrator to get these permissions. For more information about the security model, see Analytics Service security.

Access to the resource is forbidden.

Q: How do I resolve error: Project "X" does not exist or you do not have permissions to access it?

We encountered an error while trying to connect.
Details: Invalid "Project" parameter. Project "X" does not exist or you do not have permissions to access it..

A: This error indicates that the value you entered for Project name does not 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 on a daily basis 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, whereas Device is the team name.

ProjectNotFoundError

Feedback and support

We welcome your feedback.

For product related inquiries, review our User Voice page or report a problem at Developer Community. Also, you can get advice on Stack Overflow, or get support via our Support page.

Follow us on Twitter @AzureDevOps.