Create a Power BI report with a default Analytics view

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

With Power BI Desktop, you can easily connect to an Analytics view to start creating reports for your project in Azure DevOps. An Analytics view provides a simplified way to specify the filter criteria for a Power BI report based on Analytics data.

Important

Analytics views only support Azure Boards data (work items). Analytics views don'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, see About Power BI integration.

If you don't have Power BI Desktop, you can download and install it for free.

You can create status and trend reports of your work tracking data using one of the default Analytics views available to you. As needed, you can also create a custom Analytics view.

Follow these steps to create two reports in Power BI desktop that shows a daily trend of backlog items and a count of active user stories based on a default Analytics view:

1. From the web portal for Azure DevOps:
   a. Open Analytics view
   b. Verify a default Analytics view for your data.
2. From the Power BI Desktop:
   a. Get the dataset defined by the Analytics view
   b. Create a trend report
   c. Apply filters to your trend report
   d. Create a card and apply the Is Current=True filter to show current count.

Prerequisites

• Access: Be a member of a project with at least Basic access.
• Permissions: 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.

Power BI prerequisites

To create a Power BI report that references an Analytics view, you must meet the following criteria:

• Have installed Power BI Desktop October 2018 Update or later version. You can download this client application from the official Power BI Desktop download page.
• Have tracked work items for some period of time on which to generate a trend report.

Open Analytics to access views

From your web portal, select Boards > Analytics views.

If you don't see Analytics views, check that you have permissions to view Analytics. See the Permissions and prerequisites to access Analytics.

Verify the default Analytics view for your data

By verifying the view you'll use in Power BI, your view is more likely to load correctly in Power BI. If verification takes too long, you can quickly adjust the view. Add filters or narrow your history and verify your view again.

1. From the Analytics view>All page, choose the actions icon next to the default view and select the Edit option. For a project that uses the Agile process, use the Stories -Last 30 days view.

   

2. Choose the Verification tab and then the Verify view button.

   

   Wait until the verification process completes. Verification time varies according to the amount of data defined in your view. For example, a view that includes all work item types and specifies "All history", will take more time to verify than a view that includes only stories and specifies a rolling period of 30 days.

   

3. If your view successfully verifies, then go to the next step. If it's unsuccessful, select the Work Items tab and adjust the selections to select fewer teams or specify fewer work items in the dataset.

   For more information about defining views, see Create an Analytics view.

Connect to an Analytics view

1. Open Power BI Desktop.

2. Sign in to the service. First-time access requires you to sign in and have your credentials authenticated. Power BI Desktop saves your credentials so you only have to do so once.

   Note

   Azure DevOps doesn't support cross-tenant scenarios using OAuth. Instead, use service principals and managed identities.

3. Select Connect upon verification of your credentials.

   

4. Select Get Data > Online Services, then select Azure DevOps (Boards only) for cloud services or select Azure DevOps Server (Boards only) for on-premises. Select Connect. Analytics views only support queries against work items and test cases.

   

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

   

   Important

   Don't confuse the team name with the project name, which is a common mistake. For example, if the URL that you use is http://fabrikam-server/AzureDevOpsServer/fabrikam-collection/Fabrikam-Fiber-Git/Device, then Fabrikam-Fiber-Git is the project name and Device is the team name.

   After successful sign-in, Power BI verifies that your user credentials have permissions to access the specified project. If any errors arise, see the FAQs for solutions to the most common problems.

Select the Analytics view

1. Expand the Shared Views folder, select an Analytics view, and then select 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 following image shows the default Analytics views. Your list of views might differ based on the process model used to create your project. All views listed, except the views appended with "Today," provide historical trend data.

   In the following example, we select Stories - Last 30 days, which filters for product backlog items.

   

   Note

   Because you verified the view in the previous section, the view should load. If the view doesn't load, it's 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.

2. 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 affect the full data refresh.

   • Select the 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 the Work Items - Today table and select Load.

   Note

   Analytics views don't apply filters defined by using Power BI on the server. Any filters applied in Power BI limit the data shown to end users but don't reduce the amount of data retrieved from Analytics. If the filter is intended to reduce the size of the dataset, apply it by customizing the view.

3. Wait for the data to load. You can observe its progress by looking at the status messages, which appear directly under the table name. If you see any errors, see the FAQs section for solutions to the most common problems.

   

4. Review the Data model. While the model loads, review the dataset design for the Power BI Data Connector.

Create a daily trend report

1. In your report, (1) select the Line chart visual, (2) enter work item id in the search field, and then (3) check the box for Work Item Id.

   

   It will change your chart to a single dot.

   Tip

   To change the chart size, choose the View tab, Page View, and then select the Adjust Size option as shown. You can then resize the chart to your desired dimensions.

   

2. Next, (1) select the Date field in the fields list as your axis. By default, Power BI creates a date hierarchy from any date field. To see a daily trend, (2) select the context menu icon next to the field and change it from Date Hierarchy to Date.

   

   Note

   To view trends over time, you want to use the Date format and not Date Hierarchy. The Date Hierarchy in Power BI rolls ups everything into a simple number for the period. The day level in the hierarchy rolls all days to a number between 1-31. For example, April 3 and May 3 both roll up into number 3. This is not the same as counting items per actual date.

Group and filter the trend by State

• To group your user stories by State, drag the State field into the Legend area. Optionally, filter the set of States to show in the chart.

  Here we've filtered the list to show work items in the Active, Committed, and In Progress states.

  

  The chart now shows a daily distinct count of user stories, grouped by Active, Committed, and In Progress.

  Tip

  If you need to modify your Analytics view, you can do so and then return to your Power BI report and refresh the data. Simply click the Refresh option as shown.

  

Create a card to show the latest active stories count

1. Add a new page by clicking the plus sign (+) at the bottom of the page.

2. Select the card visual, add the Work Item Id field, and then drag the State and Work Item Type fields under Page level filters. Filter the State to show Active, and filter the Work Item Type for Bugs.

   The card now shows the number of active stories times the number of days each one was defined during the past 60 days. For this example, that number is 894.

   

3. To get the latest active stories count, you filter the card to count only the latest revision of the filtered set of work items. Add Is Current as a filter and select True to filter only the data for the latest day. Is Current is a field added to the view automatically marking the rows that contain the latest revision of the work items.

   Applying this filter brings the Active stories count to 39.

   

   The value should match the count on the last day of the trend chart that you created in the previous section.

Continue to explore your data

The reports shown in this quickstart illustrate how easy it is to generate reports of your work tracking data using Analytics views.

Next steps

Create an Active bugs report

Related articles

• Get started with Power BI Desktop
• Power BI integration overview
• Create Analytics views
• Connect with Power BI Data Connector
• Dataset design for the Power BI Data Connector