Forum Discussion
Onelake security with large tables
Thank you for the response,
What is so confusing for me is that I could make it work with a DirectLake on SQL model, just in a very inconvenint way:
1. Give user read on the lakehouse
2. Give user read role in Onelake security
3. This is the confusing one, I had to explicitly add an RLS rule on all tables I wanted the user to see.
With no RLS rules added, I didn't see everything as one would expect, I saw nothing. My only guess to why this worked is that when RLS applied, the query got small enough to not having to fallback to DirectQuery and thus not going through the SQL endpoint, but this is just my guess, I have no clue tbh. This is however not a feasible solution so I moved on.
Anyways, it turned out we had not used any V-Ordering on the table, so I turned V-Order on and ran optimize + z-order and i shrunk the table by almost half, so now DirectLake on OneLake is working again.
Super UserHello FabricJacob
There's a small nuance to this if you're using a semantic model to access data - Microsoft documentation says that
Thank you for the response,
What is so confusing for me is that I could make it work with a DirectLake on SQL model, just in a very inconvenint way:
1. Give user read on the lakehouse
2. Give user read role in Onelake security
3. This is the confusing one, I had to explicitly add an RLS rule on all tables I wanted the user to see.
With no RLS rules added, I didn't see everything as one would expect, I saw nothing. My only guess to why this worked is that when RLS applied, the query got small enough to not having to fallback to DirectQuery and thus not going through the SQL endpoint, but this is just my guess, I have no clue tbh. This is however not a feasible solution so I moved on.
Anyways, it turned out we had not used any V-Ordering on the table, so I turned V-Order on and ran optimize + z-order and i shrunk the table by almost half, so now DirectLake on OneLake is working again.