SSRS to Powrer BI migration

[ypyash75]

Hello All,

We are in the process of migrating approximately 250 SSRS reports to Power BI. Each report currently uses a single data source (an on-premises SQL Server) and contains multiple embedded datasets. We are using Power BI Report Builder to load these SSRS RDL files and then publishing them to the Power BI workspace one by one.

However, all these SSRS reports are currently using a shared data source. We would like to achieve the same setup in Power BI. The challenge is that our on-premises SQL Server will soon be migrated to Azure SQL. When this happens, we will need to update the data source and republish each report individually.

Is there a way to avoid this by using a shared data source in Power BI? Any guidance or suggestions would be greatly appreciated.

Thank you!

[Poojara_D12 · Accepted solution]

Hi @ypyash75 

Since Power BI Service does not support shared data sources for paginated reports, you will need a workaround to avoid manually updating all reports when migrating from an on-premises SQL Server to Azure SQL. One approach is to use parameterized connection strings in Power BI Report Builder. Instead of hardcoding the database connection, you can define parameters for the server name and database name and reference them dynamically in the report. These parameters can be linked to a centralized table storing connection details, allowing you to update the database information in one place rather than modifying each report individually. Additionally, you can automate updates using Power Automate or the Power BI REST API, ensuring bulk modifications without manual intervention. If you are using Power BI Report Server (PBIRS) instead of Power BI Service, you can create a shared data source similar to SSRS, where updating the connection in one place automatically applies the change to all reports. Another option is to leverage PowerShell scripts or REST API calls to programmatically update the data source for multiple reports at once, streamlining the migration process and reducing manual effort.

[ypyash75]

@bradsy thanks a lot for taking a look at it, however I am still confused, I have 250 reports, each report has 6-10 datasets, and unique connections strings are 7 in total. How to achive what I am looking for.

[v-sgandrathi]

Hi @ypyash75,

To manage over 250 reports using the Power BI REST API, ensure you operate within the rate limits and modify your approach accordingly.

The rate limit is 200 requests per hour per user or service principal per workspace.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Regards,
Sahasra.

[v-sgandrathi]

Hi @ypyash75,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thanks for using Microsoft Fabric Community Forum.

[v-sgandrathi]

Hello @ypyash75,

Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help. 

Thankyou for connecting with Microsoft Fabric Community.

[v-sgandrathi]

Hi @ypyash75,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thanks for using Microsoft Fabric Community Forum.

[bradsy]

You could create semantic models that get the data. Then have the paginated reports use the semantic models as a data source.  https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-understand 

There are also API's that can update data sources so you do not have to republish reports: https://community.fabric.microsoft.com/t5/Developer/Update-Datasource-in-Paginated-report-using-Powe...