Optimizing Data Extraction with the Azure DevOps in Power BI
Integrating Azure DevOps data into Power BI can significantly enhance project visibility and decision-making for development teams. By leveraging the capabilities of Azure DevOps in Power BI, teams can transform raw Azure DevOps data into actionable insights. This integration not only brings transparency to the development process but also automates the reporting of key metrics, helping teams monitor progress, identify bottlenecks, and drive continuous improvement in their workflows.
Even though Power BI has a native connector for Azure DevOps, it may fall short of more comprehensive reporting needs as it lacks access to essential data, such as the ability to trace parent/child relationships and description of work items. This gap could make it challenging to gain a full understanding of our development activities and dependencies. By utilizing Azure DevOps in Power BI, teams can overcome these limitations and gain deeper insights into their data.
Consequently, we recommend using two approaches: OData and REST API. These methods provide greater flexibility and access to detailed information, allowing for more robust and insightful Power BI reports.
ODATA
Basic request
While connecting to the "WorkItems" table using the basic OData feed is straightforward, it comes with its own challenges. The simplicity of the connection process is evident in the following code snippet:
let
Source = OData.Feed("https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/v2.0/", null, [Implementation="2.0"]),
WorkItems_table = Source{[Name="WorkItems",Signature="table"]}[Data]
in
WorkItems_tableHowever, this approach returns all columns from the table, which can result in an overwhelming amount of data. Including too many columns can lead to inefficiencies and make it harder to focus on the specific information needed for meaningful analysis. Therefore, it's important to refine the data extraction process to ensure that only relevant columns are retrieved, improving both performance and clarity in your Power BI reports.
Explicitly mention only the columns you need
To avoid the issue of retrieving too many columns, you can explicitly specify only the columns you need by adding query parameters to your request. Using the "select" parameter helps to exclude unnecessary columns, resulting in a more efficient and manageable dataset. Here’s an example of how to use the "select" parameter:
&"&$select=
WorkItemId,
WorkItemType,
Title,
State,
ParentWorkItemId,
TagNames"Expand fields.
If you need to include fields that require expansion, use the "expand" parameter to achieve this. It is advisable to combine the "expand" parameter with a "select" parameter to specify only the necessary columns within the expanded field. This targeted approach ensures efficiency and clarity in your data retrieval. You can choose to expand all fields of a record:
&"&$expand=
AssignedToHowever, a more efficient approach is to expand only specific fields of a record. This method minimizes the data volume and focuses on the relevant information. For example, you can expand only certain fields within the AssignedTo record:
&"&$expand=
AssignedTo(
$select=
UserName,
UserEmail
)Complete ODATA M Code.
To illustrate how you can efficiently retrieve and expand only the necessary fields using OData, below is the complete M code example. This code demonstrates how to filter work items, select specific fields, and expand certain fields within the Azure DevOps data:
REST API
Even though OData is very powerful, it may still fall short in certain areas, such as retrieving the descriptions of work items. To get them, we need to leverage the REST API.
Getting your PAT
The first step for requesting information via the REST API is to obtain a Personal Access Token (PAT). You can generate a PAT through the settings option in Azure DevOps. This token will grant you permission to access and retrieve data using the REST API.
Creating a function
To retrieve work item descriptions via the Azure DevOps REST API, you can create a custom function in Power BI. This function uses a Personal Access Token (PAT) for authentication and constructs a web request to the Azure DevOps API. Below is an example of how to create this function:
Invoking the Custom Function
After creating the custom function to retrieve work item descriptions via the Azure DevOps REST API, you need to invoke this function within Power BI. Follow these steps:
Invoke Custom Function:
Go to the "Add Column" tab in the Power Query Editor toolbar.
Click on "Invoke Custom Function."
Configure the Function Invocation:
In the "Invoke Custom Function" dialog box, configure the following:
New column name: Enter a name for the new column that will hold the results of the custom function (e.g., WorkItemFx).
Function query: Select the custom function you created (e.g., WorkItemFx).
Parameter mapping: Map the parameter required by the function to a column in your query. For example, map workItemId to the WorkItemId column in your query.
Confirm and Load:
Click "OK" to invoke the custom function.
The new column will be added to your query with the results of the custom function for each row.
It is important to mention that the function only requests the work item's description, for additional information you will need to modify the function to include it.
Warning: Invoking the custom function for many items can significantly slow down your queries. It's important to know the performance impact when dealing with extensive datasets.
Additionally, the provided function only requests the work item's description. If you need to retrieve more information, you must modify the function to include additional fields. This flexibility allows you to tailor the function to meet your specific data requirements.
Great article and video. Does this approach work with hierarchy too? For example to see what percentage of open Epics are complete, load epics using OData and then all child features and PBIs using separate rest calls?