Performance issues related to security roles in Analysis Services Tabular #ssas #tabular

I have experienced (on difference customer’s databases) some performance issues related to security in SSAS Tabular models. The symptoms are that certain queries, pivot table, or reports, have good performance when you connect as a database administrator, but end users see bad performance no matter what selection and filter they use.

I wrote an article, Security Cost in Analysis Service Tabular, which explains how security is implemented and the different techniques that could be used, depending on the size (number of rows) of tables involved. It’s a tough start of the year, it is certainly not an “easy read”, but I wanted to write the information that will help me (and many others!) to remember what is going on and to evaluate possible solutions.

In my experience, a common situation is a star schema where you apply security on many dimensions. Such approach will create multiple joins in the storage engine queries generated for any measure, because rows in the fact table have to be filtered according to the security, even if the dimension is not included in the filters of the report. There are no silver bullets for this issue, specific optimizations might vary depending on specific data volume and security rules. However, knowing where is the problem is always the first step towards the solution.