Scenario:
Lets suppose we have a Data Model as follows:
The tables and sample data is 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.
Based on the above Organizational Hierarchy, the RLS should implement and work as per the below:
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.
tbl_Emp_Org :
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.
In the above table, the Employee 121 is playing the CEO and COO roles in the Organization.
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.
----------------------------------------------------------------
Now lets Implement the Dynamic Row Level Security(RLS), on the table tbl_Org_Hierarchy , Column [Org_ID] using the below DAX Expression, as per below:
[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.
Result:
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.
Case 3: Emp ID =121 ; TPREDDY-PC\MorganFreeman ; Org_ID = 1 (CEO)
Result:
Based on the above Data Model, the CEO holds the Top Level hierarchy, he can see all the data as per below.
Notes:
if we have a single Employee table with details like [Emp ID], [Email], [Manager ID] then we can create the Hierarchy as [Hierarchy_Path] using PATH([Emp ID], [Manager ID]) function.
Next we can use any one of the following logic for RLS definition.
Logic1: If Employee[Hierarchy_Path] based on [Email].
PATHCONTAINS(Employee[Hierarchy_Path], USERPRINCIPALNAME())
Logic2: If Employee[Hierarchy_Path] based on [Emp ID]
PATHCONTAINS ( Employee[Hierarchy_Path],
MAXX(
FILTER(Employee,
[Email] = USERPRINCIPALNAME()
)
),
Employee[Emp ID]
)
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.