In the February 2021 release of Power BI Desktop – Object Level Security was finally released into public preview. This was one of the last features that existed in Azure Analysis Service (AAS) and SSAS – that finally found feature parity in Power BI.
Well what is it?
In short – Object Level Security allows the data architect to ‘completely disappear’ a column or table from data model – so that it’s as if it never existed.
Let’s demonstrate a use case using this fictitious employee table containing salary amounts. It’s reasonable that SOME people using this data model might need to see these columns – but certainly not EVERYONE.
One previous strategy might be to ‘hide’ the column from the report view using the visibility option on the model screen
but this technique only hides the column values – it never actually removes the column metadata from the model.
Users connecting to this shared dataset can clearly see the lingering metadata and hidden column header.
For many users – maybe this strategy is good enough since the column values are no longer accessible – but for some organizations – the column headers can be just as sensitive as the column contents. In these cases – object level security can help.
Once OLS is applied – the column and its associated metadata (like the column name) are completed removed from the data model. It is a true security protocol – like Row Level Security.
Here’s how to setup OLS in a Power BI Desktop file:
1. Create 1 (or more) Roles: In my case – I’ll use one role for those who can see the salary column – and one for those who cannot.
2. Open Tabular Editor and navigate to the Table and Column of interest. In the Properties pane (bottom right) scroll down to “Translations, Perspectives, Security” – and find Object Level Security. Then change the assignment for the Role of interest to “None”. (In this example I changed the context to “None” for the Role “NoSalaryView”)
3. Save the changes to the model:
We can easily test this in Power BI Desktop using the View as Roles tool
and then by navigating to the Table View, we can see that the salary column is completely gone.
Awesome! – any report builder connecting to this as a Shared Dataset – will no longer see the column or it’s lingering metadata.
But what happens when another developer takes over this project – and opens the PBIX file. How would they know that OLS has been applied?
As it stands right now – there are absolutely no breadcrumbs of an OLS implementation in the PBIX file.
Nothing shows up in the Role Table:
Nothing appears on the column properties window:
The only place we can learn that OLS has been applied – is by opening tabular editor and reviewing the table permissions on each role.
This action of routinely opening Tabular Editor when taking over a project is still not a habit (especially if the project can be opened via a PBIX file) – so let’s consider some best practices for flagging that next developer to give them a ‘heads-up’ about OLS being applied:
Here are two suggestions:
Add comments to the DAX Expression box for the Role & Table
This is super easy – very obvious – and so long as we prefix each line with “- -” or “//” it doesn’t throw an error.
Add a description to the column properties when setting up OLS
Once the data model is refreshed – these comments show up on the report canvas screen
and on the model view screen when the column is selected
These are great ways to ensure that everyone handling the PBIX file – can easily identify that OLS has been applied.