The Azeeki Blog
Start Here
New to Azure Dev Ops reporting? Don’t worry, we’ve got your back. Start here to learn the basics.
Explore the Blog
Filter Work Items by ADO Tags in Power BI
Filter Work Items by Tags Creating a report in Power BI using work items from Azure DevOps (ADO) offers a powerful way to visualize and analyze project data. One particularly effective feature is the ability to filter work items by tags. Tags allow teams to categorize and segment their work items according to various criteria, such as priority, team, or sprint. When these tagged work items are imported into Power BI, users can create dynamic and interactive reports that provide a focused view of specific aspects of the project. This blog post shows how to create a filtered table of workitem tags to use as the source of a slicer on the
Optimize your ADO Reports with a Star Schema Data Model in Power BI
Optimize the Power BI Report with a Star Schema Data Model using Analytic Views or ODATA Using a star schema data model in your Power BI report to link iterations and area paths to the work items fact table offers several benefits. This schema enhances data integrity by ensuring consistency and reducing redundancy, and it is scalable, allowing for easy addition of new dimensions. It also facilitates better data analysis with drill-down capabilities and comprehensive insights and provides a user-friendly structure that makes reporting more intuitive and interactive. Optimize your ADO reports with a star schema data model in Power BI by making your data model more efficient, scalable, and user-friendly, enhancing
Implementing an Incremental Refresh Policy for Analytics Views with Historical Data
Creating an Incremental Refresh Policy in Power BI for Analytics Views Containing Historical Data If you are using azure DevOps Analytics Views (AV) to create Power BI reports and dashboards, you might have encountered some challenges with the size and performance of your datasets. Azure DevOps analytics views provide a rich and comprehensive source of historical work item data, but they also return a large amount of rows and columns that can quickly exceed the Power BI service limits and affect the refresh time and user experience of your reports. To reduce refresh times, this blog post will show the benefits of implementing an incremental refresh policy for the ADO Analytics Views
Best Practices for Writing OData Queries for Azure DevOps Reporting
Efficient Workflow for Writing ODATA Queries The Azure DevOps documentation for writing ODATA queries and adding to the Power BI report advises you combine the ODATA query to a single line when adding to the Power BI report. This is not a good idea! Instead, follow the steps in this blog post to simplify the process creating, testing, and debugging the queries. This blog post will show you efficient steps for creating and connecting the ODATA queries for extracting the data from ADO and adding to the Power BI report. These are best practices for writing efficient ODATA queries for Azure DevOps analytics data. Tooling and Environment for Creating and Testing ODATA
Detect if a Tag is Associated to a Work Item in Power BI Reporting
Is a Tag Associated to a Work Item? With Azure DevOps, you can use tags to manage your work items, such as user stories, tasks, bugs, and features. Tags are words or phrases that you can attach to work items to group, rank, or filter them. However, when you extract tags from ADO using the Analytics Views or ODATA query, they are a comma separated list of values. This makes it difficult to filter in Power BI or do calculations to determine if a tag is associated to a work item in the Power BI report. In this blog, I will demonstrate how to check for tags associated to a work item
Search by Work Item ID in the Power BI Report
Your ADO organization probably generates a high number of work items and Power BI is an excellent reporting tool to help visual and track the work your team is assigned for an iteration. When creating Power BI reports using work items, it is important to be able to search for specific work items by the ID. However, by default, the work item data type is whole number or integer. The Power BI slicer only allows for searching for values if the data type is text. Users of the Power BI report may want to focus on a specific work item and providing them with a method to search by Work Item ID
How to create a workitem tags table with Power BI
Tags are useful for categorizing and filtering work items in Azure DevOps. However, when you want to create Power BI reports based on the tags, you may encounter some challenges. For example, how can you count the number of work items with a specific tag, or how can you compare the tags across different work item types? In this blog post, you learn how to create a workitem tags table for Power BI reporting. This table will enable you to create more flexible and powerful Power BI reports with the tags data. The Work Item’s Tags field in the Analytics Views is a comma delimited list when working with the field in
Connect Power BI Services to SQL Server with a Service Principal
A guide to securely and efficiently connect the Power BI Service to SQL Server with a service principal. A service principal is useful for accessing SQL Server from the Power BI Service as it is an identity that represents an application or a service in Azure Active Directory (AAD). Unlike a user account, a service principal does not have a username or password, but rather a set of credentials such as a client ID and a secret, or a certificate. Using a service principal, you can authenticate and authorize your application or service to access various Azure resources, such as SQL Server, without requiring a user to sign in. This blog post
Configure the Power BI Incremental Refresh for Analytics Views in Azure DevOps
Incremental Refresh in Power BI for ADO Analytics Views Ensuring that work item reports remain useful requires a performant refresh action to keep the report up-to-date with the latest work item values. Configuring the incremental refresh policy on the analytics view in Azure DevOps will ensure the dataset is refreshed quickly and the latest work item values are ready for reporting. If it is not enabled, the refresh process brings back ALL the work items and this can dramatically increase the refresh time This blog post covers the steps to configure the incremental refresh for an analytics view for current work items. There is a different process for configuring incremental refresh for
View the HTML or Long Text Fields from a Work Item in a Power BI Report
Creating a Popup Report to View the HTML or Long text field from a Work Item In this blog post, I showed how to obtain the work item’s long text or HTML to the Power BI dataset. This enables you to create a report, or use a visual to show the formatted data. This post discusses the process for creating a popup report to view the HTML or long text value from the work items. Adding the HTML Visual to Power BI Desktop The HTML visual is not native to Power BI desktop. Search Visual gallery for an appropriate visual to format and display the long or HTML text. In the Visualizations
Add the HTML and Long Text fields from a Work Item to an Analytics View
Add the HTML and Long Text Fields to Analytics Views and ODATA Queries in Power B To add the HTML and long text fields to an existing Analytic View or ODATA query in the Power BI report, you need to add a custom function to the Power Query editor to return the values through the API. A simple custom function can be used to return the work items’ values and is easy to implement. Please note that the work item’s long text or HTML fields are not available by default in the Analytics Views or ODATA queries. Data Modeling: The Work Item ID field’s data type is numeric by default. This data
Learn how to Open the ADO Work Item the Power BI Report
Quickly Open the ADO Work Item to view the Details from a Power BI Report Opening the ADO Work Item to view the details from a Power BI report is one of the most useful reporting. The users want to see the data in Azure DevOps from the Power BI report without having to open a new browser window and search on the Work Item ID. This blog post shows you how to add the code to open the work item from the Power BI report and view in Azure DevOps. This is simple to implement and requires some trivial DAX code to make it work. Create the Measure Add a new
Clean and Transform the Analytics View’s Data
Clean and Transform the Analytic View’s Data to make it Visual Friendly Making the default data types from an Analytics View graph friendly means changing to a more readable format. Most of the default data types for the fields in the analytics view are appropriate for visuals in the Power BI report. There are a few fields I normally change for the report design process using the Power Query Editor before building calculations or reports. This blog post shows how to clean and transform the data from ADO using the Power Query editor. Select the Home button on the menu bar and Transform Data. This opens the Power Query Editor. Become familiar
Shorten the Area Path and Iteration for Easier Graph Readability
Steps to Make the Area and Iteration Path Easier to View In some Azure DevOps projects we see long string values for Iteration Path due to nesting of iterations under common parent iteration. This long string value is not friendly for usage in Power BI visuals, such as bar graphs or table. This blog post shows you how to shorten the Area Path and Iterations by replacing the long or repetitive string values in a dataset. The goal is the show the second level area path node. This removes the word AzureDevOps from the path. Remove the First Node in the Area Path: Open the Power Query editor by selecting the Transform
Make Changes to the Analytic Views after using in a Report
Adjusting the Analytic Views and Propagating the changes to Power BI In some cases, you may need to make changes to the analytic views after establishing the initial connection. The most common scenario involves adding or removing a field from the view. The new field will not automatically become visible in the Power BI data model. Follow these options to propagate the change to the data model so the field is available for reporting. Review this blog for instructions on how to create an Analytic View and connect to the Power BI report. Update the Analytic Views Update the analytics view with the new fields, work item type, filter, date range, etc.
Create an Enterprise Azure DevOps Report with Analytics Views
Create an Enterprise Dataset for Multiple Team Projects ADO users commonly write work item queries to construct dashboards or export data to Excel for reporting. Converting these queries into analytic views as the primary source of reporting on work items is a straightforward process. Analytic views leverage the ADO analytics service, emphasizing performance in data retrieval and reporting. The issues arises when trying to merge multiple analytics views into a single Power BI table for reporting. The following blog post will show you how to create an enterprise Azure DevOps report with Analytic Views by querying across the ADO Team Projects and Organizations. This approach actively addresses the challenge of merging multiple
Creating an Analytics View and Connecting to a Power BI Report
Using an Analytics View as the Source for your ADO Report The simplest way to start reporting with Power BI is to use an Analytics View as the source for Azure DevOps work items. Analytics Views are easy to create and manage. However, some parts of the UI wizard need extra explanation to make sure the view shows the right data. This blog post goes over the steps to create an Analytics View and connecting to a Power BI report with the latest work item data. This means that the report will display the work item values that match what you see in the web browser for Azure DevOps. Creating the Analytics
A Guide to Seamless Power BI Reporting with Azure DevOps Analytics Views
The Benefits of Using Analytics Views for Power BI Reporting In the ever-evolving landscape of software development, the necessity to promptly and comprehensively monitor project progress is paramount. The integration of Azure DevOps with Power BI reporting, specifically through the utilization of analytic views, presents a straightforward solution for observing both the active and historical states of work items. Analytic views within Azure DevOps act as a catalyst for creating Power BI reports, enabling a comprehensive overview of your project’s status and facilitating the tracking of both active and historical states of work items. This blog post delves into the pivotal advantages of employing analytic views while also addressing their limitations. An
Introducing the Azeeki Blog Series-Unlock Azure DevOps Data
Companies face numerous challenges when it comes to gaining visibility into the progress and performance of their software projects. This task becomes even more daunting when dealing with multiple projects spread across the entire enterprise. To address these challenges and unlock valuable insights, companies often need to explore reporting tools beyond the capabilities offered by Azure DevOps (ADO). This blog series is meant to help your company unlock Azure DevOps data by providing guidance on creating an enterprise data model in Power BI. This enables the creation of reports to help project managers and stakeholders understand project health, identify potential bottlenecks, and make informed decisions to ensure successful project outcomes. Although ADO
How to Determine the Azure DevOps Organization and Team Name
Quickly find the ADO Organization and Team name to use for Connecting to an Analytic View in Power BI A common questions for those getting started with Power BI reports connecting to an analytic view in Azure DevOps is “How do I find the Azure DevOps Organization and Team Project Name for the connector?” Azure DevOps Organization and Team Names The two URLs to use when connecting to ADO are https://Organization-Name.visualstudio.com and https://dev.azure.com/Organization-Name. My ADO Organization name is MyPFESite and this how the URL appears in the browser: The Team Project is this situation is Code. To connect to an analytic view saved in the Code team project, I would use MyPFESite