Scenario:
Lets suppose we have a Data Model as follows:
tbl_Org_Hierarchy :
This table represents the Organizational Hierarchy Levels as per below.
The Organization ID (Org_ID) = 1 represents the CEO, is a Manager for the CFO, and COO.
The Organization ID (Org_ID) = 2 represents the CFO, is the Manager for Finance Lead and Account Lead.
The Organization ID (Org_ID) = 4 represents the Finance Lead, who is a Manager for Finance Analyst and Credit Analyst.
The Organization ID (Org_ID) = 5 represents the Account Lead, who is a Manager for Account Analyst.
The other Organization IDs like COO (3), Account Analyst (6), Finance Analyst (7) and Credit Analyst (8) are not playing the Managerial roles.
When a user CEO login to the Report, the CEO (1) should see all data as he is the Top Level in the Organizational Hierarchy.
When a user CFO login to the Report, the user should see the data of CFO (2) and data of the Finance Lead (5) and Account Lead (4), as they are reporting to CFO.
Similarly, when a user Finance Lead (4) login to the Report, the user should see his data and the data of Finance Analyst (7) and Credit Analyst (8) , as they are reporting to Finance Lead.
For the users case, they will see only their respective data as they are not playing Managerial Roles.
Notes:
Org_Path = PATH(tbl_Org_Hierarchy[Org_ID], tbl_Org_Hierarchy[Org_Manager_ID])
Org_Level 1 = LOOKUPVALUE(tbl_Org_Hierarchy[Org_Role], tbl_Org_Hierarchy[Org_ID],
PATHITEM(tbl_Org_Hierarchy[Org_Path],1,1))
Org_Level 2 = LOOKUPVALUE(tbl_Org_Hierarchy[Org_Role], tbl_Org_Hierarchy[Org_ID],
PATHITEM(tbl_Org_Hierarchy[Org_Path],2,1))
Org_Level 3 = LOOKUPVALUE(tbl_Org_Hierarchy[Org_Role], tbl_Org_Hierarchy[Org_ID],
PATHITEM(tbl_Org_Hierarchy[Org_Path],3,1))
Org_Level 4 = LOOKUPVALUE(tbl_Org_Hierarchy[Org_Role], tbl_Org_Hierarchy[Org_ID],
PATHITEM(tbl_Org_Hierarchy[Org_Path],4,1))
tbl_Employee :
This table consists the Employee details.
This table consists the Employee ID, and their Organization Hierarchy ID details.
An Employee might have mapped to Multiple Roles in the Organization Hierarchy Levels.
The Employee 123 is playing a Account Analyst and Account Lead roles in the Organization.
The Employee 125 is playing a Finance Analyst and Credit Analyst roles in the Organization.
The other Employees are mapped to a Single role in the Organizational Hierarchy.
tbl_Sales :
This table consists of the sample Sales data that mapped to the Organizational Hierarchy ID.
[Org_ID] IN
DISTINCT (
SELECTCOLUMNS (
FILTER (
ADDCOLUMNS (
CROSSJOIN (
FILTER (
tbl_Emp_Org,
tbl_Emp_Org[Emp ID]
= LOOKUPVALUE ( tbl_Employee[Emp ID], tbl_Employee[Emp User ID], USERPRINCIPALNAME () )
),
tbl_Org_Hierarchy
),
“Is Path contains Org ID”, PATHCONTAINS (tbl_Org_Hierarchy[Org_Path], tbl_Emp_Org[Org ID] )
),
[Is Path contains Org ID]
),
tbl_Org_Hierarchy[Org_ID]
)
)
—————————————————————-
Now, we will test the RLS for different Employees and see the result as per below.
Case 1: Emp ID =124 ; TPREDDY-PC\Elephant ; Org_ID = 4 ( Finance Lead)
Result:
Since the the Emp ID = 124 is mapped to the Org_ID = 4 , the user is seeing his data and the data of Emp ID = 125, as 4 is the Manager for 7, 8; which are mapped to Emp ID = 125.
Since the the Emp ID = 122 is mapped to the Org_ID = 2 , the user is seeing his data and the data of Emp IDs 123, 124 and 125, as 2 is the Manager for 4,5; 4 is the manager for 7,8; and 5 is the manager for 6.
Based on the above Data Model, the CEO holds the Top Level hierarchy, he can see all the data as per below.
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment