Security Cost in Power BI and Analysis Services Tabular

When you define a security role in a Power BI model or in a SQL Server Analysis Services Tabular database, you can apply a filter to every table in the data model. You can write an expression that is evaluated in the row context of the table. Thanks to filter context propagation, if you filter the Product table, you also filter the Sales table if a relationship exists between the two tables.

Usually, we say that the cost of security is applied when the user connects to the database, but this is not entirely true, and there are different behaviors of the engine depending on the size of the table.

Evaluation of Filters in Security Roles

Regardless of the version of Power BI and Analysis Services, the security role filters are not evaluated exactly when the user connects to a database. The security role filters are applied only when the first query reaches the engine: the filters applied to all the tables involved in a query are considered by the storage engine, producing the desired result for the client. Thus, in reality the filters in active security roles are applied to every single storage engine query, producing several side effects. There are two different types of cache used to minimize the impact on the performance, and understanding them might help you in some optimization.

The filters applied to a query are only those associated to tables involved in a query. For example, consider a data model with three tables, Date, Product, and Sales, where Sales has a many-to-one relationship with both Date and Product (using single cross filter direction). In the following DAX query, only Date and Sales are explicitly mentioned in the query:

DEFINE
    MEASURE Sales[Sales Amount] =
        SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
EVALUATE
    ADDCOLUMNS ( VALUES ( 'Date'[Calendar Year] ), "Sales", [Sales Amount] )

If you have security applied to the Product table, it is included in this query, because the rows filtered in Product will filter Sales, too.

However, the same security role would not affect the following query, because it does not reference the Sales table, which is filtered by Product:

EVALUATE
ALL ( 'Date'[Calendar Year] )

The security enforces its filters at the storage engine level. For this reason, a DAX filter function such as ALL ( Product ) will not show products that are hidden by the security filter. For this reason, it would be wrong to simulate security behavior by using an external CALCULATETABLE function.

The implementation of the filters in the xmSQL code can use two different techniques, which depends on the complexity of the filter and on table size:

• Embedded Filter Expression: applies the filter expression to the storage engine query.
• Cached Bitmap Index: creates a bitmap index that filters rows of the table, so that it does not evaluate the security filter expression for every query made by the same user.

Embedded Filter Expression

The xmSQL code sent to the storage engine for the initial DAX query when there is no security applied is the following:

SELECT
  [Date].[Calendar Year], SUM([Sales].[Quantity] * [Sales].[Unit Price]) AS [$Measure0]
FROM [Sales]
  LEFT OUTER JOIN [Date].[OrderDateKey]=[Date].[DateKey];

For example, consider a security role that applies to the Product table the following filter:

=Product[Unit Cost] = 1

The same DAX query will generate the following request to the storage engine:

SELECT
    [Date].[Calendar Year], SUM([Sales].[Quantity] * [Sales].[Unit Price]) AS [$Measure0]
FROM [Sales]
    LEFT OUTER JOIN [Date] ON [Sales].[OrderDateKey]=[Date].[DateKey]
    LEFT OUTER JOIN [Product] ON [Sales].[ProductKey]= [Product].[ProductKey]
WHERE
    [Product].[Unit Cost] = 1;

The presence of a filter in the Product table generates an additional join in the xmSQL, so that the filter applied to Unit Cost will be effective on the Sales table, too, respecting the propagation of the filter context through the relationship Product-Sales.

From a performance point of view, the Embedded Filter Expression does not precompute the security filters. The storage engine cache is the only possible performance improvement for future queries.

Cached Bitmap Index

With the Cached Bitmap Index, there is a two-steps operation: first, the formula engine creates a bitmap index for a table; then, the storage engine query applies a filter to the table using the bitmap index computed before. The first step is not repeated for every query, but is reused for following queries made by the same user.

For example, consider the following filter on Product for a security role:

=Product[Unit Cost] > 1

A first storage query includes the columns required to evaluate the security filter:

SELECT
    [Product].[RowNumber], [Product].[Unit Cost]
FROM [Product];

The formula engine iterates the datacache returned by this xmSQL query, and creates a bitmap index for the product table used in the following storage engine query:

SELECT
    [Date].[Calendar Year], SUM([Sales].[Quantity] * [Sales].[Unit Price]) AS [$Measure0]
FROM [Sales]
LEFT OUTER JOIN [Date] ON [Sales].[OrderDateKey]=[Date].[DateKey]
LEFT OUTER JOIN [Product] ON [Sales].[ProductKey]= [Product].[ProductKey]
WHERE
    [Product].$ROWFILTER IN '0xfff…';

Once the formula engine creates the bitmap index, it will reuse it in following queries, so you will not find the first storage engine query again.

Choice of Filter Technique

The engine automatically chooses one of the two techniques described based on some heuristic that is not documented. Based on observations, and knowing that this behavior can be different in particular conditions and/or in future versions, the logic behind the choice is the following:

• Cached Bitmap Index: the filter condition is complex and the table has less than 131072 rows.
• Embedded Filter Expression: the filter condition is simple or the table has more than 131072 rows.

In the previous examples, you have seen that a comparison using the “greater than” operator is already considered a complex condition, whereas a “equal to” is considered a simple filter. However, it is better to read the generated xmSQL code in order to understand which technique is used for a specific query.

Performance Considerations

Saying that “security filters are evaluated only once when a user connects” is not entirely true. This is in part what happens when the cached bitmap index is in use, but you should consider the performance issue from an overall point of view:

• Cached Bitmap Index executes filter expression only once per row for a session.
• Embedded Filter Expression leverages storage engine cache (if there are no callbacks to the formula engine), but does not persist a list of “valid” rows for the security role.
• Regardless of the technique used to implement security, when you apply a filter to a dimension table, such a table is always included in a JOIN with the fact table.

If you hit a performance issue when you are using security roles, you have to measure it and understand what is the bottleneck for your specific data model. We observed several cases that might require different approaches. We just list some examples here.

• For a large dimension table (having more than 131072 rows), the cached bitmap index is never used. If you have a very complex filter expression in the security role, it will be evaluated for every query. Possible approaches are: moving the security to a smaller table (creating a snowflake schema), reducing complexity of the filter expression.
• For a large fact table, any filter will be an additional filter in the storage engine query. In this case, using the embedded filter expression is an advantage thanks to the storage engine cache, but you have to make sure that the filter is very simple. Make sure there are no calls to CallbackDataID in the resulting storage engine query, because this would stop saving the result in the storage engine query (for example, comparison of a currency column with a constant value always generates a CallbackDataID in SSAS 2012/2014, whereas it works well in SSAS 2016 and following versions, including Power BI).
• Applying filters to many dimensions will always generate multiple joins with the fact table also for very simple queries. Consider carefully the number of dimensions where you apply a security filter.

If you apply security roles to a large model, it is a good idea to test performance before releasing the database in production. The constraints created by security filters can affect the optimization that you carefully made during development.

At the moment of writing, an additional issue is that you cannot use DAX Studio to study the performance of a query when you apply a security role, because tracing features are not available in that condition.