Forum Discussion
Retrieve Historical cut of Work Items from DevOps into PowerBI via Power Query
Hello,
To track historical changes for all work items within an Azure DevOps project and visualize them in Power BI, you can follow an alternative approach using Azure DevOps Analytics extension and Azure DevOps OData API to pull the data into Power BI. Here are the steps to achieve this:
Install the Azure DevOps Analytics extension: This extension allows you to connect to Azure DevOps data and provides a more structured and user-friendly way to query the data compared to the raw JSON format from the REST APIs.
Enable Analytics for your organization: Once the extension is installed, you need to enable Analytics for your Azure DevOps organization. Go to Project Settings > Boards > Queries > Analytics Views, and then create the Analytics views you need, like a view for tracking changes in work items.
Write an OData query to fetch historical changes: Using OData queries, you can fetch the historical changes for work items, including field value changes, relations, links, attachments, and comments. OData queries provide more flexibility and control over the data you want to retrieve.
Import data into Power BI: In Power BI, use the OData connector to import the data from the Azure DevOps Analytics endpoint. Power BI supports OData queries and can handle the historical data from work items.
Transform and visualize data: Once the data is imported into Power BI, you can transform it as needed and create visualizations to show historical snapshots of a single work item. You can build charts, tables, and other visualizations to represent the changes over time effectively.
Schedule data refresh: Since the data in Power BI is connected to the Azure DevOps OData API, you can schedule a periodic data refresh in Power BI to keep your reports up-to-date with the latest changes in Azure DevOps work items.
It's important to note that the specifics of the OData queries will depend on the exact data you want to retrieve, including filtering, sorting, and grouping. You may need to experiment with the queries to get the data in the desired format.
By using Azure DevOps Analytics and OData queries, you can overcome the limitations you faced with the raw JSON format and the "Web" source option in Power BI. This approach provides a more robust and scalable solution to track historical changes in work items and create insightful reports in Power BI.
Resolver IIIHello,
To track historical changes for all work items within an Azure DevOps project and visualize them in Power BI, you can follow an alternative approach using Azure DevOps Analytics extension and Azure DevOps OData API to pull the data into Power BI. Here are the steps to achieve this:
Install the Azure DevOps Analytics extension: This extension allows you to connect to Azure DevOps data and provides a more structured and user-friendly way to query the data compared to the raw JSON format from the REST APIs.
Enable Analytics for your organization: Once the extension is installed, you need to enable Analytics for your Azure DevOps organization. Go to Project Settings > Boards > Queries > Analytics Views, and then create the Analytics views you need, like a view for tracking changes in work items.
Write an OData query to fetch historical changes: Using OData queries, you can fetch the historical changes for work items, including field value changes, relations, links, attachments, and comments. OData queries provide more flexibility and control over the data you want to retrieve.
Import data into Power BI: In Power BI, use the OData connector to import the data from the Azure DevOps Analytics endpoint. Power BI supports OData queries and can handle the historical data from work items.
Transform and visualize data: Once the data is imported into Power BI, you can transform it as needed and create visualizations to show historical snapshots of a single work item. You can build charts, tables, and other visualizations to represent the changes over time effectively.
Schedule data refresh: Since the data in Power BI is connected to the Azure DevOps OData API, you can schedule a periodic data refresh in Power BI to keep your reports up-to-date with the latest changes in Azure DevOps work items.
It's important to note that the specifics of the OData queries will depend on the exact data you want to retrieve, including filtering, sorting, and grouping. You may need to experiment with the queries to get the data in the desired format.
By using Azure DevOps Analytics and OData queries, you can overcome the limitations you faced with the raw JSON format and the "Web" source option in Power BI. This approach provides a more robust and scalable solution to track historical changes in work items and create insightful reports in Power BI.