Use RLS on a Dual Storage model

[Anonymous]

Hi all,

Firstly if this has already been answered elsewhere I'm happy to be redirected.

We have a report that is built to use Direct Query, largely so that all the RLS and dynamic permission can be managed by our Data Warehouse. However, the fact tables being referenced are reasonably large (25M+ rows), the issue we're having is that once we apply our permissions on top of this within the DW performance is being severly effected because the report is sending numerous queries for all the visuals, slicers etc (let alone when an end user tries to actually interact, and if multiple people are using it it really grinds...)

I made the suggestion that we create an aggregated version of the Fact table within the model using Import, and then switch everything necessary to Dual, to help with performance. 

As per: https://docs.microsoft.com/en-us/power-bi/desktop-aggregations

The immediate response was that Importing the table in this way would by-pass the RLS on the DW and so defeat the purpose.

My question is, would it be possible to:

• Create an aggregated version of the Fact table using Import
• Hide the table from the report so end users couldn't access any of it
• Switch the relevant tables to Dual Storage
• Create a relationship between the aggregated table and the Fact table so that when the Fact table is filtered due to RLS on the DW, the agg table is also filtered?

1. Would this approach even work (regardless of performance, I can anticipate potentially not being able to make that relationship active due to ambiguous paths) ?
2. Would it help performance, or would trying to filter the imported table usind the DW RLS via a Direct Query table be just as inefficent?
3. Is there a more optimal approach that I'm just missing at the moment/am I making this way more complicated that it needs to be?

Obviously we're also discussing restructuring our tables/views in the DW, but if there is a way to minimise the performance issues in PBI without having to do so it'd be great to be able to feed that back.

Thanks!

[v-lili6-msft]

hi, @Anonymous 

It is possible for these, As a preview feature, there are some limitations. The Detail table should be in Direct Query mode. Please refer to power-bi/desktop-aggregations#validations.

>>>Detail table must be DirectQuery, not Import. Public preview limitation.

And refer to blog post says:

If you are querying even a DirectQuery table with 250 millions of rows, but you are only querying it by Date, then Power BI act differently. Power BI will not send a query to the data source of the fact table. It will query the aggregated table in the memory instead, and you get a fast response. Power BI only will switch to the table underneath, if aggregated tables cannot answer the question.

http://radacad.com/power-bi-fast-and-furious-with-aggregations

Best Regards,

Lin