Error 1 million rows limit only after applying RLS on SQL Endpoint and using fixed identity

[databot_kd]

Error 1 million rows limit only after using a fixed identiy connection for my semantic model.

Even though my selection/filter criteria only has 7000 rows. 

Before applying RLS on my sql endpoint and the report/model worked fine. 

After appllying RLS on my sql end point i get the above error. 

Keeping in mind i have RLS on SQL Endpoint for users who access that directly and i have separate RLS in semantic models as per below. 

1. Semantic Model uses fixed identity (service account) to retrieve data from Fabric SQL endpoint. All entities, to model . Model RLS filters based on user accessing model/report. 

2. security table (*many) links to entity table (one) and entity table (one) links to transaction table (many)

on my RLS table Manage roles (security[email]) = (USERPRINCIPALNAME())

[databot_kd]

Thanks all. we will move off direct lake on sql end point to lakehouse. 

[v-hjannapu]

Hi @databot_kd,

I would also take a moment to thank @blopez11  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support team.

[GilbertQ]

Hi @databot_kd 

As we have said, I would recommend using Directlike because in that way users are always gonna get the latest information, but because it is not using Directquery and not falling back you will then not run into this limitation.

[blopez11]

@ibarrau Is correct that this is a limitation of DirectQuery.  Placing RLS on the SQL endpoint forces your DirectLake on SQL model to fallback to DirectQuery and thus you hit this limitation.  Doing this kind of defeats the performance improvements of DirectLake.  Not sure why you want users to query your lakehouse directly from the SQL endpoint, but you can either have them query your model instead, or if they really need to use SQL, migrate to OneLake security and use a DirectLake on OneLake model.  You implement granular role-based access on OneLake and it is enforced across all Fabric workloads.

[ibarrau · Accepted solution]

Hi. This is a known issue with direct query. You can read more in the answer of this user with the issue referencing to this doc: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery#million-row-limit

https://community.fabric.microsoft.com/t5/Desktop/The-resultset-of-a-query-to-external-data-source-h...

You might want to filter data before requesting for query or visual.

I hope that helps,