Forum Discussion
Resolver ITable contains foreign key to multiple tables
I have a curious data model from which I need to create Power BI reports.
In this model there is an Assignments table which contains foreign keys to two other tables, enabling a m:m relationship.
One of those foreign keys is consistent in that it always relates to the Assets table and the key is Asset_UID.
The other key is called Record_UID and it relates to one of several tables, the relevant table being determined by Entity_UID.
I've not seen this sort of data structure before, but then I'm not a Data Scientist so it may actually be fairly comon and even have a name? I didn't create the data model and I have no influence over it, I'm just trying to report on it.
To make sense of things I have used Power Query to create multiple copies of the table, each filtered on Enitiy_UID and then create the relationships in Power BI to the relevant table, dictated by the Enity_UID.
Is this the right thing to to? Or is there a better/simpler approach I could take?
Example: A row in the table could link an Asset to another Asset, an Asset Type, a Work-order, or an Event, with seperate tables for each of these.
Hi Anonymous , Whilst lbendlin's reply was a useful pointer to star schemas, on which I need to do quite a bit more reading, it didn't really answer my question. However CoPilot did; here's what it said:
It sounds like you're dealing with a polymorphic association, which is a design pattern where a single foreign key in one table can reference multiple other tables. This is not uncommon in data modeling, especially when dealing with complex relationships.
Your approach of using Power Query to create multiple copies of the table, each filtered by Entity_UID, and then creating relationships in Power BI is a valid solution. However, there are a few other approaches you might consider:
Union Tables: Instead of creating multiple copies of the table, you could create a union table that combines all the possible related tables (e.g., Assets, Asset Types, Work-orders, Events) into one. This way, you only need one relationship in Power BI. You can add a column to this union table to indicate the type of entity (Asset, Asset Type, etc.).
Bridge Table: Create a bridge table that maps Record_UID to the appropriate table and Entity_UID. This bridge table can then be used to create relationships in Power BI. This approach can help simplify the model and make it more scalable.
Dynamic Relationships: Use DAX to create dynamic relationships based on the Entity_UID. This can be more complex but allows for more flexibility in your reports.
I'm going to mark that as the answer I think.
7 Replies
- lbendlin
Super User
Is it M:M single directional, or bidirectional? How familiar are you with Qlik's associative model?
- IntaBruce
I'm not at all familiar with Qlik's associative model I'm afraid, I just Googled it and it looks like something that is proprietary.
As for the relationships, the Assignments table is what enables a m:m relationship between say Asset and Work-order. A work-order may use zero, one or many Assets and an Asset may be used in zero, one or many Work-orders. I'm not sure about the direction but I have found that Power BI will not allow me to make them all bi-directional.
- lbendlin
Exactly, because then it would be Qlik....
Power BI wants 1:* relationships, single direction. Read about star schemas.
