Configure OLS in Power BI using Tabular Editor to limit access to non-financial measures
What Object Level Security (OLS) allows me to do, is to define which tables or columns users can or cannot see.
My example which I am going to detail below will show you how I will restrict a user from viewing sales data. The same user will be able to see Quantity amounts. This becomes really powerful because not all users need to see all the data.
My goal here is to show you how to the basics on how to use Object Level Security. Yes, there are more advanced options to configure a combination of Row Level Security and Object Level Security.
By using Object Level Security, it means that I can now have a single model which can be used for Financial and Non-Financial reporting.
I am going to configure to Roles.
- The users in this role can only see data from the Mideast.
- When testing manually the Mideast role should only show the following:
- For the Non-Financial role, they can only see the Order Quantity.
- Whilst this cannot be tested manually, the total order quanuty should be about 2.85M as shown below, this is because there is no filtering happening on any other item.
Creating the Roles
The first step is I had to create the roles in Power BI Desktop
- I went into manage roles and created the following roles.
- The first role I created was my Row Level Security Role.
I clicked on Create, then gave the Role Name of “Mideast”
- I then went to the City table and put in the filter for [Sales Territory] = “Mideast”
- The second role I created was for my Object Level Seurity Role.
I clicked on Create and then gave it the name of “Non-Financial”
- NOTE: I did not create anything else for this Role.
- I clicked on Save
Configure OLS in Tabular Editor
Now to configure the Object Level Security I had to use Tabular Editor to configure this.
As with my example I want to restrict the OLS role to be able to see Quantity Amount but NOT the Sales Amount.
NOTE: When restricting using OLS, I have to restrict the column.
NOTE II: Also, if there is a measure that uses one of the columns in a restricted column this too will have the OLS applied.
In my example the existing measure [Sales] is made up of the following:
- The above would mean if I restrict the column [Total Including Tax] with the OLS role the [Sales] measure would NOT work for users assigned to this role.
I am very fortunate to be using Tabular Editor 3, even though it looks slightly different it will work exactly the same way with Tabular Editor 2
- I clicked on External Tools and then clicked on Tabular Editor 3
I then expanded Roles to see the two roles I created previously.
The first thing that I needed to do is to enable the table where I want to apply the Object Level Security
- With my dataset this is my fact table which is called Order
- In the Order table I have got the columns [Quantity] and [Total Including Tax]
In Tabular Editor I clicked on the Non-Financial Role
- Then under the Security section, I expanded Table Permissions and change the permissions for the Order table from Default to Read as shown below.
Now what happens after I enable the read permission on the Order table is that the Role Non-Financial can now be expanded to show the Order Function (This did take me a little while to find!).
- I then clicked on Order under the Non-Financial role.
Now as with my example I wanted to restrict access to the column [Total Including Tax]
- I expanded the Security Section, then expanded the OLS Column Permissions.
- I then went down to the column [Total Including Tax] and set the permissions to None
- NOTE: The permission None means that the user who is part of this role will have NO ACCESS to the column [Total Including Tax]
- That is all that I needed to do.
I then clicked on Save in Tabular Editor
- I then saved my PBIX file and uploaded it to the Power BI Service.
Now to test the Row Level Security I can do this in the Power BI Service.
I went to my dataset, then clicked on the three dots, then selected Security
I then hovered on the right-hand side next to Mideast and clicked on Test as role
- I could now see the role being applied to Mideast and viewing the data as expected.
To test the OLS I continued from the above.
In the ribbon I clicked on the dropdown for Mideast.
- I removed the tick from Mideast
- I then enabled the tick Non-Financial Role
- Then clicked Apply.
Now as expected I should only see the Order Quantity and all the other measures should have a big X as shown below.
NOTE: The reason that I can see the measure [Order Quantity] is because I still have access to the underlying column [Quantity
In this blog post I have shown how to setup and configure RLS and OLS.
Whilst this is a basic implementation, I hope that it gives you enough insights to see how powerful this can be.
There would still be some additional planning around measures and which ones to show and put into reports so that I would avoid the big X.
Thanks for reading and comments or suggestions are most welcome!
Here is the original blog post from the Power BI Team: Announcing public preview of Object-Level Security in Power BI | Microsoft Power BI Blog | Microsoft Power BI