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

This post has been republished via RSS; it originally appeared at: SQL Server Support articles.

First published on MSDN on Apr 09, 2018
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.

https://docs.microsoft.com/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

One Reply to “Dynamic RLS support for Analysis Service Tabular Model based on multiple roles for each user”

  1. Hi,

    I have a similar problem because I need to have more than one security filter to an user depending one the fact table to analyse.

    I try to implement your idea but with no success.

    I have a Security User Table with two [Sk Perfilagem] attributes:

    – [Sk Perfilagem Fluxo Caixa]
    – [Sk Perfilagem Balanco]

    The first filter rows in Fact Table Fluxo Caixa and the second one filter rows in Fact Table Balanco.

    In the Row filter in my Role I add this DAX queries to each Fact Table:

    =’Fct Fluxo Caixa'[Sk Perfilagem] = LOOKUPVALUE(‘Dim Seguranca Utilizador'[SK Perfilagem Fluxo Caixa], ‘Dim Seguranca Utilizador'[Utilizador AD], USERNAME(), ‘Dim Seguranca Utilizador'[SK Perfilagem Fluxo Caixa], ‘Fct Fluxo

    Caixa'[Sk Perfilagem])

    =’Fct Balanco'[Sk Perfilagem] = LOOKUPVALUE(‘Dim Seguranca Utilizador'[SK Perfilagem Balanco], ‘Dim Seguranca Utilizador'[Utilizador AD], USERNAME(), ‘Dim Seguranca Utilizador'[SK Perfilagem Balanco], ‘Fct Balanco'[Sk Perfilagem])

    I do not know if I am missing anything. Can you please put me in the right direction? Any idea why this is not working?

    Cheers
    Ricardo Castro

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.