We aim to update only the schema name in the Custom SQL-based Power BI Reports and Dashboards

[ganeshsalunkhe9]

Hello,

We are facing a new challenge with updating over 200+ custom SQL-based Power BI reports that require a schema change in the power query. Is there an automated method to implement these changes on the report server, or any alternative to manually updating the SQL in each report?

[Anonymous]

Hi,@Vanchy_Liao .Thank you for your reply,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello, ganeshsalunkhe9 .I am glad to help you.
For automatically updating architectural queries in power query (changing information such as data sources), a better approach is parameter management in power query (using parameters instead of hardcoding).
Using parameters makes administration easier (when I have ten reports, each using the same parameter representing the data source)
I just need to change the value of this parameter uniformly using the API or power shell to modify the architecture information in bulk.
If you take a hard-coded approach, I think it's better to use an external tool (like powershell) to do the batch processing by executing the code

Solved: Getting Error while updating datasource using Rest... - Microsoft Fabric Community

6 Ways to Change Data Source in Power BI - Power Tech Tips

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vanchy_Liao]

@ganeshsalunkhe9 ,

although the account/password indeed stored in sql server, i don't recommand you to update it by sql directly because it encrypt the password by certain mechanism.

you can use powershell or report server api to change credentials by script.

follow this document to use powershell

[ganeshsalunkhe9]

Thanks for the document, we aim to chage the existing SQL queries with the schema name directly.So i think this will not help to update the reporting queries.