How to implement Role Level Security with Role Playing Dimensions in PowerBI

Role Playing dimensions are a common thing in BI scenarios and in the context of PowerBI, Reza Rad (blog|twitter) did an amazing job describing them. So if you want to learn how to implement them in your model, please visit Reza’s article here.

Recently I had to create a model, that had role playing dimensions and on top of it I had to build a dynamic row level security. This model had one fact table, containing rows for each order, that has moved between the sender and receiver store. The task in hand was to implement RLS so that Sales Reps in the company have to be able to access only those rows, where the store, they are responsible for, is on the receiving or sending end. At first it looked a simple task, but unfortunately it wasn’t. So here is how I handled it.

Let’s start with a simple illustrative model – one fact table, where each fact contains a Sender and Receiver StoreID:

Part of the model with Store dimensions and the fact table

The task is to devise a row level security schema, that allows Sales Reps to see results where the StoreID, they are responsible for, is either Sender or Receiver. A sample of the Fact table you can find below:

The fact table with some rows

The first step is to have a mapping table, that has a row for each SalesRep and the StoreID he manages. There might be more than one store that is managed by a single sales rep. Here is my table:

User security mapping table

Note: This is not the best design at all for the security table. There are better ways to design it, but it’s out of the scope for this post

So, I have my model, my security table, how do I define my row level security?

I tried having the security table connected to both dimensions and implement the traditional approach with DAX Filter of the table [UserID] = USERNAME(). But this cannot work, as the connections will create ambiguity. Even if you use inactive relationships and USERELATIONSHIP() to activate them, the filter flow will reach the fact table and enforce AND condition (it will filter the fact from both ends – the sender and receiver dimensions). This means, that if Sales Rep 1 can see only Store 1, then the only orders returned will be those, where Store 1 is both Sender AND Receiver.

The second approach I used was not to relate the table and use IN and OR in the DAX Filter. It looks like this:

Model security in PowerBI

The code itself:
VAR Stores =
CALCULATETABLE (
VALUES ( UserStore[StoreID] ),
UserStore[UserID] = USERNAME() )
RETURN
OR (
‘Fact Table'[StoreSenderID] IN Stores,
‘Fact Table'[StoreReceiverID] IN Stores
)

How it works?:

  • The variable Stores is a virtual table, that contains only the stores, associated with the logged user (utilizing the USERNAME() function)
  • The construct in the RETURN part practically is the same as what you would do in SQL -*** WHERE StoreSenderID IN (Stores) OR StoreReceiverID IN (Stores)

Based on this, the RLS model over role playing dimensions worked as a charm and here is the proof!

Fact table with user with full permissions:

Fact table, viewed by an user with full permissions

The same Fact table for SalesRep1:

Sales Rep 1 data

In this post I briefly described how to create RLS on top of a model with role playing dimensions. The technique can be applied to extend to much more complex scenarios without having to create M:M relationships and comlex logic.

Post Views: 5,065