Dynamic RLS support for Analysis Service Tabular Model based on multiple roles for each user

Hello everyone, I am sure that whenever you wanted to implement Row Level Security(RLS) for Analysis Services Tabular Mode you might be wondering how I will implement RLS when some of my user has multiple roles assigned. Well here is the solution for this issue.

Now before going on to the details of this blog, if Row Level Security in Tabular Mode is still an alien to you, I would recommend you pay a visit to the Microsoft document below which will give you a clear picture to implement Row Level Security.

This DAX filter will get the data for the logged in user, match the user with the DimUser table, pick the SalesTerritoryID or ReSalesTerritoryID from the DimUser table, match it with the FactInternetSales or FactResellerSales and it will get the data specific to the territory that is assigned the user.

Once everything is set I have saved the model and deployed it in my Analysis Service.

Result:

Now to test it, I have browsed the Model from Management Studio with the user Harpreet(xyz/harpsi) who has access to FactInternetSales for Australia region and FactResellerSales for Germany region.

Upon browsing the Fact Table based the SalesTerritory Region, it worked completely fine for me. Please refer the screenshot below.

Additional Requirement: Also let say that you have an additional requirement like mine where you want to give more than one Territory permission for one user in a Fact Table. This can also be done with this above approach. All you have to do is to add the Territory ID with the user details in the DimUserSecurity table. Please refer the screenshot below.

EmployeeID

SalesTerritoryID

ReSalesTerritoryID

FirstName

LastName

UserName

1

1

6

Mani

Jacob

xyz\majac

2

2

7

Kane

Conway

xyz\kaneco

3

9

8

Harpreet

Singh

xyz\harpsi

3

3

NULL

Harpreet

Singh

xyz\harpsi

2

NULL

6

Kane

Conway

xyz\kaneco

Here my user Harpeet has access to FactInternetSales for two Territory 9 and 3 which is Australia and Central whereas he has access to only one Territory for FactResellerSales.

This option is very helpful over some out of box requirement if you have user assigned to different role for different departments.