Implementing a Business Intelligence (BI) platform is absolutely critical to any organization, as it offers data visualizations in real time to provide actionable metrics needed to improve decisions making.
In all of these BI processes, Business Intelligence-related security is absolutely key and it’s a critical conversation that is happening in organizations throughout the globe. EPC Group’s Power BI Consulting practice will provide some insights below to assist in your effort.
Power BI is the leading Business Intelligence platform in the marketplace and with the inclusion of Row level Security in Power BI, the restriction of data is applied for users as row level filters. These can be configured for individual users or groups and defined for very specific access to each user who requires this data ensuring they can only view what they have been provided access to see.
If you are looking to apply security filters specific to departments in your organization, the application of Row level security will come in very handy for you.
Also considering the segregation of data spread across the location wise filter could use the RLS concept to restrict data to only few users. Let’s go into some additional details regarding the steps involved in the sections below.
Managing Roles and Creating rules for Row level security in Power BI Desktop?
When we talk about the application of security, we need to set some rules within the Desktop application itself. Before publishing any report in Power BI, it is recommended to create and modify these role definitions. In order to achieve that, follow the procedure below:
Step 1: Get data into the Power BI Desktop section or setup the DirectQuery connection to your SQL Server.
Step 2: Go to the modelling ribbon > Go to Manage Roles
Step 3: A dialog box opens up – Now create a new role to be defined as a row level security element. Now provide a name for the role.
Step 4: Select the TABLE to be applied the security filter – This table data is the information you imported into Power BI Desktop using Get Data option.
Step 5: Now input the DAX language expression that counts for the rule to be created. For example: [Table ID] = “Value”. For Department specific application, this could be: [Department] = “Human Resources” and likewise.
Step 6: After the expression is created, check the expression box to validate the expression.
Step 7: Save the rule and publish the report to achieve the security model applied
There is a concept of Dynamic security within Power BI Desktop wherein specific users can be assigned to a role. This involves the usage of username () which has the format of DOMAIN\username in Power BI Desktop and userprincipalname() which has the format of [email protected] in Power BI Report Server and Service.
Checking the Row level security in Power BI Desktop
After performing the first step of creating roles, a user can test the results by performing the following steps:
Step 1: In the modelling ribbon > Go to View As Roles. In this, you will see the roles you have created.
Step 2: There will be number of roles that one can create for their reports. Select the role created and now click on OK to apply that role. After applying, the data relevant for that role is presented and displayed.
Step 3: If the security is applied based on individual user, select the option ‘Other user’. Enter the email address in form of: [email protected]
Step 4: Once you select OK, it will display the report page which will be visible to that user’s specific geographical RLS or Departmental based RLS applied. It is important to note that in Power BI Desktop – when selecting ‘Other user’ option, it will display different results based on the applied Dynamic security as per Microsoft’s practice.
Important Things to remember for Row level security on Cloud models
There are some important considerations to be taken care of to ensure this is performed in a best practices manner. EPC Group’s Senior Power BI consulting team has summarized them below:
1. A rule which is already created in Power BI Service must be re-created again for it to work on Desktop versions
2. Row level security rules can be created only on the dataset files uploaded on Power BI Desktop.
3. The feature of Cortana support is currently not available with Row level security
4. For the on-premise service models, live connections can be handled for RLS implementation. On the other hand, ETL and DirectQuery connections will be available for Power BI Desktop security model.
Still curious about how Power BI? If you wan’t to learn more about how it can be implemented into your business, contact our experts.
