Forum Discussion
Changing dataset source in composite model without re-importing measures
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
Hi Anonymous ,
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:
- Backup PBIX file
- Open File with “old connection” with Power BI Desktop
- Save it as Power BI Project File (.pbip)
- Open the PBIP file using Tabular Editor 2
- Under [Model] > [Shared Expression] you will find one or multiple connections named such as “DirectQuery in AS – [name of the semantic model]”
- In the [Expression Editor] I changed the name workspace (if needed) as well as the name of the semantic model
- Because I had 3 connections I changed all 3 of them
- Open the PBIP file with the Power BI Desktop
- 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/#comment-709
- https://www.linkedin.com/pulse/composite-models-tabular-editor-daniel-otykier
Cheers,
Dom
4 Replies
Hi Anonymous ,
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:
- Backup PBIX file
- Open File with “old connection” with Power BI Desktop
- Save it as Power BI Project File (.pbip)
- Open the PBIP file using Tabular Editor 2
- Under [Model] > [Shared Expression] you will find one or multiple connections named such as “DirectQuery in AS – [name of the semantic model]”
- In the [Expression Editor] I changed the name workspace (if needed) as well as the name of the semantic model
- Because I had 3 connections I changed all 3 of them
- Open the PBIP file with the Power BI Desktop
- 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/#comment-709
- https://www.linkedin.com/pulse/composite-models-tabular-editor-daniel-otykier
Cheers,
Dom
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)