Forum Discussion
Performance Optimization for Power BI Row Level Security Model with Bidirectional Relationships
- 3 years ago
Anonymous
I think your use of bi-directional to drive RLS should be ok but I would combine the users together in a single table. It looks like it is always a UPN is related to 1 or more Company_ID so you should be able to combine those tables 'Client Manager' and 'Client Company Users' into 1 table.
You would need to add the field [Client Company ID] for the users in the 'Client Company Users' user table but then your master Users table would link on [Client Company ID]. It is faster for the model to traverse a relationship based on shorter field length so joining on [Client Company ID] is a better choice than using [Company Name].
I would also remove the GUIDS [ZAP employee ID] / [Unique User ID] unless you really need them. If they are not being used for anything then they are just taking up space in the model.
Another option would be to filter the company list from a disconnected user table using an filter on the company table like this.VAR _UPN = USERPRINCIPALNAME () RETURN 'Client Company'[Company ID] IN CALCULATETABLE ( DISTINCT ( 'Users'[Client Company ID] ), 'Users'[Email Address] = _UPN )Again, I would do this against a single user table.
jdbuchanan71 Great suggestions. I will test each suggestion. These changes, combined with load testing - as outlined in this Chris Web's post should hopefully cover off our needs.