Forum Discussion
SSRS to Powrer BI migration
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!
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.
7 Replies
bradsyMicrosoft Employee
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-Power-BI/m-p/3272107
v-sgandrathiCommunity Support
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.
- Poojara_D12
Super User
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.
