Building relationship in Fabric Datawarehouse failing and ends up with an error message - something

[AbhishekRathor]

While creating data model in Fabric datawarehouse, I ends up with following error.. Its not allowing me anymore to establish any relationship further with Facts and Dims. Its a bug in the environment and not letting me move ahead.

Does anyone know about this problem and have any solution to fix it, would be highly appriciated.

[ToddChitt]

I'm getting a similar error when trying to make basic adjustments to my Semantic Model connected to a Warehouse. All I'm trying to do is set SUMMARIZE BY to NONE, or specify a Display Folder or mark a column as HIDDEN.

IMHO, Fabric still has a long way to go before it is really solid.

[AbhishekRathor]

thanks for looking at this issue. Am trying to build a relationship between fact and dim tables, some worked. but after a certain point, the Fabric Datawarehouse environment didn't allow the addition of any new relationships. Every time it gives the same error. 

My dataset is a bit heavy with some facts keeping millions of records. Dealing with large table data seems the cause of the issue though MS is in their product showcase talking about billions of records in the dataset. 

Without establishing the right relation, I can't consume data at the PowerBI reporting layer. Not sure if anyone else has encountered a similar issue. 

I am stuck!

[AndyDDC]

You can check the relationships in the Warehouse by querying the system tables. Run the query below on your Warehouse using a SQL query and see what relationships you have.  FYI you can create relationships either in the model view or using SQL (Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn).  You may find you need to remove relationships using SQL and then re-add them.

SELECT

    fk.name 'FK Name',

    tp.name 'Parent table',

    cp.name, cp.column_id,

    tr.name 'Refrenced table',

    cr.name, cr.column_id

FROM

    sys.foreign_keys fk

INNER JOIN

    sys.tables tp ON fk.parent_object_id = tp.object_id

INNER JOIN

    sys.tables tr ON fk.referenced_object_id = tr.object_id

INNER JOIN

    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id

INNER JOIN

    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id

INNER JOIN

    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id

ORDER BY

    tp.name, cp.column_id

[AbhishekRathor]

Thanks Andy, This looks great and flexible. I just checked the query on one of the facts in my model, and viewed this as well from PBIX file that is connected with a live connection to the Fabric DWH. The query is surprisingly showing me two FKs, though the model view is showing more relations -

Why am I missing the other two relations?

[AndyDDC · Accepted solution]

It could be that when the model relationships were created, there was an error registering those relationships in the physical model and hence not visible in the system tables. 

What I would do to "clean up and start fresh" is:

• Delete the relationships from the model view from the Fact table (so the Fact has no relationships in the model view anymore)
• Run the SQL system query again and see if you still see any FKs for that particular Fact table
• If you do, then delete the FK using SQL
  • ALTER TABLE <table_name> DROP CONSTRAINT <FK_Name>;
• Go back to the model view and create the relationships again and see if they now appear in the system tables

[AbhishekRathor]

Hi Andy, following your guidance, have dropped all the FK constraints and recreated them through SQL. It seems working, could see Relations in the visual model. But there is one issue, all the relations are appearing as Inactive, have to make them Active one by one manually - bit inefficient! Is there a waymaking them Active through the SQL statement?

[AndyDDC]

AFAIK no, the relationships will appear as inactive until you activate them in the model view. Theory is you may wish to create FKs in the warehouse but not actually activate them in the model. Eg scenarios where you have multiple FK from one table to another (PBI only supports 1 active relationship)

[AbhishekRathor]

This sounds very logical. Earlier when I had the same issue, I dropped all the objects; created a fresh one, and built the relationships. I'll do it again but this time I will keep an eye on what's going on in the sys tables just to ensure it aligns with my model design. Thank you so much, though it is a bug we have to accept it. 

[vanessafvg]

I dont think having billions of records is an issue, although that does require your model to be well optimised.

The issue could be how you are modelling this data and by the looks of it, that is the problem having so many joins in your model will definitely create an issue, it looks like a 3rd normal form model you are using, but for that many records, and tables you really need to denormalise some of your tables and use a star schema approach.

With the limited information you have given me that is the best advice I can give.  The answer is mostly always in the model.

[AbhishekRathor]

Thanks! In fact, the model is fully based on the Star Schema. I have approximately 20 Facts and 6 Dims,  and I do not have any inactive/multiple Joins. That is of surprise to me, shouldn't see any issue in design causing the failure. It's a bug in Product IMO in handling large models. Thanks anyway for your advice!

[vanessafvg]

are you able to create screenshots of what you trying to do?  have you duplicated a column?

[vanessafvg]

ah, i misssed the dim_ tables

you know the error message you get  and under that, out of curiosity, when it says see details, what does that provide when you click the see details arrow down?