Is there a way to bulk change the data sources on PBI Paginated Reports (Cloud)

[jbenfield]

Is there any easy way (or any way at all) to bulk edit the data sources and gateway connections on a set of PBi Paginated Reports in the cloud?

We have a pool of reports that are currently all pointed to Dev/Test servers and we need to move the whole mess to production in a few months. Our original plan had been to do everything via a data warehouse and just update the sources there rather than for each report, but for various reasons we ended up having to go with direct data source connections. 

I've seen references to using the PBI API to automate this, but I couldn't find any details or examples, just vague "use the API, Luke" hints. 

Alternatively, is there a way to parameterize the connection names to make them easier to redirect when we promote paginated reports through environments?

[v-saisrao-msft · Accepted solution]

Hi @jbenfield 

Thank you for reaching out to the Microsoft Forum Community. 

I understand the challenge of updating data sources and gateway connections across multiple Power BI Paginated Reports. Here are some solutions that can help you achieve this efficiently: 

Use Power BI REST API for Bulk Updates 

The Power BI REST API is the most effective way to automate bulk updates. Here’s how you can do it: 

Steps:

1.Use the get report api to list all Paginated Reports in your workspace. 

Reports - Get Reports - REST API (Power BI Power BI REST APIs) | Microsoft Learn 

2.Use the get datasource api to retrieve the current data source details for each report. 

Datasets - Get Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn 

3.Use the update datasources in group api to replace Dev/Test server details with Production server details. 

Datasets - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn 

4.Write a script to loop through all reports and update their data sources. 

 Datasets - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn 

While Paginated Reports in Power BI do not natively support parameterizing connection strings or data source names, you can achieve a similar effect by centralizing connection management. 

• Create a shared dataset in Power BI that connects to the data source. 
• Ensure that all Paginated Reports reference this shared dataset. 
• When migrating to Production, simply update the data source of the shared dataset, and all reports will automatically reflect the new server connection. 

For more details, please refer to the following links:
Introduction to semantic models across workspaces - Power BI | Microsoft Learn 

Paginated reports in Power BI: FAQ - Power BI | Microsoft Learn 

If this post helps you, please mark it as the solution and give a kudo so that other members of the community can easily find it helpful. 

Thank you.

[jbenfield]

That looks perfect. I haven't had a chance to implement yet, but it seems like the best solution for a mass migration at go-live. Thanks!

[GilbertQ]

Hi @jbenfield 

The good news is that there is a way to parameterize your connection names so that it'll make it easier to change it in the future. Here is a blog post explaining how to get this done. Implement Dynamic Data Sources in SSRS - MSSQLTips.com

[jbenfield]

Thanks for your response. That's a great solution and we may implement something like it for future reports. But https://community.fabric.microsoft.com/t5/user/viewprofilepage/user-id/883010 provided a way to automate the source changes and that's what we'll probably do for this pass. 

Again, thanks for your help!