Implementing Row Level Security
Row Level Security can be applied whenever there is a need to restrict certain users of the report to only certain parts of the data. For example: You have multiple regions within your reports and each region has its own Sales Manager. In such a case, you might want to implement row level security in a way that each sales manager is only allowed to view the data for the region he is responsible for. It’s important to note that Row Level Security only applies to the viewer role in Power BI workspace.
Implementing Row Level Security is a two-part process. You need to first create roles within Power BI Desktop and filter the report according to each role. After you have created these roles within Power BI Desktop, then you can go within Power BI service to then assign users to each role.
Configuring Row level Security
You need to perform the following steps to create row level security in Power BI Desktop:
1. Click on the Modeling tab in the ribbon and select Manage Roles.
2. In the Manage Roles window, click Create. Give the role a meaningful name.
3. After adding the necessary filters, click Save to apply the role. .
4. Once you’ve defined the roles, publish the report to the Power BI Service where you can assign users or groups to these roles.
5. In the Power BI Service, you can assign users or security groups to a role by entering their email address or name. However, you cannot add groups created within Power BI itself. Additionally, you can assign external users outside your organization to roles, as long as they have the necessary access permissions.
Configure row-level security group membership
You can use the following groups to create RLS roles
- Mail-enabled Groups: These are email distribution lists that allow members to communicate via a shared email address. Members can send and receive messages as part of the group, and they are commonly used for email-based collaboration.
- Distribution Groups: These groups are designed for sending email notifications to multiple people. They do not provide security permissions and are primarily used to distribute information across a large audience via email.
- Security Groups: Security groups are used to manage access to resources like files, folders, applications, and SharePoint sites. These groups control permissions in Active Directory or Azure Active Directory for security purposes. Office 365 groups are not supported and cannot be added to any role.
Types of Row Level Security
- Static Row-Level Security:
In Static RLS, roles are predefined, and users are assigned to specific roles that filter data consistently for each user in that role. For example, you might create a role called “Sales Region West” that only shows data for the western region.
This type is straightforward and works well when the access permissions do not change frequently or are based on fixed criteria.
- Dynamic Row-Level Security:
Dynamic RLS uses a more flexible approach by assigning permissions based on user identity. Instead of creating specific roles for each region or group, you manage data access through a security table that maps users to specific data they can access.
For example, if a report is used by multiple sales managers, each manager can only see the data for the regions they manage, without needing separate roles for each region.
This method is more scalable and is often implemented using the USERPRINCIPALNAME() function in DAX to filter data based on the logged-in user’s identity.
Improving Model performance
- Removing unnecessary rows and columns:
Optimizing your Power BI model by removing unused or irrelevant rows and columns reduces the size of the data model, leading to faster load times and better query performance. By only keeping essential data, you decrease memory usage and make calculations more efficient. - Performance Analyzer and DAX query view:
The Performance Analyzer and DAX Query View tools help identify slow-performing visuals, measures, and relationships. By analyzing these details, you can detect bottlenecks, optimize DAX expressions, and adjust relationships, improving the overall performance of your Power BI reports and dashboards. - Reducing granularity:
Reducing data granularity (e.g., summarizing daily data to monthly) helps improve performance by decreasing the volume of data processed during calculations. This reduces the load on memory and speeds up the rendering of visuals, making reports more responsive and scalable in larger datasets. - Use Star Schema:
Flatten your data model into a Star schema with facts and dimension tables to improve query efficiency. - Limit the Use of Visuals:
Power BI processes all visuals on a report page when it’s loaded, so reducing the number of visuals on a page can improve performance. - Disable Auto Date/Time:
Disabling the Auto Date/Time option prevents Power BI from creating hidden date tables for each date field, which can consume memory. Instead, create a custom Date table and use it across the model for time intelligence.
