How to Get Started with Azure DevOps Reporting using Power BI
If you’ve found this blog, it’s likely because you’ve encountered the constraints of reporting within Azure DevOps (ADO). While ADO is sufficient initially, as your team or organization evolves in its development practices, you start to seek out reporting capabilities that align more closely with your specific team processes. The built-in reporting in ADO is designed for a wide user base and may not offer the level of customization required by many teams. The main roadblock for teams is understanding how to get started with reporting on ADO work items using Power BI.
This blog aims to guide you through the complexities of querying, extracting, and generating Power BI reports using data from ADO by listing blogs from the Azeeki site as well as other sources I use for reporting. Whether you’re at the beginning stages of reporting ADO data with Power BI or seeking more sophisticated methods, this blog is here to enhance your report-building expertise.
This Blog:
The initial set of blog posts focuses on the use of Analytic Views as a data source for the basic workitem reports. This approach simplifies the initiation process into Power BI reporting by eliminating the complexities involved in setting up an ODATA data source. With ADO’s user interface, you can assemble and filter work items without the necessity of formulating and troubleshooting ODATA queries.
Upcoming blog entries will delve into the use of ODATA queries for reporting on ADO pipelines, testing, and repositories, among other topics. Our aim is to evolve from the simple work item reporting using analytics views to more intricate reporting involving various aspects of ADO.
Keep an eye on this blog series for future topics, and feel free to contribute through comments and suggestions regarding additional reports!
Beginner Reporting:
The beginner section is separated up into each phase of the report development process. The sections contain links to online documentation or blog posts to help start the report development process and answer questions.
Data Extraction – Getting the Workitems into the Report
This section focuses on creating the data source to ADO from the Power BI report. It discusses the process for creating the Analytics Views and how to connect them to the Power BI report. Start your ADO reporting journey here.
What are Analytics Views and How to Create One
Benefits for using an Analytics View for Reporting with Power BI
Introduction on Creating an Analytics View and Connecting to a Power BI Report
Microsoft Help: A Walk Through on Creating an Analytics View and Connecting to a Power BI Report
If you are having trouble figuring out what values to fill in to the ADO Analytics View connector within Power BI check out this blog post.
Data Transformation – Cleaning and Adjusting the Workitem Data
The next step in the report development process is to transform or clean the data. The data types in the AV may not match what is needed in the reports and some adjustments are needed on the columns. This helps with visualization of the data in charting.
Learn how to set the date columns from ADO to the correct data format. Follow the steps in this blog to set to just date format.
Create a searchable work item ID field to use in slicers on the report. This allows the user to search by workitem ID.
Shorten the Area or Iteration Path to make it easier to view in graphs and tables. The Area and Iteration Path by default in the Analytics Views and ODATA queries shows the entire path. This blog shows you how to shorten to make it easier to read.
Make changes to the Analytics View, like adding a field, and propagating the change to Power BI. After you have created the Analytics View, there are times you will need to add or remove a field or add a filter. This blog shows you the process and making sure it appears in the Power BI report.
This help file reviews the steps to expand columns, transform lead and cycle times to whole numbers, convert the DateSK fields to date columns, and other transformations.
Power BI Data Model Options:
Review this blog post on the different data models I have used for reporting with ADO data. There are some strategies to make the rollup and other calculations between work item types easier when the data model is planned out.
Working with Tags:
Create a table of workitem tags from an Analytics View query. This is helpful to create slicers to filter the data or create reports on Tags.
Need to detect if a tag is associated with a workitem? Check out this post write a calculated column to do the detection of an assoicated tag.
Create a list of Workitem IDs and Tags for filtering or querying of data. The blog explains the process of creating the table of tags from an existing Analytics View.
This blog is similar to the previous one with the difference being this is a table of Tags from an ODATA query. The other blog references using an Analytics View for the source of Tags.
Visual and Graphing Help:
A cool feature is to open ADO from the workitem ID using a hyperlink from the report. This blog walks you through the steps to implement this trick.
View the Workitem’s Description or other HTML fields from the workitem. First, follow this blog on how to bring in the HTML or Rich text data into the report. This blog shows how to show the HTML field as a popup report. Very helpful tip.
Publish Report and Managing Data Refresh Schedule
Your Power BI report is now ready for an audience. Follow the steps in this blog post to learn how to publish the report, set the correct credentials to authenticate to ADO, and setup the data refresh intervals.
Intermediate Reporting:
Data Model for the Power BI Report:
Start on this blog that describes the data model options for the Power BI report. This posts go from easy to implement, to using more advanced options with ODATA and parent/child relationships. The data model is the backbone of the report. If you have a solid data model, then the calculations in the report are easy to implement.