Azure DevOps OData Reporting vs Analytics Views
Azure DevOps Reporting using OData can open up many opportunities when creating reports in Power BI. The Azure DevOps product group is encouraging users to utilize Analytics Views for your Power BI reports. For users just starting with ADO reporting in Power BI, this is the best method to use. It is a first-class connector in Power BI that connects to AVs and retrieves work items. As I have mentioned in other blogs, this is a great way to start reporting for smaller teams and projects with work item counts in the tens of thousands. You won’t find me trying to talk you out of using AVs for work item reporting when first starting.
However, at some point, you will encounter limitations.
The first limitation is that the definition of the data source exists outside of Power BI. Any time you need to add fields, relationships, or make structural changes, you must leave Power BI, modify the Analytics View, and then synchronize those changes back into the report. As your reporting requirements grow, this process becomes increasingly difficult to manage. Another challenge is incremental refresh, which is either difficult to implement or not fully supported depending on the scenario. I will cover both of these topics in future blog posts.
The alternative to Analytics Views is the ODATA connector.
Why ODATA
Using the ODATA connector allows you to take your ADO reporting to a completely different level. Instead of being limited primarily to work item data exposed through Analytics Views, you gain access to other Azure DevOps entities, including repositories, boards, testing, builds, releases, pipelines, artifacts, and more. This allows you to build reports that connect the entire DevOps lifecycle together. For example, you can trace a work item from backlog planning to source control, through the build process, and ultimately into deployment environments. That alone creates reporting opportunities that are difficult or impossible to achieve with Analytics Views.
The most important reason is using the ODATA connector allows for advanced reporting techniques such as passing parameters and preparing to export the data from ADO to Microsoft Fabric. The AV connector does not allow for the output of ADO data to an external source. Moving to Fabric enables your team to setup incremental refresh to retrieve the changed or new records to speed up the refresh process. It also allows for creating advanced calculations on larger data sets to create trend reports. These are things that are not available in ADO and using the AVs connector.
OData also provides capabilities that become increasingly important as the reports become more complex. Parameters can be passed into the ODATA queries to reduce the amount of data queried. This improves performance, reduces refresh times, and creates more flexible reporting solutions. More importantly, ODATA provides the foundation for moving Azure DevOps data into a Microsoft Fabric data warehouse or SQL database.
Once the data is in Fabric, the reporting possibilities expand significantly. You can implement incremental loading strategies, store historical snapshots, work with larger datasets, and create advanced trend reporting that ADO cannot create. Rather than reporting only on the current state of your work items, you can begin analyzing change over time, deployment patterns, team velocity trends, and cross-functional relationships between ADO entities.
This blog series will show how to use ODATA to unlock those capabilities and create a reporting platform that goes far beyond what Analytics Views were originally designed to support.
Blog Series
- Blog 01 – Start Here – Azure DevOps OData Reporting Without Analytics Views
- Blog 02 – Parametrize the ODATA query
- Blog 03 – Sliding Time Window to Reduce Data Model Size
GitHub Examples
Jump to the series with example files in GitHub starting here.