Edit tables of semantic model does not add tables

[MarthaLies]

Hello,

I have a Lakehouse containing the data for facts and dimensions. In order to create semantic models for Power BI, we decided to create a Datawarehouse. Within this Datawarehouse, we have developed SQL views to incorporate role-playing dimensions, additional business logic, and to translate table and column names into French or Dutch.

The initial semantic model was created with a few tables. Now, we would like to add the other tables, which are represented by SQL views. However, when we select them in the "Edit semantic model" window, it takes several minutes to load, but nothing is added to the model.

We are working within a Workspace with a Trial capacity and the user also has a Trial license. Any idea what might be causing this issue?

Thanks in advance!

Martha

[MarthaLies · Accepted solution]

Hello,

I don’t think is temporally, it has been some days that we are struggling with this issue.

I’m fully working within the Fabric web portal.  A have a Lakehouse containing all the data, the views are basically used to de-normalize some data and to add business translations. Both Lakehouse and Datawarehouse are in the same Workspace (with Trial Fabric capacity).

The list of available tables/views is well refreshed, I can select the view, then clicking Confirm.  It runs for some seconds and the windows is closed.  Looking at the model, the tables are not added.  If I try several times, sporadically one of the tables is added, but not all times.

Regarding the choice of a Datawarehouse, we chose that option to be fully compatible with all CRUD operations via T-SQL.

We are using an automation tool that generates T-SQL procedures as part of our ELT process, to Create, alter the tables.  It also generates T-SQL to Update, Insert and Delete the data.

In my example, I only have views in the DWH. I know that using views will use DirectQuery. If performance is not good, I understood we will need to materialize the views logic into tables.

Thanks,

Martha

[MarthaLies · Accepted solution]

Hello,

I don’t think is temporally, it has been some days that we are struggling with this issue.

I’m fully working within the Fabric web portal.  A have a Lakehouse containing all the data, the views are basically used to de-normalize some data and to add business translations. Both Lakehouse and Datawarehouse are in the same Workspace (with Trial Fabric capacity).

The list of available tables/views is well refreshed, I can select the view, then clicking Confirm.  It runs for some seconds and the windows is closed.  Looking at the model, the tables are not added.  If I try several times, sporadically one of the tables is added, but not all times.

Regarding the choice of a Datawarehouse, we chose that option to be fully compatible with all CRUD operations via T-SQL.

We are using an automation tool that generates T-SQL procedures as part of our ELT process, to Create, alter the tables.  It also generates T-SQL to Update, Insert and Delete the data.

In my example, I only have views in the DWH. I know that using views will use DirectQuery. If performance is not good, I understood we will need to materialize the views logic into tables.

Thanks,

Martha

[frithjof_v · Accepted solution]

Here is some more info regarding XMLA endpoint for Direct Lake Semantic Models:
Learn about Direct Lake in Power BI and Microsoft Fabric - Microsoft Fabric | Microsoft Learn

Announcing XMLA Write support for Direct Lake datasets | Microsoft Fabric Blog | Microsoft Fabric

Editing your Direct Lake Datasets from Tabular Editor? Yes please! (youtube.com)

The YouTube video shows where to find the XMLA endpoint connection string:

Are you using the default semantic model, or have you made a custom semantic model? I think creating a custom semantic model is preferred:
Power BI Default Semantic Model or Custom; A Guide for Using in Fabric Environment - RADACAD

I think this is also true (or even required) if you want to use XMLA endpoint.

According to this blog: Controlling Direct Lake Fallback Behavior (fabric.guru)

"After updating the model using Tabular Editor, you will not be able to use the web modeling experience. You cannot use this method for an auto-generated default semantic model as it doesn't have XMLA endpoint."

I see. Yes, when wanting to work with T-SQL, stored procedures, etc. for all CRUD operations, I think it makes sense to use the Warehouse. 

To me, the issue you are facing seems to be a bug with the Fabric user interface.
Hopefully, the XMLA endpoint can be used as a workaround for adding/removing tables from the semantic model.
However, the preferred solution is that the bug in the user interface should be fixed.

[MarthaLies]

I'm using a custom semantic model, not the default one.
I'll investigate more about editing via XMAL or tabular editor.  Thanks for links.

[v-shex-msft]

HI @MarthaLies,

Did these edit operaritons from external tools at the XMLA endpoint works for your model?

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @MarthaLies,

Did this semantic model from direct Lake? Can you please share some more detail information about this issue? 

AFAIK, currently power bi existed known issue when using web editor to operate with the semantic model from direct lake.

For a workaround, you can use external tools or XMLA to perform a schema sync to change tables or add new columns from the data source.

Regards,

Xiaoxin Sheng

[MarthaLies]

Hello Xiaoxin,
We have hundred of tables and columns to add to our semantic models.

Is there any way to generate the XMLA code to add the tables and its columns from a Datawarehouse table/view?

Thanks,

Martha

[frithjof_v]

I think it sounds like a bug/issue with your data warehouse (semantic model). Hopefully just a temporary issue. I think I have experienced something similar myself before. I don't remember now if it was a temporary or permanent issue.

Did you try clicking the refresh button first (on the right hand side of the Search box), before selecting the tables and then clicking confirm? You could try that and see if it helps.

On a side note, I'm curious about the reason why you use data warehouse instead of lakehouse? I'm trying to understand the difference between these two options myself. So far, I've found that using only Lakehouses seems like a good option (also if using a bronze-silver-gold structure).

Remember: if you aim to use direct lake, then the views will not work with direct lake. They will fall back to DirectQuery. If you want to achieve direct lake, you will need to create tables instead of views.