If you have been following this blog, you have noticed already how big fans of Azure DevOps we are, and how much we like all the features that come with the tool, especially those that are not so well-known.
One of those fantastic features is the ability to create your own dashboards.
They are amazingly easy to use, and any user without technical knowledge can create and customize dashboards in a matter of minutes.
These dashboards are great and all, but they are limited. There is only so much that you can do. And for users like us, who are constantly pushing and trying to find the limits of the tools we use, this is simply not acceptable.
Fortunately, in addition to being Azure DevOps users, we are also Power Platform fans, and we already know that we can combine the power of both.
Analytic Views
Analytics is a free extension for Azure DevOps created by Microsoft available in the marketplace, that includes additional widgets for your dashboards as well as the internal components required to integrate DevOps with Power BI.
The way to install this extension is through the Marketplace icon available in the top right corner of the App. From there, all you have to do is to type “Analytics” on the search bar, and then get it for free. Remember you need to be an Organization admin to install any extensions, and when you install it, will be available in all projects.
Installing the extension, will enable a new section in the navigation bar, called the Analytics View.
This new section allows us to configure what is visible from Power BI. Is like the views of DevOps we want to make available to use in our BI reports.
There are a few views created by default that you can edit and customize, for example, to add your custom fields, but you also can create your new ones from scratch.
On the details dialogue, you can decide what fields you want to make available, but also, what I find most interesting, is the history options. The history options allow us to define how far we want the view to go back in time.
Is like being able to know exactly what tasks you had in the project 30 days ago so you can compare the to the current status. To me this is mind-blowing.
Just for curiosity, what DevOps does to be able to provide this functionality, is to store the data in a structure called a Periodic Snapshot Fact Table. The way this works is that every time you save a change in a Work Item, DevOps doesn’t overwrite the original record. What it does instead is to create another record in the table, with the new values and the date the operation took place.
This function was there since the beginning. Azure DevOps uses this to be able to report on the progress of the Work Items and the projects in general, for example, to create a Burndown chart. Installing the Analytics extension only exposes it to the reporting tool. If you want to know more, check out the ASOF operator in Wiql queries.
Connecting from Power BI
Now, let’s try to create a very simple Power BI Report step by step.
Start by opening Power BI Desktop and clicking on the Get Data button in the ribbon. From there, select the Azure DevOps connector.
The next step is to provide the organization and the project name. You can get those from the Azure DevOps URL you are using to access the environment. For example, if your address is https://dev.azure.com/acme/timesheetsmanager/ the organization name is acme, and the project name is timesheetsmanager. You will be asked to provide your credentials when connecting.
Now we need to choose what tables we want to bring to our Power BI report. To keep it simple I will be selecting only one table, the Bugs in the last 30 days. When you are ready, click on the Load button.
From here onwards, everything is very simple. We have all the visualization and data transformation tools that Power BI provides to create all different types of reports. What I will do, is build a report that will allow me to compare what was the status of my bugs throughout the month, so I can tell my boss how much progress we have made.
What I will do is use a timeline slicer that will allow me to select the date. Once I add it to my report, all I need to do is to connect it to the Date field on Bugs Table. This will then show the days I have in the last month.
Next, I will add a slicer to show the people who were working on bugs in the last month.
Last, a few tiles show the count of records on the selected day, and a few pie charts display the same information graphically.
Here is the result. Just a simple example that shows the possibilities. There is no way you can achieve something like this using only the out-of-the-box dashboards.
Imagine using this technique to report on pretty much anything you are storing on your DevOps instance. Not only bugs or tags, but also Requirements, or even the Solution Components you have.
Conclusion
This is a technique that I learnt a while ago. It is just the tip of the iceberg of the possibilities the combination of Azure DevOps and Power BI provides us.
In a future post, I will show you how to integrate your BI Reports directly into the Azure DevOps interface.
I hope you find it useful. If you have any comments or queries, please leave them below.
More information
- https://devblogs.microsoft.com/devops/analytics-for-azure-devops-services-is-now-generally-available/
- https://learn.microsoft.com/en-us/azure/devops/report/powerbi/analytics-views-create