Using Azure DevOps Analytics views and Microsoft Fabric to create Sprint review reports

Reading Time: 8 minutes

In this post I want to cover using Azure DevOps Analytics views and Microsoft Fabric to create Sprint review dashboards.

I consider this post to be a sequel to one of my post popular posts that covered using Azure DevOps Analytics views and Power BI to create Sprint review dashboards. For four very good reasons.

Reasons

First reason is due to the fact that Azure DevOps Analytics views finally became GA (Generally Available) this year as part of the April 2023 Azure DevOps updates. Considering my previous post is from March 2021 it has been a long time coming.

Second reason is because this post covers Microsoft Fabric. Which was announced earlier this year and is currently in public preview. I have written various posts about it already, including my last post on how to prepare Azure DevOps for Microsoft Fabric Git integration.

Third reason is because it gives me an opportunity to show some changes in Power BI Desktop since my last post. Like the new way to change the minimum value shown for a Y-axis.

Fourth and final reason is because I show what happens when you publish a report that uses import mode into Microsoft Fabric using Power BI Desktop. Because I know some people are curious about some aspects of this.

If you want to follow and need to create Microsoft Fabric and Azure DevOps environments, I recommend my other post about creating your own Microsoft Fabric environment. Which covers setting up both of these services.

Before you read any further, I must highlight that Microsoft Fabric is now generally available. You can read more about this in detail in the official post by Ryan Majidimehr.

Plus, this post contains plenty of links.

About Azure DevOps Analytics Views

Analytics Views is a feature that can be found in Azure Boards within Azure DevOps. To help with some jargon here, Azure Boards is the service in Azure DevOps that manages your work items.

I first covered Analytics views in a previous post about moving away from spreadsheets. Back then it was still in preview and stayed in preview up until this year.

An Analytics view is a way you can present metrics based on various work item types in Azure Boards to be consumed for Power BI in a very user-friendly way. For example, metrics about time spent on tasks during a sprint.

At this moment in time, Analytic views only support metrics relating to work items in Azure Boards within Azure DevOps. For example, the details about backlog items completed in the last 30 days.

Which is very useful for product owners or anybody else who wishes to report on work items. Because it gives them the possibility of creating powerful dashboards for their stakeholders.

You open Analytics views by going into Azure DevOps, selecting ‘Boards’ and then selecting ‘Analytics views’ from the list of features.

Creating new Analytic Views in Azure DevOps to get sprint metrics

In reality, there are a few ways you can make a sprint review report based on metrics stored in Azure DevOps available in Microsoft Fabric.

However, because I want to show what happens when you publish an existing report, I decided to use the same Analytics Views that I had created before. Which I based on the default ones shown below.

• Backlog items – Last 30 days
• Tasks – Last 30 days

I will quickly cover how I created the customized version of these views originally below.

First, I added the sprint number to the names of the new views to make them easier to identify.

For my customized backlog view, I added a filter for the current sprint.

For the view to get the task metrics I also added a filter for the current sprint. In addition, I added the remaining work field. Due to the fact that it is needed for the burndown chart that gets created.

Note: If you intend to do something similar you must be using tasks as part of your sprint (or iteration). In addition, you must be entering values in the ‘Remaining work’ column to show how many hours you have left to work on the task.

Plus, I highly recommend updating your estimate for the amount of remaining work left each day and the current state of your task.

If you are looking to copy the above, I recommend selecting edit the on the views you intend to copy. So that you can copy their settings into the new views as required. Like I have done with the History settings below.

Selecting the Azure DevOps Analytics views in Power BI desktop

Note: To follow along it helps to have a recent version of Power BI Desktop installed.

Once I had created the views, I was ready to create the dashboard in Power BI Desktop. First thing to do was to get the data using Azure DevOps (Boards only).

After clicking connect I then had to enter in the URL to my organization and the project name. Just to confirm, you can use spaces here if they are in your project name.

After I clicked on OK, I selected the two views that I created.

Creating a burndown chart

As part of the sprint review report, I decided to create two pages. One to represent a burndown chart and another one to show all the backlog items worked on.

In reality, you can extend this by adding other visualizations to the report. Like a velocity chart. In addition, you can show a pie chart of all the items based on their different tags if you really want to.

Right at the start I renamed the page to show what it was going to show. In my case, it was burndown chart. However, those who work with scrum will see towards the end that it is a bad burndown chart.

Afterwards, I selected the ‘Area chart’ visualization. I then selected the ‘Date’ field for the X-axis and ‘Remaining Work’ filed for the Y-axis. Be aware that you may have to change the X-axis to use the Data value instead of the Date Hierarchy for data to appear.

However, as you can see here this burndown chart is misleading. Because the remaining work value does not go down to zero on the Y-axis.

To change this, you can select the ‘Format your visual’ option in Power BI desktop. From there, navigate to the range value for the Y-axis and change the minimum value to zero as below.

You can now see a better representation of this burndown chart.

Creating dynamic backlog items page

Next, I looked at creating a dynamic backlog items page to show details about completed work items.

First of all, I selected the ‘Assigned To’ and ‘Title’ columns from the ‘Sprint 18 – Backlog Items’ view. Which created a static list on the screen. In addition, I went to the ‘Format’ section again and in the ‘Values’ section I made the font bigger.

As you can see this table appears to be static. I can click on the filter to expand it and get a few other options. However, I decided to make it a bit more dynamic by adding a slicer visualization.

Just by adding this slicer I created a more dynamic filtering mechanism fairly quickly. Once done I was ready to publish this report based on Azure DevOps Analytics views into Microsoft Fabric.

Publishing Azure DevOps report to Microsoft Fabric

Once the report was done, I published it into a new workspace in Microsoft Fabric. So that I can look deeper into what happens. As you can see, you get the same objects as when you deploy to Power BI.

Microsoft Fabric does not magically create a Lakehouse for you behind the scenes or anything. Which makes sense as far as migrating existing Power BI reports to Microsoft Fabric at scale is concerned.

I then went to check the folder for the workspace within OneLake File Explorer. However, I could not even see the folder for new workspace. I tested this and I can confirm that OneLake File Explorer will not show folders for workspaces that contain only reports and datasets.

Next I went to OneLake data hub. Where I was able to see my uploaded dataset there as an object.

From there, I tested connecting to the Dataset using SQL Server Management Studio. I did this by entering the XMLA connection, which you can find in Workspace settings.

This allowed me to view the tables based on Azure DevOps Analytics Views within Microsoft Fabric.

So, it appears that if you publish reports to Microsoft Fabric the traditional way through Power BI Desktop without tweaking anything it will be a similar experience as deploying to Power BI now.

As well as making migrating reports at scale easier this also allows for some interesting possibilities. Including Microsoft Fabric Git integration.

Due to the fact that reports and semantic models(datasets) are two of the three objects currently supported. Other supported items for Microsoft Fabric Git integration are Lakehouses, notebooks and paginated reports.

Way of working within Microsoft Fabric

However, one thing you can consider doing to make the most out of the premise of Microsoft Fabric is change your mindset and way of working.

For example, you can look to import your data into a Microsoft Fabric Lakehouse or Data Warehouse before creating the report. Which will make your data more available and allow it to be shared amongst the experiences.

Of course, there are over factors to take into consideration when considering this. Such as time and cost of compute. Feel free to share your thoughts about this.

Final words

I hope this post about using Azure DevOps Analytics views and Microsoft Fabric to create Sprint review reports is insightful. Since it contains a lot of updates since my original post two years ago.

Personally, I am really glad I got to do a sequel to my original post for various reasons. Including the fact that my original post became one of my most popular posts of all time.

Of course, if you have any comments or queries about this post feel free to reach out to me.