Dynamic RLS support for Analysis Service Tabular Model based on multiple roles for each user
Tested on: SQL Server Analysis Service 2016 Tabular, Azure Analysis Service.
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.
/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular
Coming back to my question, let's assume that you have a two Fact tables namely FactInternetSales and FactResellerSales with a Dimension table named as DimSalesTerritory.
Requirement: A user will have access to FactInternetSales for one territory but FactResellerSales for a different territory.
Using the conventional mode of the RLS setup this requirement won't be possible, so I have a new way to set up the RLS.
Setup: From the SQL Server, to make this setup we have created a Dimension Table called DimUser as below:
Here I have used two Columns one for SalesTerritory Region and one of ReSalesTerritory Region for each of the user to assign the Territory according to the FactTables.
Project Creation: From the Visual Studio we have created Tabular Project and imported the tables as shown below:
Please note the relationship that I have built.
DimUserSecurity has a two relationship with DimSalesTerritory
DimUserSecurity.SalesTerritoryID --> DimSalesTerritory.SalesTerritoryKey
DimUserSecurity.ReSalesTerritoryID --> DimSalesTerritory.SalesTerritoryKey
Here one of the relation is Active and one is Inactive.
Now based on the user I have created a Role named SalesTerritoryUsers, given read permission to the model and added all the member there which are a part of DimUsers table.
Now for the Row Filters I have added DAX filter to each of the Fact Table
=FactInternetSales[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], FactInternetSales[SalesTerritoryKey])
=FactResellerSales[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[ResalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[ReSalesTerritoryID], FactResellerSales[SalesTerritoryKey])
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.
Hope this helps for you as well.
Author: Jaideep Saha Roy –Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft