Creating Azure DevOps Analytics metrics in Power BI using Pre-Built Analytics Views

By:   |   Comments (3)   |   Related: > DevOps


Problem

Azure DevOps Analytics provides the reporting platform for Azure DevOps and Azure DevOps Server. It is enabled for all Azure DevOps services projects and can be used to monitor and measure present or past state of projects by automatically populating itself with data in Azure DevOps. You can read more about Azure DevOps Analytics here.

As of July 2020, not all data in Azure DevOps can be accessed via Analytics, however, at this time it is generally available for Azure DevOps Service and Azure DevOps Server 2020, and can be used to access data for services like Boards, Pipelines and Test Plans. One way to connect Power BI to Azure DevOps data is via Analytics Views (currently in Preview at the time of writing this Tip). Analytics views is described as data sets that are exposed to Power BI in Azure DevOps and can be used to create reports based on your Azure DevOps data. However, there is currently (August 2020) a limitation in that Analytics views only support Azure Boards data (work items) and does not support other Azure DevOps data like Pipelines for now. See more information on this here.

The focus of this tip is how to create DevOps metrics in Power BI using default Analytics views.

Solution

The approach here starts by setting-up prerequisites for Analytics views, then connecting to Power BI Desktop, creating visuals in Power BI Desktop, and Publishing to Power BI service. See the steps as follows.

Step 1: Setting up prerequisites

To begin connecting Azure DevOps data to Power BI via Analytics views, we need to first ensure the following Prerequisites are in place.

  • You need to be a member of a project with Basic access or higher.
  • You need to ensure Boards is enabled. If it is disabled, Analytics views will not be displayed. See here on how to Turn an Azure DevOps service on or off.
  • Since Analytics views is still in Preview at this time, you need to enable the Analytics Views preview feature either for individual users or for the organization.
  • Have the *View Analytics permission set to Allow.

If all prerequisites are done correctly you should be ready to connect to Power BI. But before that I will quickly talk about how to enable Analytics views in the preview features. To do this, first log in to your DevOps account on https://dev.azure.com then click on "Overview" as seen in the diagram below to see if Analytics views have been enabled. If enabled you would see it among the list of items, if not then you need to enable it.

Screenshot for Azure DevOps Overview

In the diagram above, its obvious Analytics views is yet to be enabled, so the diagram below shows how to enable it within the preview features.

Screenshot on how to navigate to Azure DevOps Preview features

Just click on the preview features and toggle the button for Analytics views to "On" as seen in the diagram below.

Screenshot on how to activate Azure DevOps Analytics Views

After enabling the feature, you would then be able to see the Analytics views blade as shown in the diagram below. This means Power BI can now connect to it.

Screenshot Azure DevOps blades showing  Analytics Views

Step 2: Connecting to Power BI

Assuming you are still logged in to Azure DevOps, you need to now log in to Power BI Desktop. At the time of writing this blog, there are two connectors in Power BI that can link to Azure DevOps Analytics views they include as follows:

  1. Azure DevOps (Boards Only)
  2. Azure DevOps Server (Boards Only)

However, in this tip, I will only leverage the Azure DevOps (Boards Only) connector for demonstration purposes. See more information on this here.

First, click on Get data and select "More" to have access to more Power BI connectors of choice.

Screenshot showing how to connect Analytics Views with Power BI

Then, we need to either type a search for the connector we want or just click on "Online Services" to show all in this category. Next, we select "Azure DevOps (Boards Only)" option and click "Connect".

Screenshot showing connection to Azure DevOps (Boards only)

This would lead us to a window where we need to enter the Azure DevOps Team project and Organization details as seen below. To get these details for the sake of those not too conversant with Azure DevOps you need your project URL like the one below.

https://dev.azure.com/{Organisation}/{ProjectName}

Once you get these, enter it into the window in Power BI connector and click "OK".

Screenshot showing Azure DevOps (Boards only) connector window

The next navigator window would list all the pre-built Analytics views existing for the project as shown in the diagram below. One can then choose to select all the views or choose a few of them as required. However, for the purpose of this tip, I have only selected just two of the views as shown in the diagram below.

Screenshot showing how to connect to individual Analytics Views within Power BI

You can choose to "Transform Data" or just "Load", but usually you might want to cleanse the data first before loading the queries.

Step 3: Creating your visuals in Power BI

Having loaded the queries successfully, we can now create a basic visual to illustrate the end-product of this tip. As shown in the diagram below, I have created a Card and Column chart visuals to show the number of Work Titles by Work State in the last 5 days. Please feel free to create your own visuals accordingly.

Screenshot showing sample visuals for Analytics Views created in Power BI

Step 4: Publish to Power BI Service

Next is to save the work and publish to Power BI service. I would like to assume that the reader is conversant with publishing reports to Power Bi service already if not see one of my tips where as part of the demo I illustrated how to do this in detail.

Within Power BI service, you would need to configure a refresh schedule for your dataset to allow it update as scheduled. To do this you need to navigate to the "Datasets + dataflows" settings as shown in diagram below. Then select Schedule refresh.

Screenshot showing how to navigate to Data source settings in Power BI service

You might need to configure the data source credentials to ensure refresh can be scheduled. This is illustrated in the diagram below.

Screenshot showing error message for data source credentials

To solve the issue, we need to click on Edit credentials and enter the right authentication. In my case, the Authentication method was "OAuth2" and the Privacy level was "Organizational".  After this you would be prompted to enter the account name and password for this authentication, just remember to use the account username and password for you DevOps account as that is the source of the underlying data.

Screenshot showing how to configure authentication for the data source

Once the authentication is done right, you would observe that the warning message earlier is now gone as seen in the diagram below.

Screenshot showing authenticated data source credentials

Also, the diagram below shows that you do not need a gateway usually for this kind of reports as it is a cloud source. Then just schedule your refresh as required, and your report is set.

Screenshot showing no gateway required and need to schedule refresh

If you are not too familiar with setting a refresh schedule in Power BI service, I would recommend you read my tip which demonstrates this in detail as part of other tip focus.

Next Steps
  • Read more about Azure DevOps Analytics here.
  • For more information on prerequisites on Analytics views see here.
  • Try this tip out in your own data as business requires.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, January 25, 2021 - 3:33:32 PM - Salam Back To Top (88100)
Thanks, yes it is working as your instructions now

Sunday, January 24, 2021 - 9:46:09 AM - Aigbe Kenneth Omorodion Back To Top (88095)
Hi, I think I actually wanted to use a Card Visual for the demo, but later opted to use a Slicer Visual which is the one you see the date on. All you need do is drag in your date field onto the canvas --> select the Slicer Visual --> On the fields section of the Date field dropdown change it from "Date Hierarchy" to "Date" --> then go to the Slicer Visual you have in the Canvas and click on the dropdown and select "Relative Date".
That is all! Let me know if you need any more help, I will do an edit on the post to correct the Card to Slicer Visual to prevent any confusion going forward.
Thanks

Saturday, January 23, 2021 - 8:20:07 AM - Salam Back To Top (88091)
Hi Kenneth, you indicate "I have created a Card....", can you please let me know which visual you use and how it is configured to show those boxes inside? thanks














get free sql tips
agree to terms