CODEWITHSUNDEEP
ssasdaxssas-tabularazure-analysis-services
Neil P
Neil P

Reputation: 3190

How to resolve row level security performance impact

I have a star schema with a fact table and related dimension tables.

I have row level security which is applied to both the fact table and dimension tables. It is important that end users cannot see the values in the dimension tables that they are not allowed to and many measures calculate based on the foreign keys in the fact table, so security is required here to to ensure consistent results.

The performance impact of this is huge as any query querying any single table requires every table in the model to be filtered according to the RLS predicate and joined before returning the result, even for tables that are not included in the query.

Is there any known method for improving the performance or allowing filtering to only be applied to tables included in the query, instead of processing every table/join?

Upvotes: 1

Views: 1094

Answers (1)

iamdave
iamdave

Reputation: 12243

I have not experienced the issues you are facing with RLS, though I have also never tried to apply RLS to a Fact table.

Ideally you would only ever access the data in your Fact tables via selections made within the Dimensions. When your user access the report, they will only be able to see the Dimension records that meet the RLS criteria and then these Dimension records will filter the Fact table over the materialised relationship in the model. Because the RLS is working on a much smaller Dimension table, the RLS filters won't take long and the resulting DAX queries into the Fact table will also perform well.

By applying filters directly to your Fact table, you are requiring the Fact table to be processed each query, rather than utilising the pre-processed and optimised relationship.

I think the only solution to your problem here is to rethink your design so that you don't need to apply RLS to your Fact tables, as this is absolutely a bad idea.

Upvotes: 3

Related Questions