Hi EDNA,
I am working on a pbix file that has various access level scenarios:
The content is employee recognition data for an HR department. Sorry, file is not sharable. Much internet searching was performed, many dynamic filters attempted, but no success.
There are 3 tables within the model at play:
A company table, used by 3 roles to filter a fact table by companykey.
A Org Roll Up table, which all 4 roles may be subject to filter the fact table by OrgID.
A fact table, containing the necessary keys above for filtering.
Access Requirements:
Specified HR professionals which are manually mapped to 3 different roles:
Role 1 - Corporate HR Professionals - Can see all records (no RLS filters applied on any tables)
Role 2 - Business Unit 1 HR Professionals - Can see all records associated with employees whom belonged to Business Unit 1 at time of award.
Role 3 - Business Unit 2 HR Professionals - Can see all records associated with employees whom belonged to Business Unit 2 at time of award.
This RLS is straight foward. A Business Unit/Company DIM Table has a relationship with my fact table on Companykey. RLS roles filter the DIM table on Companykey values.
New/additional requirement - Leaders shall be able to view recognition occurring within their current ORG ID or below.
I have a roll up table that easily maps a relationship between all managers in the company and all org IDs rolling up to them. This table would have a relationship to my fact table based on ORG ID. UPN = UserprincipleName would effectively filter this roll up table and the rest of the model.
ISSUE/Challenge -
HR Professionals in Role 2 or 3 will be included in this new prospective Role 4 (call it “Leaders”), as HR professionals will be manually mapped to Roles 1, 2, 3 as well as Role 4 (if a leader, such as a VP) via an Active Directory Group.
As such, HR professionals belonging in Roles 1, 2, or 3 but also belonging to Role 4 (if a leader) will be subject to the Leader’s filtering (limited to just recognition within their organization). This fails to meet the requirements of broader access to all recognition within their Business Unit/Company.
How can I configure RLS for the “Leader” role, on the org roll up table to ensure RLS respects the more broader access of HR Professionals when they log in, but more restricted access to a non-HR Leader (such as the VP of Customer Services)?
Limitations -
My employee/org data does not support identifying HR professionals. No flag in the database, no reliable logic. The ONLY data that identifies if an HR professional is logged in is by reading the role they are logged in as, as manually mapped in the PBI service.