changing data source in existing PBIX when current data source is not available

[MarkU]

I have several Power BI workbooks that I need to transfer from one client to another -- just the report construction, not the data.  The data source schemas are the same in both places, so it should just be a matter of swapping out one client's data source for the other's to do the migration.  However, I can't be connected to both clients at the same time.  So, at client A, where I am migrating from, I can open the PBIX files, but I can't replace the data source because I am unable to connect to client B from there.  At client B, I can't even open the PBIX files, because their connection is to client A, which is not available.

All of the suggestions for ways to replace data sources I have seen are predicated on being able to open the workbooks in the first place.  Since I can't do that at client B, is there some other way I can replace the data source in these files without having to open them first?  One thing to note is that client A's model is hosted in SSAS and at client B, the model is in Power BI Services.  So it's also not a matter of just changing a connection string but with the same source type.  When I open the reports at client B, Power BI does give me the option to edit the connection parameters since client A's SSAS instance isn't available from there -- but I can only specifiy a new SSAS instance, not change to Power BI models as a source.  

[NaveenGandhi]

Hi

Does the report have a live connection to a SSAS model in client A? Also when you say in client B the model is in Power BI service, Is it again a live semantic model that connects to a SSAS?

Regards,
Naveen

[MarkU]

Hi Naveen.  In client A, yes the reports have a live connection to the model in SSAS.  I don't think it matters for this discussion, but for fuller context, that SSAS model is then connected via Direct Query to SQL.

In client B, it also will be a live connection to the model, with the model this time hosted in Power BI.  However the Power BI model will be in-memory rather than Direct Query back to SQL.

Mark

[NaveenGandhi · Accepted solution]

Please keep backups of all the files.

In client A save the report as PBIP[enable PBIP from preview features in PBI desktop first], Then you can navigate to the below file.

PBIP saved folder -->Report_name.Report-->definition.pbir

Open the definition.pbir using notepad, You will see the SSAS connection string in there.

In client B create a new report using live connection from PBI Model, Use the same method as above to get the connection string for this.

Now try to swap and play around with the connection string from B to A. [Then bring back the file to B]

I have executed something very similar, It might get tricky with the changes, but if done proper , this would work.

Let me know if you have any questions.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[MarkU]

Hi Naveen -- I tried your suggestion today and it works great.  The reports need a little stitching up once brought to client B, but this was expected.  Overall, your flow did exactly what I needed it to do.  Thank you very much!  Regards.

[NaveenGandhi]

Thanks Mark, Glad to be of help 🙂

[MarkU]

Thank you, Naveen.  I will try this on Friday and update, including Accept as Solution, once I've given it a go.  Much obliged!