Unable to change Default semantic model in Warehouse

[Dainian]

I've been adding non-enforced primary and foreign key constraints to the tables I've created, but what I didn't realize is that was also auto-establishing relationships in the default semantic mode; while this is a cool feature, after I added a second foreign key constraint to the DateDim (Start and End from the fact), which started the periodic error messages like "Default dataset rebuild operation encountered user error: There are ambiguous paths between tables..." when making any changes. Sync default semantic model is turned off and I've since dropped all foreign key constraints between the offending tables.

As of now, I have been unable to modify the default semantic model at all for several days; I'll get different errors depending on what I try to do, e.g. uncheck tables in 'Manage default semantic model', delete the relationships that still exist (I can't get rid of them), delete either of the tables, anything.  Each comes with a different error message.

I am, however, still able to create other direct lake models and they work fine. It seems like the default semantic model is stuck in a loop and I'm hoping I can get some guidance on a 'hard reboot' of the default semantic model.

[ToddChitt]

I am having similar issues. In my Default Semantic Model, I added a new table, then set up a One-to-one relationship to dim_date on a DATE type column. I later saw this:

Yes, you see it correctly: TWO ACTIVE RELATIONSHIPS BETWEEN THE SAME TWO TABLES.

Us data modellers have DREAMED of this but Microsoft does NOT allow it. So how did it happen for me?

I got an error (that mentioned a third table) when I tried to delete either relationship. (Table A is related to table B, but those relationships can't be deleted because of something related to table C).

My resolution was to delete the table (not dim_Date) and recreate it. I added it back to the model, then added the relationship again. AGAIN it created a SECOND relationship. This time I was able to delete the one that didn't belong. BUT, I am stuck with NOT beging able to create Measures in the newly added table, and this table does NOT show up as available in Power BI, even though the model is set to automatically add tables from the warehouse.

Any ideas?

[Dainian]

Yes, I've been getting something very similar to what you're describing, depending on what I try to do to resolve it; in the case where I try to delete a related table from the model, the error will reference an unrelated third table. I remember thinking the same thing about seeing multiple relationships between the same table, but wondering why subsequent relationships after the first wouldn't be auto-disabled as per standard semantic model behavior. 

Since the non-default models are working, I guess I'll continue with that for the time being until a solution appears or an update resolves.

[DataBard]

Were the relationships created between the same columns for both relationships? Or were they two relationships, one uni-directional and one bi-directional, on the same columns for both relationships? The former would suggest some form of auto-detection of a relationship, the latter would suggest a bug that I'd start a ticket with Microsoft for.

I haven't been able to recreate that circumstance yet with the data provided, but if it's happening multiple times when you set up relationships, something has to be happening behind the scenes.

[ToddChitt]

The relationships both used the same set of fields from both tables. 

Auto-detect? Maybe, but the fields had different names.

The second time it happend I was able to delete one of the relationships.

The client is on a FREE Trial so I am unable to create a Support Ticket.

[DataBard · Accepted solution]

Hi @Dainian ,

I've encountered similar behaviour when adding constraints. In general the guidance I've received from Microsoft is proceed as you are, creating other semantic models and not using the default model for that reason.

As for how to deal with all the errors currently in the default semantic model, the only answer I've gotten to work is creating a new warehouse without the relationships, migrating data to the new warehouse, and point your semantic models to that in direct lake mode. You might be able to find the needle in the haystack of constraints that have been created and unravel them, but depending on how much content is pointing to the warehouse, it might be faster to just create new and start over.

[Dainian]

Thanks for the reply. I'm getting the vibe that "wait it out" or "start over" are the two choices right now. Since we can continue with other semantic models we will probably do that.

We were primarily an on-prem SQL shop prior to Fabric, so it's taken a lot of trial and error to get used to; because so many analysts were used to SQL, I wanted to provide a T-SQL friendly environment from the jump but I'm not convinced that polaris-on-Delta is quite ready for prime time (it is a little odd this is priced as production-ready product).

I've been warning up to notebooks with a lakehouse, so I may actually migrate to a lakehouse as the silver layer and use a warehouse for the aggregated/gold layer. 

[DataBard]

Yes, that's my sentiment on it as well. To date I haven't heard of much success being had with the default semantic model. Creating other semantic models is your better bet.

Agreed that the warehouse is new, has some odd issues from time-to-time, and it has some growing to do, but on the whole I've found it pretty useful. We just have to keep bringing up our concerns on the forum when they arise, and it'll keep growing in a good direction.

For notebook/lakehouse, I think it's very viable. I've been using the same approach (silver lakehouse, gold warehouse), and it's been pretty successful to date. The notebook should give you some more flexible options performing the same admin tasks across tables.

Hopefully this was helpful! If it was, be sure to mark your answer so others in the community can learn from this discussion!

[v-huijiey-msft]

Hi @Dainian ,

I did some testing and was unable to reproduce your issue, nor did I find a related KNOWN issue.

Given we have so little information at this time, I can't tell what's causing your problem.You can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support:  https://powerbi.microsoft.com/en-us/support/

For how to create a support ticket, please refer to How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you for your understanding.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Dainian]

Thanks for the reply, @v-huijiey-msft, I didn't see the issue in the known issues either. The only other thing I can think of is that we did upgrade the runtime from 1.2 to 1.3 in the middle of development; who knows, but it is something.

I'm curious if in your testing, adding two foreign key constraints resulted in two enabled relationships to the same table (not allowed): so you have a fact table with two foreign date keys and a date dim. Set a primary key constraints on the date dim, then create one foreign key constraint for each date key on the fact - does this create two relationships in your default semantic model, both enabled?

[ToddChitt]

>>You can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.<<

Define "FREE": FREE only if you already have PAID for an F SKU. If you are still on a FREE TRAIL, then you do NOT have a support plan and cannot get help.