How to make sure RLS will propagate always?

ovonemty 56 Reputation points
2023-01-18T12:38:52.1933333+00:00

I have the below model:

User's image

And I am unsure if my RLS propagates correctly…

 

The model has 2 regular facts FactRevenue and FactSales  (I cut them from the screenshot, just for clarity) and it also has Aggregated Revenue (shown in the screenshot).

 

I have two roles, on Employee Current where I have the next RLS:

ROLE1:

=[NT Username] = USERNAME()

 

ROLE2:

=[TeamLeadID] IN SELECTCOLUMNS(FILTER('Employee Current','Employee Current'[NT UserName] = USERNAME()),"ID",[ID])

 

(Most users should see only themselves, and Team leads themselves + their teams they lead).

 

Engagement Role is an UNPIVOT of Employee (each Employee can have up to 5 different roles…) (Therefore it is 1:*)

 

 

My questions are:

  • will my RLS filter in Employe Current flow to Engagement Role and then flow to WBS, and then flow to FactSales ?
  • will my RLS filter in Employe Current flow to AgProfitCenter and then flow to Aggregated Revenue?
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Haris Rashid 1 Reputation point
    2023-02-20T21:18:59.51+00:00

    RLS uses a value for earch record. It does not propagates as such.

    you should be creating one role. And it should use a table where Employees and leads are combined in hierarchical structure. And then use LOOKUPVALUE DAX to dynamically get desired results based on connection.

    e.g. DAX

    =DimEmployeeType[EmployeeTypeKey]=LOOKUPVALUE(DimEmployeeTypeHierarchy[EmployeeTypeID], DimEmployeeTypeHierarchy[UserName], USERNAME(), DimEmployeeTypeHierarchy[EmployeeTypeID], DimEmployeeType[EmployeeTypeKey])

    0 comments No comments