Forum Discussion
Build Model according to one entity being logged in (RLS), not many entities
I'm seeing poor performance because of loads of "many to many" relationships.
There's a table where the id is repeated for every possible combination of logged in entity, but each logged in entity would only ever see a set of unique ids from this.
e.g.
| entity_id | other_id |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
The report would only ever be used by one logged in entity, so all of these relationships stemming from this table should be one to one, or one to many. Many users / entities could never log on.
How can I model this properly so that it's optimised for a single user?
Nb the DWH is snowflake so the tables are embedded - hence every combination is in there.
2 Replies
jrnhbc I'm not aware of a way to use RLS to alter the cardinality of relationships - Fabric might have something do this but not sure.
From a Power BI point of view, what you could do is add a column to the table to act as the primary key, where you combine the entity_id and other_id into one unique key. That way you can make the relationships 1 to many as you would expect when just one entity_id is logged in.
Great idea - sadly can't use a composite key here as the subsequent tables in the relationship would only have other_id and not entity_id (which is why they're all demanding many to many relationships despite the fact in the published, embedded app this could never happen)
