Change data source from Power BI Semantic model to on prem analysis services instance.

[henriklundberg]

Hi,

I have a report with a lot of work done that has a live connection to a semantic model i Power BI.

Due to reasons, I have moved the semantic model to an on prem SQL Server 2022.

Now I want to 'hot swap' the connetion so I can migrate the report keeping all work done on the visuals side.

'Hot swap' external tool doesn't seem to work (it works the other way around).

Does anyone know a way to just redirect a live connection in Power BI Desktop from a semantic model in Power BI service, to a tabular model hosted on prem SQL Server 2022?

Br,

Henrik

[JOOV2]

Hi Henrik,

I encountered a similar situation. Migrated from SSAS endpoint to a XMLA endpoint (Power BI Dataset).

After a few versions we needed to switch back, but indeed the UI doesn't seem to provide such a feature.

I managed to switch back from XMLA tot SSAS by doing this:

1) Save .pbix as .pbip (a .pbip file will be saved along with two folders: .Report and .SemanticModel)

2) From the .SemanticModel folder open the 'model.bim' file and look up and edit the connection string (replace the server and database/model). Save the edited file.

3) Open de .pbip file and check whether the change was successful.

4) Save file as .pbix. Done

Maybe there are other/simpler solutions. Let me know when you find any.

Good luck!

Best regards!

[henriklundberg]

Hi,

No, it seems 'hot swap' only  works in the direction of AS --> PBI semantic model. When trying the reverse through below steps.... the connection to the PBI semantic model is still there after step 6. So it doesn't remove... Any other ideas?

1. Download and 'hot swap'
2. Open a blank power bi desktop file – not the file you want to change. 
3. Under external tools click Hot swap connections. 
4. Choose the second tab called 'Remove' 
5. Click 'Overwrite and remove live connections' 
6. A windows explorer appears and now you choose the PBIX file you want to 'hot swap'. 
7. This will open a new Power BI Desktop, with that file, but the connection will not be there. Only the visualizations. 
8. Click Get Data - and choose the Power BI Dataset you want. 
9. Save and close.

[OwenAuger]

That's odd that it's not working for you.

I routinely use this method to change from PBI semantic model -> PBIX opened on local machine (using Analysis Services connection).

Regardless, you can do the same thing manually by opening a copy of the report PBIX as a ZIP file and deleting these two files:

• SecurityBindings
• Connections

(This is essentially what the Hot Swap Connections tool does in the Disconnect-PBIX function.)

Then you should be free to open the report and connect to an Analysis Services source.

Does this method work for you?

[OwenAuger]

Hi @henriklundberg 

You can use the Hot Swap Connections tool to remove the live connection to a Power BI semantic model from a report. Then you can connect that report to SQL Server Analysis Services (Get Data > Analysis Services).

See the "Remove tab" section here.

Are you able to get that working?