Row-Level Security

Overview

• Row-Level Security (RLS) has been configured to be enabled on all the models from a Power BI prespective
• For RLS to work it also needs to be configured correctly inside the app. Creating Business Units (BU) and assigning users/projects/programs/portfolios to the correct BU.
• While deploying the models, its best to assign users with workspace admin rights to bypass the preconfigured RLS setup, so the users can access the reports.
• Once the models are ready for production these users will need to be removed and access will be given by the AAD group.

Row-Level Security (RLS) in Power BI

• Row-Level Security (RLS) in Power BI facilitates the selective visibility of data rows in a dataset. It ensures users access only the data pertinent to their role or identity within an organization.
• For instance, consider an organization with sales data segregated by regions. The goal might be to allow regional managers to view only the data relevant to their specific region.
• Implementation Process:
  1. Role Definition: Begin by defining roles within Power BI. For instance, roles might include "North Region" and "South Region". A specified formula or rule is associated with each role to determine its data access. An example rule for the "North Region" might restrict visibility to rows where the "Region" column is labeled "North".
  2. User-Role Association: After defining roles, associate them with individual users. As an example, if John manages the North Region, he would be linked to the "North Region" role.
  3. Data Access: Upon accessing a Power BI report, a user's assigned role dictates the data they view. Using the prior example, John would only see data pertinent to the North Region.
  4. Adaptable Rule Creation: Power BI allows the development of dynamic rules. A single rule could, for example, evaluate a user's profile for regional association, subsequently displaying only the data relevant to that region.

Utilizing PATHCONTAINS in Power BI's Row-Level Security (RLS):

Understanding PATH and PATHCONTAINS:

• Within Power BI, hierarchical relationships can be represented by connecting entities in parent-child structures. The PATH function creates a string representation of these relationships, resulting in a lineage identifier. As an illustration:

  CEO (ID 1)
  |- Manager (ID 2)
  |- Employee (ID 3)
  

• This hierarchy, when processed by PATH, yields the lineage 1/2/3 for the Employee.

• The PATHCONTAINS function evaluates whether a specified ID is present in a given path, returning a boolean value (TRUE or FALSE).

Application in RLS:

• Consider a scenario where a Manager, represented by ID 2, should access data only related to their immediate subordinates and their own data. Using PATHCONTAINS, a rule can be instituted to ensure this. When the Manager accesses the data, the rule scrutinizes the lineage of each data row to ascertain the presence of the Manager's ID. If identified, the row becomes accessible.

Expressed in DAX:

PATHCONTAINS(PATH([ID_Column], [ParentID_Column]), 2)

Rows satisfying the condition set by this formula are rendered visible to the Manager.

Significance:

• The strength of PATHCONTAINS lies in its capability to dynamically filter data rooted in hierarchical distinctions. It's invaluable for organizations with intricate organizational structures, ensuring data accessibility aligns with an individual's hierarchical placement.

• Simply put, it functions like filtering a family tree, permitting data visibility based on one's position in that tree.

Leveraging PATHCONTAINS for Business Units in Power BI's RLS:

Introduction to Business Units and Hierarchies:

Organizations frequently organize their functionalities into hierarchical Business Units (BUs). Consider this hierarchy for "Global Corp":

Global Corp
|- North America Division
   |- US Branch
   |- Canada Branch
|- Europe Division
   |- UK Branch
   |- Germany Branch

Application of PATHCONTAINS to Business Units:

Given a dataset mirroring this hierarchical BU structure, PATH and PATHCONTAINS can be employed to determine data access for users based on their BU positioning.

Implementation Steps:

Data Structure: Represent the hierarchy in a table:

BU_ID	BU_Name	Parent_BU_ID
1	Global Corp	NULL
2	North America Division	1
3	US Branch	2
...	...	...

Lineage Creation: The PATH function delineates a lineage for each BU:

BU_ID	BU_Name	Parent_BU_ID	Path
1	Global Corp	NULL	1
2	North America Division	1	1/2
...	...	...	...

RLS Implementation: For a user associated with the "North America Division" (BU_ID 2), the RLS rule using PATHCONTAINS will validate each data row's lineage for the presence of the identifier "2". Rows with this identifier are made accessible.

Expressed in DAX:

PATHCONTAINS([Path], "2")

Here, the manager aligned with the "North America Division" can view data rows with the lineage "1/2", "1/2/3", and "1/2/4".

Conclusion:

Using PATHCONTAINS for Business Units streamlines data accessibility based on hierarchical positioning within the organization, ensuring users access only pertinent data.

Please see below image for a general understanding of RLS.

Troubleshooting Row-Level Security (RLS) (Portfolio Model, Resource Model, Strategy Model)

RLS_General (Role)

By Default, RLS is configured to use the internal email address on the Dim_System_Users table within the Portfolio, Resources, Strategy models and as such, some clients will use a different single sign on address. To change this you will need to load the model in Power BI Desktop and follow the following steps:

1. From the top ribbon, navigate to Modelling.

2. Click Manage Roles.

3. Select the role: RLS_General.

4. Scroll to the Dim_System_Users table and select it. You should see a DAX formula in there that is the following:

   VAR CURRENTLOGIN = USERPRINCIPALNAME ()
   VAR FilterUser = FILTER(Dim_System_Users,Dim_System_Users[internalemailaddress]= CURRENTLOGIN)
   VAR CurrentBU= MAXX(FilterUser, Dim_System_Users[BusinessunitIndex])
   RETURN
   PATHCONTAINS(Dim_System_Users[Path], CurrentBU)
   

5. Update the column in the second line [internalemailaddress] to the desired column with the correct userprinciple name format for the client. This will match the userprinciple name of the person logging into the report against the row within the new column you define to make RLS work.

6. Save the model and publish to the service.

7. From the service navigate to the Portfolio Model, Resource, Strategy datasets > security settings and populate the security groups that will be associated with the RLS_General Role.

Troubleshooting Row-Level Security (RLS) (Work Model)

RLS_Work (Role)

By Default, RLS is configured to use the internal email address on the Dim_Resources table within the Work Model and as such, some clients will use a different single sign on address. To change this you will need to load the model in Power BI Desktop and follow the folling steps:

1. From the top ribbon, navigate to Modelling

2. Click Manage Roles

3. Select the role: RLS_Work

4. Scroll to the Dim_Resources table and select it. You should see a DAX formula in there that is the following:

   [internalemailaddress] = USERPRINCIPALNAME ()
   

5. Update the column in the first line [internalemailaddress] to the desired column with the correct userprinciple name format for the client. This will match the userprinciple name of the person logging into the report against the row within the new column you define to make RLS work.

6. Save the model and publish to the service.

7. From the service navigate to the Work Model dataset > security settings and populate the security groups that will be associated with the RLS_Work Role.