Forum Discussion

ahsan005's avatar
ahsan005
Frequent Visitor
2 years ago

RLS for 3 Tier Hierarchy – Two Way Filtering

Hi All,

I have a 3 Tier Hierarchy (Regional, Area & Territory Manager) for which I’ve successfully implemented RLS by creating a separate RLS _Hierarchy table based on the roles. My lowest granularity is the SAP ID on the basis of which I’ve joined the tables. Data Model screenshot attached and below is the RLS role I’ve created for RLS_Hierarchy table:

IF(
[DIVISION_USERPRINCIPALNAME] = USERPRINCIPALNAME () ||
[AREA_USERPRINCIPALNAME] = USERPRINCIPALNAME () ||
[USERPRINCIPALNAME_MANAGER] = USERPRINCIPALNAME (),
TRUE(),
FALSE()
)

So if a RM logs in, he can see all his areas & territories, Similarly an area manager can see all his territories and territory manager being the lowest in hierarchy can see his territory data only.

 

For the National View (not affected by RLS & visible to all irrespective of who logs in) I’ve created an aggregated fact_sales table so whoever logs in can see all the data since this table is not part of RLS filteration.

 

Now my question is that I want to do the same for the Area Manager & Territory Manager. For e.g. If an area manager logs in, he can see the complete value for the region that he belongs to and not only the regional values that correspond to his area. Similarly a territory manager can see the values for his complete area and not only the area values that reflect his territory. Kind of a two-way filtering thing.

Screenshot ‘RLS_Hierarchy’ attached that shows the rows when territory manager [email protected] logs in and can see his territory data but as you can see area Lahore and region North are getting filtered out on the basis of SAP IDs that only belong to 1101.

 

I know this concept violates the RLS principle but is there a possibility of doing this through changes in the data model? complex DAX as RLS role?

 

AlexisOlson Can you please have a look?

 

Need Help
Tips and Tricks

5 Replies

    • ahsan005's avatar
      ahsan005
      Frequent Visitor
      2 years ago

      This also works in one direction from higher to lower which I've already implemented. I'm looking for a solution that allows me to go two ways. 

       

      lbendlin Ritaf1983 Greg_Deckler seniors, could you please have a look?

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        What's the point of RLS if you are filtering up?  Can you challenge if RLS is actually required for your use case?

         

        Anyway, your rule can be refactored as

         

         

        USERPRINCIPALNAME() IN {
[DIVISION_USERPRINCIPALNAME],
[AREA_USERPRINCIPALNAME],
[USERPRINCIPALNAME_MANAGER]}