Composite Data Model RLS producing inefficient SQLs

[Anonymous]

Dear Friends,

We are facing an issue and tried searching extensively but didn't find a right solution, hence posting here.

Datasource: Snowflake

Power BI Service

Data Model: Composite Model as shown below.

We have a very complex model but here it's simplified so that it's easy to reproduce, if needed.

Security table has User profile data and RLS related dimension values.

Company and Source are dimension, Fact_AGG tables in import mode and RLS is working fine in this combination.

Fact_DIRECT table is in DIRECT mode (because of large voumes) and user defined aggregation is configured with Fact_AGG_Import table. Without RLS, everything works fine as expected.

As soon as we start using RLS, SQLs that are generated are quite inefficient and mess because of WEAK relationships that exist between Dimension tables and Fact_DIRECT tables.  

For e.g. It's fetching the values from Fact_DIRECT by including WHERE clause with COMPANY_ID in (Companycode1, Companycode2, Companycode3, ....CompanyCode1000)

This still gives right results! However, when there are more dimension values, it's generating additional SQLs where it's passing first 1000 values in SQL1 and next 1000 values in SQL2, then combining result from SQL1 & SQL2.

We have 7 dimensions like this, which is resulting in a very bad SQL in the end.

Does any one every used RLS with COMPOSITE model and User defined aggregation. Any suggestions are really helpful.

-----------------

We also tried changing both dimension tables to DUAL mode but then we are unable to define RLS itself and getting below errors

function CONTAINSROWS is not allowed as a part of row level security on direct query models. We have multiple dimension values in Security configuration, hence we have to use "IN" clause. 

[Daryl-Lynch-Bzy]

Hi @Anonymous - so Direct Query is slow.  And Direct Query with RLS is super slow.  Here are couple of ideas to consider:

1. The RLS rules table in Snowflake, so the table can be added in DUAL mode.
2. Consider adding Composite Key (Company and Source) on the Fact, Fact_Agg and Security table to make RLS filter work on single table.

Out of interest - when you say "DIRECT mode (because of large voumes)" are you not import to avoid using Premium Capacity?

[Anonymous]

Thanks @Daryl-Lynch-Bzy for the quick response. 

1. The RLS rules table in Snowflake, so the table can be added in DUAL mode. Are you referring to dimension tables to be in dual mode? Santosh: Yes, we tried already and I have shared the error in my issue description about CONTAINROWS, please check once. 
2. Consider adding Composite Key (Company and Source) on the Fact, Fact_Agg and Security table to make RLS filter work on single table. Santosh: Unfortunately this is not possible as I mentioned there are 7 different dimensions and with lot of rules: sometimes OR and sometimes AND. Hence, we can't combine all roles rules configuraiton into 1 composite key. 
3. DIRECT query because even with PREMIUM capacity it's not good to import a table size with 85 GB. We only have P1 licence! which is max limit of 25 GB. Again we tried Hybrid tables concept but we can't use "User Defined Aggregation" because Hybrid table has to be in IMPORT mode.

[Daryl-Lynch-Bzy · Accepted solution]

Sorry @Anonymous - I missed the significance of the CONTAINROWS comment.  It sounds like we need to find a way to simplify the RLS rules so they are executed in Snowflake.  Have you seen the guidence:

Understanding Row Access Policies — Snowflake Documentation

I am wondering if you need to consider spliting you Power BI approach to create different data set for different groups.  Each group would effective have a Materialised View in Snowflake to be source of the Power BI data model.  This would simplify the SQL but increase the maintenance of Power BI.  Especially if you want to manage changes across multiple sub-sets.  

Would it be possible to chunk the RLS into groups to reduce the number of dimensions?

An alternative option would be go back to the business and tell them that you need P3 or P4 to support the RLS requirement and here is the annual bill for their business requirement.  Who knows they might say yes.