If you follow my blog carefully, you’ve probably noticed that I prefer not to write about the features that are still in preview. There are multiple reasons for that, but the main is that it’s hard to evaluate and analyze a feature thoroughly, when you don’t know which functionalities will be available once the feature goes GA – it can easily happen that some of the functionalities will be improved, while other can be completely removed.
However, this time I decided to challenge myself! Maybe just because I was anticipating this feature for a long time in Power BI:)…
And, when I saw in Power BI Release Plan that this feature comes to Power BI at the end of February – well, saying that I was excited would be an understatement!
OLS in a nutshell
Let’s first try to introduce Object Level Security (OLS) in general. This is not a revolutionary feature in the Power BI. It’s not even a revolutionary feature within Microsoft’s BI stack!
As per Microsoft’s official statement, OLS enables model authors to secure specific tables or columns from report viewers. From a viewer standpoint, the table or column simply does not exist.
I was using this feature for a long time in good old SSAS Multidimensional. There, when I wanted to limit access to specific figures within the Cube, it was quite simple to achieve this goal, just by checking/unchecking the figures that the user (or more often, group of users) can or can’t access in the reports:
In the illustration above, you can see how members of this specific role in the SSAS solution, can see all the figures, except the measure called Company Costs. And that works brilliantly in 99% of cases! I’ll explain later why 99% and not 100%…
Once the member of this role connect to a Cube via Excel, or other similar tool, this measure will not be visible at all!
Now, going back to the statement that this works great in 99% of cases…Well, if you don’t give access to a specific measure to a user role, if you use that measure later as a reference in the calculation, and the user role HAS access to this new referenced measure, the user will get an error!
However, that can be easily solved by extending your code, something like this:
IIF(ISERROR([Measures].[Hidden Measure]),NULL,[Measures].[Hidden Measure])
The combination of IIF and ISERROR functions helps to control the flow of the calculation – in simple words, if a hidden measure returns an error (because the user can’t access it), return NULL. If there is no error (the user has access to a measure), simply return the value of the measure.
Not that you could control access to a measures in SSAS – you could also apply security rules to a whole dimension, or even to a specific cell!
Of course, there are a lot more advanced tips and techniques to learn when it comes to using OLS in SSAS. These topics are out of the scope of this article, as I just wanted to point out that OLS is not a revolutionary invention. In any case, if you are interested to learn more about OLS in SSAS, I strongly recommend reading the book “Expert Cube Development with SSAS Multidimensional Models”, by Marco Russo, Alberto Ferrari, and Chris Webb – this is a “Bible” for the Cube development, and there is a whole chapter dedicated to applying different types of security to your Cube data.
Understanding Row-Level Security
The concept of Row-Level security exists in Power BI for a long time. By using RLS, we can restrict access to data for specified users, on a row level. I’ll not go deep into details about RLS itself, as this is a well-known feature in Power BI – in case you are interested to learn more about it, you can check this article.
However, the definition of RLS, that it can restrict access to data for specified users, on a row level is, in my opinion, well…a little bit misleading. And, let me explain why…
Let’s say that I have a matrix displaying the total sales amount for every single product and country. Countries are COLUMNS in our matrix, and let’s imagine that we want to limit access to a specific group of users to see numbers only for Canada.
We would go to Modelling tab, Manage roles and create a role Canada, that will filter the data for Canada only:
And, if I choose View as role “Canada”, you can spot that only Canada numbers left in the report:
Now, if you think of Row-Level security as something that is applied strictly to rows…well, in this case it was a column:)
Therefore, I like to think about Row-Level security as limiting access to specific attribute(s) – for example, country (like Canada only), product category (i.e. Economy, Regular, etc.), customer’s location, and so on. Whether they are displayed as rows in your report or not!
On the other hand, I prefer to think about Object-Level security as limiting access to numbers (measures), such as Unit Cost, or Company Expenses, or complete data objects – for example, whole tables!
Ok, enough about RLS, I just wanted to illustrate why the feature name is kind of confusing to me (maybe it’s not confusing at all to others).
Object-Level Security in Power BI
So far, we were not able to control access to specific objects (officially). Therefore, I was applying a simple trick when I wanted to hide the whole table from the user.
Let’s say that I want to restrict access to a whole DimGeography table in my data model. I can go and do something like this:
I’ve created a role called Hide Geography, selected DimGeography table, and just put FALSE() as a DAX expression!
Boom! The matrix is completely empty now! That’s fine, but what if I want to hide only specific measure, for example, total sales amount (I mean, in reality, you would probably limit access to more sensitive measures than the sales amount, but let’s pretend that only a selected group of people can see total sales amount in our report).
Here comes the new OLS feature to the rescue!
Wait…How can I control OLS in Power BI?!
Yes, that’s the first…let’s call it limitation! You can’t specify OLS directly from Power BI Desktop – instead, you’ll need an external tool called Tabular Editor.
This is an amazing (still free) tool, created by Daniel Otykier, and you should definitely use it – be it for OLS, or not. Once you download Tabular Editor from here, you should be able to access it directly from the Power BI Desktop. Just go to the External Tools tab, and you will see all available external tools (the ones you’ve installed on the same machine where your Power BI Desktop is).
Gotcha #2 – not every Tabular Editor version will work for OLS!
In this example, I’m using a 2.11.6 version of Tabular Editor, and I don’t have an option to define OLS!
As soon as I’ve installed the latest version (2.15.2 at the moment of writing this article), I can see a whole set of new options for the specific column, one of them being Object Level Security:
Now, instead of Default OLS, I will set None value to Hide Geography role, for the Sales Amount column:
I will then save the changes made in Tabular Editor, and go back to Power BI Desktop to check what will happen once I start looking at the report as a Hide Geography role member:
Wait, what??!! I’m getting errors within my report! But, why, what did I do wrong? I’ve just restricted access to a Sales Amount measure…
If I’m a user trying to understand what has gone wrong…well, let’s say that the I will find this message quite awkward:
Gotcha #3 – Don’t try to “understand” the error message!
This strange outcome arises from the fact that when you apply OLS to a certain column, it behaves the same as that you deleted it from your data model! And, the following illustration confirms that:
As per Microsoft’s official statement, the key difference between RLS and OLS is that, when you apply RLS using DAX filters, data will not be displayed in the report, but the underlying metadata objects still exist in the model!
With OLS, the underlying object (in our example, Sales Amount column) is completely obscured from the data model – in plain words – these objects simply don’t exist for the users that don’t have permission to access them!
Additionally, all measures that refer to the restricted column will also “disappear” from the data model!
Look at this:
Sales Amt = SUM(FactInternetSales[SalesAmount])
This is a super simple explicit measure, showing sum of sales amount. And, this works as expected when the role has an access to a column Sales Amount:
But, as soon as I start exploring the report as a member of Hide Geography role, I will (again) see this “intuitive” error:
Conclusion
UPDATE 2021-04-09 as the OLS feature became GA: all considerations still remain
As I said in the beginning – it’s quite tricky to write about preview features, as they come with some limitations, that will probably be fixed in the future, once they become GA.
OLS in Power BI is a powerful feature, and I expect it to bring a lot more flexibility in terms of securing sensitive data. However, at this point in time, I would say that it’s still a “work in progress”.
The first potential obstacle is that OLS can be defined exclusively from Tabular Editor. Not that I dislike Tabular Editor – on the contrary – it is a fantastic tool that I’m using every single day for boosting my Power BI development. But, lot of companies simply don’t allow installation and usage of 3rd party tools (which Tabular Editor is). So, in those situations, simply forget about implementing OLS!
Other than that, I really don’t like the way Power BI behaves when a user doesn’t have access to a specific object or column! Ok, if you’re a report creator, you can assume what went wrong with visual(s) – but, if you are an ordinary end-user, there is no way that you would understand why your visuals are broken! Therefore, I firmly believe that the Power BI team will improve this “feature” – at least, showing the meaningful error message.
Thanks for reading!
Last Updated on April 9, 2021 by Nikola
