What is Object Level Security?
Object Level Security (OLS) enables model authors to secure specific tables or columns from report viewers. For example, a column that includes personal data can be restricted so that only certain viewers can see and interact with it. This added layer of security prevents users without the appropriate access levels from discovering business critical or sensitive personal information like employee or financial records. For viewers that don’t have the required permission, it's as if the secured tables or columns don't exist.
Can you give an example?
Let’s take the example of a Sales report in Power BI. Suppose you have 2 different groups of users for this report: one group is allowed to see “Margin” data, while the other group is not allowed to see “Margin” data.
Some of our terms to keep in mind when reading further:
- A column that can’t be viewed by all users is called a “protected” column (so the column “Margin” is a “protected” column in our example).
- A role that contains users who can’t see all data is called a “limited” role.
Picture 1 - Overview
What is the standard setup for OLS?
The standard setup for OLS can be found here.
Summary:
- Create different roles (e.g. 1 role “MarginAllowed” and 1 role “MarginNotAllowed”).
- Specify for the “limited” role which columns should not be visible for the users within that role (e.g. for the role “MarginNotAllowed” you specify that the column “Margin” should not be visible).
Pros:
- This setup is easy to implement and maintain.
- If a user within the “limited” role connects to the dataset in Power BI Desktop, he will not see any of the “protected” columns, so he will not be able to create visuals or measures using these columns.
Cons:
- If a visual in the report uses a “protected” column, the visual will crash for users within the “limited” role (see image below). This is annoying because the visual can still contain other relevant data (e.g. the “Sales” data is still very relevant for the users in the role “MarginNotAllowed”).
Picture 2 - Crashing Visual
How to avoid crashing visuals?
At this moment, there is no quick fix available to overcome the disadvantage of crashing visuals, but there are a few workarounds possible. These options are described below, ranked from highest to lowest recommendation.
Option 1: Using Field Parameters
In general, Power BI Field Parameters allow you to dynamically change the measures or dimensions that are used in a visual. But you can also use this feature to set up OLS! This setup is explained in the following blog post from Chris Webb: Stopping Some Users Seeing Certain Columns Or Measures In Your Power BI Report With Object Level Security And Field Parameters.
Please be aware that Field Parameters are still a preview feature. You can activate it via Options and settings > Preview features:
Picture 3 - Feature Field Parameters
Summary
- Create a Field Parameter, including the “protected” columns (or measures based on these columns).
- Create different roles (e.g. 1 role “MarginAllowed” and 1 role “MarginNotAllowed”).
- Specify for the “limited” role which selection of fields in the Field Parameter can be viewed. Alternatively, you can also specify which fields in the Field Parameter can’t be viewed (see image below). The latter is our recommendation as this is future-proof: if a new column is added to the Field Parameter that can be viewed by all users, you don’t need to update your roles.
Picture 4 - Filter Field Parameters
Pros:
- This setup avoids the issue of crashing visuals (see image below).
- This setup doesn’t require too much extra work and is easy to maintain.
- You only have 1 report, so there is no need to copy and paste general changes to other reports (difference with Option 2 described below).
Picture 5 - View Field Parameters
Cons:
- This setup only works for report consumers. If you are a report builder/editor and you are within the “limited” role, you will still have access to the full data model in Power BI Desktop. So you will be able to see the “protected” columns (or measures based on these columns) in the data model and create visuals using these columns (or measures).
Option 2: Using multiple reports and Power BI Audiences
In general, Power BI Audiences allow you to create multiple audience groups for the same App and assign different permissions to each group. But you can also use this feature to set up OLS! The general setup with Power BI Audiences is explained here.
Summary:
- Create different reports (e.g. 1 report with “Margin” data and 1 report without “Margin” data).
- Publish these reports to the same workspace.
- Register an App in this workspace and add content artifacts (reports/dashboards) to this App.
- Create different Audiences using AD (e.g. 1 Audience “MarginAllowed” and 1 Audience “MarginNotAllowed”).
- Specify for each Audience which artifact should be hidden (e.g. hide the “Margin” report for the Audience that is not allowed to see “Margin”).
Pros:
- This setup avoids the issue of crashing visuals (see image below).
- Power BI Audiences are very easy and intuitive to use.
Picture 6 - View Audience
Cons:
- You need to maintain multiple reports, so whenever there is a general change needed in all reports you should copy and paste this change to multiple reports.
- There is no security on model level, so each user connecting to the model in Power BI Desktop will see the “protected” columns (or measures based on these columns) and be able to create visuals using these columns (or measures).
Option 3: Showing “protected” columns as BLANK
This option is implemented in the measures themselves via DAX. The general reasoning is the following: if the user is in the “limited” role, the measure should return BLANK.
Summary:
- Create a helper table ‘OLS_helper’ in Power BI Desktop:
Picture 7 - OLS Helper
- Create different roles and specify a filter DAX expression for each role:
Picture 8 - Roles
Picture 8bis - Roles
- Create your measures that use “protected” columns with an IF-condition:
Margin Sum = IF(COUNTROWS(OLS_helper) == 1, SUM(F_Sales[Margin]), BLANK())
Pros:
- This setup avoids the issue of crashing visuals.
- If you don’t have many measures that use “protected” columns, the amount of extra work is limited.
- You only have 1 report, so there is no need to copy and paste general changes to other reports (difference with Option 2 described above).
Cons:
- Users that are not allowed to see certain columns, will still see that this data exists in the report (e.g. they will see the column “Margin” in a table or in a graph legend – see image below) – the data will just be BLANK for them.
- If you have a lot of measures that use “protected” columns, this setup can require a lot of extra work and can become difficult to maintain.
- If you are a report builder/editor and you are within the “limited” role, you will still have access to the full data model in Power BI Desktop. So you will be able to see the “protected” columns (or measures based on these columns) in the data model and create visuals using these columns (or measures). However, these columns (or measures) will show BLANK in the visuals that you are creating, so it will be difficult to validate the data in these visuals.
Picture 9 - View Blank
Conclusion
The standard setup for Object Level Security is easy to implement, but has the disadvantage of crashing visuals. At this moment there is no quick fix available to overcome this, but there are some workarounds possible. If you ever need OLS in your Power BI report, you can start from the pros and cons mentioned in this Insight to decide which option to go for.
For more insights & research visit the element61 Knowledge Base.
