Changing dataset source in composite model without re-importing measures

[aJayMitchell]

Hi all,

I have a composite model which links to two datasets and I want to move both the datasets and also this composite model onto a different tenant/workspace.

I thought this would be simple by publishing the datasets to the new location and then, within the composite model, going to 'Data source settings', clicking 'Change source' and pointing it to the dataset in the new location. However, when doing that, it re-imports my measures table as a new table ('_Measures 2' where the original was '_Measures') and this results in all of the visuals breaking.

Is there a way through something like Tabular Editor where I can simply repoint the composite model to the correct server/workspace path instead?

Image below shows asking to re-select tables after changing source:

Dialog asking to select tables

Images below show before and after changing source, and how the measures are being re-created in a new table (other than measures created directly in the composite model):

Measures before changing sourceMeasures after changing source

 

[DomPower · Accepted solution]

Hi @aJayMitchell ,

I faced a similar situation as you did. Meaning, that when I changed to Direct Query connection using the Power BI Desktop I was forced to re-import all the tables and measures. Unfortunately, that caused the same result as you described above. However, in my case the tenant and workspace remained the same BUT the name semantic model had changed. Thus, I found a solution for me that might work for you as well.

At this point, I wanted to thank Morten Lønskov from Tabular Editor team for helping out. 😃 Much appreciated.

Thus, that’s what I did:

1. Backup PBIX file
2. Open File with “old connection” with Power BI Desktop
3. Save it as Power BI Project File (.pbip)
4. Open the PBIP file using Tabular Editor 2
5. Under [Model] > [Shared Expression] you will find one or multiple connections named such as “DirectQuery in AS – [name of the semantic model]”
6. In the [Expression Editor] I changed the name workspace (if needed) as well as the name of the semantic model
7. Because I had 3 connections I changed all 3 of them
8. Open the PBIP file with the Power BI Desktop
9. Save file as PBIX and upload it to the Power BI Service

That even worked out without the feature [Allow unsupported Power BI features (experimental)] turned on.

However, I was not able to change the shared expression within the PBIX file directly using Tabular Editor (via connect to local instances feature). Thus, I guess you have to do it via the Power BI Project File. Furthermore, I haven’t test it using a Power BI template file (PBIT) but it might work as well.

Additional information can be found here:

• https://blog.tabulareditor.com/2023/08/18/composite-models-in-power-bi-and-fabric/comment-page-1/#co...
• https://www.linkedin.com/pulse/composite-models-tabular-editor-daniel-otykier

Cheers,

Dom

[aJayMitchell]

Thanks Dom - it's funny timing, as I'm literally going to be adjusting another composite model in the next day or two which may well require this!

[DomPower]

One thing more Morten was pointing out:

"Do be aware that there is no update schema funtionality for composite models so you would have to update partitions and table changes yourself. Finally, you may have to update the credentials in the service for the connection."

In my case the composite model was pretty small. Thus, the full refresh was done very fast. Furthermore, I did not to update the credtionals because I'm using shared cloud connection (https://learn.microsoft.com/en-us/power-bi/connect-data/service-create-share-cloud-data-sources) 

[aJayMitchell]

Used this approach today and it worked perfectly. The only step to add is to ensure you have 'Show All Object Types' selected in the 'View' options of Tabular Editor so that you can see the Shared Expressions.

Your timing of posting this couldn't have been better!