How to Connect ADO to Power BI

Building custom dashboards on your Azure DevOps data allows you to track project health, sprint velocity, and bug trends outside of the standard ADO reports. By connecting ADO directly to Power BI, you unlock the ability to create visually rich, interactive reports that you can share with your entire team. This article will walk you through exactly how to establish this connection and start visualizing your project data.

Why Connect Azure DevOps to Power BI?

While Azure DevOps has its own set of built-in analytics and dashboards, they can be somewhat rigid. Connecting a business intelligence tool like Power BI gives you complete control over your reporting. You can create highly specific views that matter most to your team, combine ADO data with information from other sources (like finance or customer support systems), and build a single, comprehensive dashboard that tracks entire project lifecycles.

Here are a few common goals you can achieve with this connection:

• Track Sprint Health: Visualize burndown charts, track story points completed versus committed, and monitor task distribution across the team.
• Analyze Work Item Trends: Create reports that show bug discovery rates, feature completion time, and the flow of user stories through different states over time.
• Monitor Lead and Cycle Times: Identify bottlenecks in your development process by measuring how long it takes for work items to move from creation to completion.
• Combine Data Sources: Merge project data with financial data to report on project budget and ROI, or with customer support tickets to connect bugs to customer impact.

Prerequisites: What You’ll Need Before You Start

Before jumping in, make sure you have a few things squared away. This will save you from hitting any roadblocks during the setup process.

• Power BI Desktop: You'll need the free Power BI Desktop application installed on your computer. It's always a good idea to ensure you're using the latest version.
• Azure DevOps Organization Access: You must be a member of the Azure DevOps project you want to connect to.
• The Right Permissions: Your account needs "View analytics" permissions to be enabled. This is usually on by default for basic access levels, but a project admin can confirm this for you in Project Settings > Permissions > Users.
• Analytics Enabled: The VSTS Analytics extension must be installed and enabled for your organization. For most newer ADO organizations, this is pre-installed. You can check under Organization Settings > Extensions.

The Easiest Method: Using Analytics Views in ADO

The most straightforward and recommended way to get your data from Azure DevOps into Power BI is by using something called Analytics Views. Think of an Analytics View as a pre-configured, flattened dataset you create inside ADO. Instead of pulling massive, complex tables into Power BI and trying to figure out how they link together, you simply build a focused "view" in ADO first. This improves performance and makes reporting much easier.

Step 1: Create an Analytics View in Azure DevOps

First, you need to log in to your Azure DevOps organization and navigate to the project you want to report on.

1. From the project sidebar, go to Overview > Analytics views.
2. Click the New View button in the top right corner. This launches a wizard that will guide you through creating your view.
3. Configuration:
4. Field Selection: This is a crucial step for performance. Instead of leaving all fields selected, pick only the ones you'll actually need for your report. Good examples include Work Item Type, State, Iteration Path, Assigned To, and Story Points. The fewer fields you select, the faster your data will load in Power BI.
5. Work Item History: Here you can decide how much historical data to include. Do you only need the current status of items, or do you want a weekly or daily snapshot of how things have changed? For simple reports, "Current only" is often sufficient. For trend analysis, you might choose a period like "Last 30 days."
6. Verification & Saving: The final tab lets you verify your view and see a preview of the record count. If it looks good, click Save.

Your view is now ready. ADO will give you the necessary details to pull this specific dataset directly into Power BI.

Step 2: Connect Power BI to Your Analytics View

Now that your customized dataset is waiting for you in ADO, it's time to pull it into Power BI Desktop.

1. Open a new or existing Power BI Desktop file.
2. In the Home tab, click Get Data and then select More...
3. In the Get Data window, search for Azure DevOps. You will likely see two options: Azure DevOps (Boards only) and Azure DevOps Server (Boards only). For ADO cloud users, choose the first option. Click Connect.
4. You'll be prompted to enter your Organization and Team Project name. You can find these in your ADO URL structure: https://dev.azure.com/{Organization}/{Project}.
5. After entering the details, Power BI will ask you to sign in. Use the same credentials you use for Azure DevOps.
6. Once connected, the Navigator window will appear. This is where you'll see your Analytics View. It will be located under the folder named Shared Views (or My Views if you kept it private).
7. Select the view you created earlier (e.g., "Current Sprint User Stories and Bugs"). A preview of your data will appear on the right.
8. Click Load.

Power BI will now load the data from your Analytics View. Depending on the amount of data, this might take a few moments. Once complete, you’ll see the dataset appear in the Fields pane on the right-hand side of your Power BI canvas.

Building Your First Report From ADO Data

You’ve successfully connected the two systems. Now for the fun part: visualizing the data. Let’s create a simple report to show the current status of work items for the sprint.

1. In the Visualizations pane, select the "Clustered bar chart" icon.
2. With the new chart selected on your canvas, drag the State field from the Fields pane onto the Y-axis of the visualization.
3. Next, drag the Work Item ID field onto the X-axis. By default, Power BI will try to sum the IDs, which isn’t what we want. Click the small arrow next to Work Item ID in the X-axis field well and change the summarization type from "Sum" to "Count (Distinct)".

Just like that, you have a bar chart showing the breakdown of your user stories and bugs by their current state (New, Active, Resolved, Closed, etc.). You can now add more charts, tables, and slicers to build out your dashboard, filtering by Iteration Path, user assignment, or work item type.

Advanced Method: Using OData Queries

For users who need more control and flexibility than Analytics Views provide, connecting directly via an OData feed is an option. This method connects you to the entire underlying dataset from Azure DevOps Analytics, exposing all the tables and relationships. This is more powerful but also more complex and can be slow if you aren't careful with your queries.

To connect via OData:

1. In Power BI, go to Get Data > OData Feed.
2. The URL structure you'll need is:
3. After connecting, the Navigator will show a list of all available tables, such as WorkItems, Iterations, Projects, Users, and so on. You can select the tables you need and load them.
4. From here, you would use the Power Query Editor in Power BI to clean, transform, and merge these tables to build your data model.

Note: This approach pulls significantly more data and is best for users comfortable with data modeling and writing M code in Power Query. For most use cases, Analytics Views are faster and more efficient.

Troubleshooting and Best Practices

• Keep Views Focused: An Analytics View with too many fields or a long history will be slow to load. Always create targeted views for specific reports instead of one giant view.
• Permission Errors: If you get an access denied error in Power BI, it's almost always a permissions issue in Azure DevOps. Confirm with your project admin that your account has "View analytics" permissions.
• Refresh Your Data: Once you publish your Power BI report to the Power BI Service (online), you can schedule an automatic data refresh. This keeps your dashboards up-to-date without you having to manually refresh everything from your desktop file.
• Timeouts with Large Datasets: If Power BI times out while trying to load data, your Analytics View is likely too large. Go back to ADO and edit the view to include fewer fields or a shorter historical date range.

Final Thoughts

Connecting Azure DevOps to Power BI lets you step beyond standard reports and build custom dashboards that highlight what's most important to your team. Using Analytics Views makes this process remarkably clean and manageable, allowing anyone — not just data analysts — to slice and dice project data effectively.

While building direct connections is powerful, we know that project data from ADO is often just one piece of the puzzle. You might also want to report on marketing campaigns, sales pipelines in your CRM, and store performance. To streamline this, we built Graphed. It allows you to connect dozens of data sources, including developer tools, marketing platforms, and sales CRMs, into a single place. From there, you just ask questions in plain English, and Graphed instantly builds the dashboards and reports you need, saving you hours of manual setup and reporting work.