How to change the SQL server datasource on a published report?

[unknown_object]

Hi all,

I'm pretty new to PowerBi and here's my situation, hoping to avoid this setup (or mistake) in the future:

- I created a report that connects to my local SQL server and its database, everything works and the report is done. 

- I have published the report to a workspace.

- I have setup the on premise data gateway on another machine that is running the target SQL server and its database. This is configured and up and running on my PowerBi portal, all good on this part.

- If I want to refresh my published report it fails, but that's normal because there is no connection to the dataset.

- In the settings of the semanctic model the datasource refers to my local SQL database connection, lets say SERVER-A;Database1

Desired result: 

The published report should be connected to a different SQL server (SERVER-B;Database1), the one that is accessible through the gateway. The database remains exactly the same. I just worked on a copy of it on my local machine. 

How can I point my report to the correct server to fetch its data? 

Much appreciated if anyone could help 😎

Thanks!

[v-jtian-msft · Accepted solution]

Hi,@unknown_object .I am glad to help you.
If you want to modify the data source of a report, whether it has been published to the service or not.
You can change the data source information via power query (transform data>Data Source settings)
Or you can modify the data source by modifying the M code directly through the Advanced Editor (I recommend this approach).
Luckily, I also found other similar issues that have been resolved, so I hope that helps.
URL:
Solved: How to Change Data Sources for Existing Power BI R... - Microsoft Fabric Community
Solved: Changing Data Source - Microsoft Fabric Community
like this:

Here are my tests:

I have two SQL Server databases with a table of the same name (you need to make sure that you don't have a huge architectural change after your data source change to avoid reporting errors in your reports)

First use databaseA as the data source to create a report

You can see that the table structure is the same but there is a difference in the data (the nowTime column is not the same).

Use AdventureWorksDW2017 as the data source to create the report:

Uploaded to service, configured gateway refreshed successfully (at this point the data source is AdventureWorksDW2017)

After modifying the data source AdventureWorksDW2022, click Refresh Preview
The data has changed

You can see that after uploading to the service, it shows that the data source credentials can not be verified through the gateway, at this time to reconfigure the gateway connection to the new database connection

After the configuration is complete, the refresh is successful and the service is displayed normally without reporting errors

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.

[unknown_object]

Hi @v-jtian-msft ,

Thanks for the extremly detailed post 😀

In all my attempts I was convinced that I had done these different approaches, maybe not in the right sequence...and mixing different guides or possible solutions.

But I started over following your instructions and I managed to get it to work.

All up and running now!

Thanks

[unknown_object]

Also in addition, I have already tried the <File - Options and Settings - Data Source Settings> in PowerBi desktop to change the values but then the report want to apply the changes and fails because I don't have access to the remote SQL server, therefore I cannot publish this updated version to my workspace (I think).