Dynamic RLS in DirectQuery Model w/ Many to Many relationship

[awhiteway]

Hello!

Hopefully someone has cracked the code here - I am trying to use dynamic row level security in a DirectQuery model and running into a road block.

The rest of my model is one to many relationships with referential integrity, sending over a nicely folded query. However, my dynamic RLS table has the potential for multiple rows and needs to be a many to many relationship. The assume referential integrity option is grayed out despite my understanding of many to many requiring referential integrity. Not being able to click it is resulting in a nasty query being folded and sent over instead of joining the dynamic RLS object inside of the query. 

Any ideas or is this just a hard stop with PowerBI restrictions on assume referential integrity & many to many relationships?

Thank you!

[Greg_Deckler]

@awhiteway - So there is no way to build a bridge table between your two tables with the many-to-many relationship? Best to avoid many-to-many relationships and use bridge tables if at all possible in my opinion.

[awhiteway]

I had the same thought - but while I could put a bridge table and enable it there- the upstream table not being part of the folded query still causes the performace hit. Essentially it seems a many to many relationship can't exist anywhere in the chain? That makes Dynamic RLS very tough to do

[v-yingjl]

Hi @awhiteway ,

"Essentially it seems a many to many relationship can't exist anywhere in the chain?"

-In the direct query mode, the many-to-many relationship is not impossible to exist, but needs to be avoided, such as using bridge tables, because it can lead to query statements that don't perform well.
In addition, your report performance depends largely on the performance of the underlying data source in direct query mode, so although creating a bridge table may reduce performance, it is a more feasible method

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.