How to use Pipeline-Releases analytics to create powerbi reports?

[Kamasani_DIvya]

Hi,

I have few pipleline and using powerbi query to extract analytics data and create a powerbi report. (using below articles)

https://docs.microsoft.com/en-us/azure/devops/report/powerbi/sample-test-analytics-test-summary?view...

Now I have a Pipeline-Releases and would like to have powerbi / Odata query to create similar reports in PowerBI. I tried to use the query that are mention for the pipeline but it did' not worked. 

Can anyone let me know how could I extract the Releases analytics and use in the powerbi?

[PavanLalwani]

To create Power BI reports using Pipeline-Releases analytics from Azure DevOps, you can follow these steps:

### 1. **Understand the OData Endpoint for Releases**

Azure DevOps provides an OData endpoint for querying analytics data. For Pipeline-Releases analytics, ensure you're familiar with the specific endpoints that provide the data you need. The typical structure for the OData endpoint looks like this:

```
https://dev.azure.com/{organization}/{project}/_odata/v3.0-preview/
```

You will need to explore the available entities to find the relevant tables for releases.

### 2. **Identify the Correct OData Queries**

Refer to the [Azure DevOps REST API documentation](https://learn.microsoft.com/en-us/rest/api/azure/devops/overview?view=azure-devops-rest-7.1) for the specific analytics you want to pull. Look for entities related to releases, such as:

- `ReleasePipelines`
- `ReleaseEnvironments`
- `ReleaseDeployments`

### 3. **Use Power BI to Connect to the OData Feed**

1. **Open Power BI Desktop**.
2. Go to **Home** > **Get Data** > **OData Feed**.
3. Enter the OData URL for your Azure DevOps organization, project, and the relevant analytics data.

### 4. **Create the OData Query**

If the predefined queries are not working, you might need to construct your own OData queries. For example:

```plaintext
https://dev.azure.com/{organization}/{project}/_odata/v3.0-preview/ReleaseDeployments?$filter=releas... eq 'YourReleaseDefinitionName'
```

This query retrieves deployments for a specific release definition.

### 5. **Transform the Data in Power Query**

Once you’ve loaded the data into Power BI:

- Open the **Power Query Editor** to clean and transform the data as needed.
- Remove unnecessary columns, rename columns for clarity, and set data types.

### 6. **Create Relationships**

If you are pulling data from multiple tables (like Releases and Deployments), ensure you create relationships between these tables in the **Model** view of Power BI.

### 7. **Build Visuals and Reports**

Now that your data model is set up:

- Use the **Report view** to drag fields into visuals.
- Create charts, tables, and dashboards that provide insights into your pipeline releases.

### 8. **Scheduled Refresh**

After publishing your report to Power BI Service, set up a scheduled refresh to ensure that the data stays up to date with the latest analytics from Azure DevOps.

### Example of a Basic OData Query

Here's an example of an OData query you can try for releases:

```plaintext
https://dev.azure.com/{organization}/{project}/_odata/v3.0-preview/ReleasePipelines?$expand=Releases
```

This would pull in the release pipelines along with their associated releases.

### Conclusion

Using the OData endpoint from Azure DevOps, you can pull in pipeline-release analytics to create comprehensive Power BI reports. Ensure to construct your queries carefully and transform the data as needed in Power Query. If you encounter specific issues, please provide error messages or descriptions, and I can help troubleshoot!

[Kamasani_DIvya]

Hi PavanLalwani,

Thanks for your response. I tried the below link to find the entities in Azure devops.

https://dev.azure.com/{organization}/{project}/_odata/v3.0-preview/

but not able to find these entities

 `ReleasePipelines`
- `ReleaseEnvironments`
- `ReleaseDeployments

I tried below links also but getting this error.

https://dev.azure.com/{organization}/{project}/_odata/v3.0-preview/ReleaseDeployments?$filter=releas... eq 'YourReleaseDefinitionName'

1. First I checked this link till preview but getting this error.

2. https://dev.azure.com/{organization}/{project}/_odata/v3.0-preview/ReleasePipelines?$expand=Releases

I tried this also but getting same error like 404 error.

Please let me know how to resolve this.

[Anonymous]

Hi @Kamasani_DIvya 

Based on your description, you can consider to use odata connecrtor, e.g 

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRuns?"
               &"$apply=filter( "
	       &"CompletedDate ge {startdate} "
	       &")"
                &"/groupby( "
        &"(Pipeline/PipelineName), "
        &"aggregate( "
            &"$count as TotalCount, "
                &"SucceededCount with sum as SucceededCount, "
                &"FailedCount with sum as FailedCount, "
            &"PartiallySucceededCount with sum as PartiallySucceededCount, "
                &"CanceledCount with sum as CanceledCount "
            &")) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

You can refer to the following link,

Pipeline outcome summary for all pipelines sample report - Azure DevOps | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.