On premise server migration - Repointing of data source

[PaisleyPrince]

My Power BI reports currently source data from an on-premise SQL database however this database is being migrated to another on-premise server. In terms of repointing the reports to the new server, can this easily be achieved without having to manually having to  repoint each of the reports ?

[V-yubandi-msft]

Hi @PaisleyPrince ,

Thank you for your prompt response @AnalyticPulse @ibarrau .

I wanted to check if you had the opportunity to review the information provided and resolve the issue..? Please let us know if you need any further assistance.We are happy to help.

Thank you.

[PaisleyPrince]

Hi, thanks for your response. How would this work in practice ? Would the new parameter have to be inserted into every single current report ?

[ibarrau]

Hi. You can open power bi desktop, go to transform data and change the source step for the tables. As a good practice, AnalytcPulse is suggesting a parametrization. If you use parameters for the source, the next time you need a change, it's going to be really straight forward. Parameter examples: 

- https://www.youtube.com/watch?v=OnaDJkGOmIE

- https://www.youtube.com/watch?v=XIq5vN5oPf8

I hope that helps

[PaisleyPrince]

ok, thanks - so apart from going into each report manually and using parameters, there is no quick way to alter the source in all the reports ?

[R1k91 · Accepted solution]

have you tried to use TMDL view (preview feature in power bi desktop)?

you can script the entire model (all the tables) at once and with a Find and Replace substitue all the occurences of your old db with the new one and then publish.

https://learn.microsoft.com/it-it/power-bi/transform-model/desktop-tmdl-view

if you leverage Power BI Project you can even do it for all the reports at once.

https://learn.microsoft.com/it-it/power-bi/developer/projects/projects-dataset

anyway the parameters suggestion it's worth to be implemented asap.

[ibarrau]

I'm confused. Even if you have all the reports saved as TMDL, the user haven't specified that, you can't change the sources and credentials. You could write some windows script runing for all folders looking for the file with sources and try changing. The viability of doing it a script for all power bi desktop files can't be done. Credentials can't be done.
I hope @PaisleyPrince you have all developments saved as power bi project in a repo, otherwise this will be opening one by one to save again.

I hope that helps,

[ibarrau]

Ammm... It's really difficult to get rid of the manual operation. Depending on how many reports they are, it might be even faster. The only alternative I can think about is using the API. If your report's data source is only the sql you want to change. Then you could run API requests to power bi service with a change of source. The source should be already added to the gateway with credentials. You could do a get workspaces, get the reports of the workspaces, run a for changing sources and probably exporing the file to pbix to local so you can keep version of those files.

But again. If you have 15 reports. Do it manually, the script is not that easy to build. You need coding skills and expertise on Power Bi Rest API.

If your idea is to change directly in Desktop, then it's manual for sure. This is the request: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-datasources

The parameter is an strategy to let any source the possibility of change, because you will see the request only allows specific sources changes.

I hope that make sense

[AnalyticPulse]

hi @PaisleyPrince 
it's easily doable using parameterized data source, you just have to create a new parameter for your data source and everything else will sync seamlessly.

My Work:
Analytic Pulse Blog 
Docynx Productivity Tools