Row Level Security (RLS) for a SSAS Tabular Model

Question

I am new to SSAS technologies for developing analytical models. I have to build several tabular models for a huge application in which security is quite relevant. What I would like to do is to re-use the row level security existing in the sources of the cube and apply it to the cube itself.

For example, if I build a tabular model from two tables of a schema, and these two table have RLS enabled, I would like the cube to take this security into account, so that when I access reports and log in as user, I will only see aggregated data according to the permissions I have.

Searching through the web I found ways of implementing RLS within the cube, but none about inheriting it from the sources. But again, I am new to the technology, so I preferred to ask here. Thanks

Answer 1

The most obvious solution to your request is running SSAS Tabular in thin mode (called DirectQuery mode). As long as in the Existing Connections dialog in Visual Studio you set ImpersonateCurrentUser, when a user queries the SSAS model, SSAS will in turn send one or more SQL queries to the database under the end user's credentials. RLS in the SQL database will come into play here.

One caveat is that I would only recommend DirectQuery in SSAS 2016 not prior. Another caveat is that performance will be slow compared to a cached model in SSAS. So if performance isn't acceptable then turn off DirectQuery and reimplement RLS inside SSAS. Also DirectQuery uses zero caching of results currently so the load against SQL will not be offloaded to SSAS at all. Finally, if you use DirectQuery and ImpersonateCurrentUser you may have to setup Kerberos if your SQL server isn't on the same server as SSAS so that user credentials can double hop.