Hide Columns or Tables using OBJECT-LEVEL SECURITY - Beginners Guide to Power BI in 2022

in this video i'm going to show you how to hide or show columns or table data in power bi using object level security i'm going to show you how to set it up in a context of why you would use this in a real world scenario all of that and more so without further ado let's get started hi my name is fenan and welcome to the solutions abroad youtube channel where we cover tips tricks and best practices when working with power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so power bi as a tool does a great job at making report developments a lot easier i've never seen a tool that allows you to end to end create visualizations and insights out of raw data really really quickly and when working with data in an organization this can either be a curse or a blessing especially if you're working with sensitive data that's because you may have access to sensitive information like say basic salary or home addresses which can be easily be made into data visuals and at the same time it can easily fall into the wrong hands luckily power bi provides a lot of different options for you to secure your data like sharing to specific people adding sensitivity labels you can even use an option called row level security which defines roles and what those roles have access to in terms of rows of data however you will find some instances where you want to just hide a whole column or even a whole table itself just purely because of the data that they contain this is where the feature object level security comes in really handy let me show you so here's an example of a power bi report that contains people data i've loaded a sample data set here which has one table people and it has information about people and different information about them such as their names date of birth email gender and annual salary we visualize the people information that we have in a table here in the report view and put the average annual salary as a card on top of it and these visuals by itself provide you great insight for the company this is the type of information that you might want to hide not on a row basis but the whole column itself you might get a requirement that requires you to hide that column to everyone aside from the senior management so first to configure object level security we first need to have a role defined so that we can assign the object level security to it if you don't have one set up yet we can create one here so let's go to the modeling ribbon here we click manage roles this will open up the manage roles windows here we're going to create a new role which we will assign people to to say that their views will be restricted so we can create one here we're just going to name it public and normally you will define which tables or which row level data this role has access to now you can still do that but for now we're just going to leave it empty and we're going to hit save so that we've created that role in our data set then to configure the object level security that that role has access to we need to use a third-party tool called tabular editor this tool lets you peek into the data model metadata that you can't do just with power bi desktop itself if you want to know more about what other things you can do with tabular editor i covered it by itself in a separate video so go check it out if you haven't yet so let's open tabular editor here and we'll show you everything that is available in your data model so things like your tables your columns and if you have any measures and also you will notice here we have our roles which is the role that we've created just now if we select that role in this tabular editor you'll see on the right hand side here under the expression editor you will find a list or a summary of what you can peek through to the metadata of the roles so in this case you can see under the security drop down here you will see what rls and ols have been applied to your role to hide tables from a role you simply expand on the table permissions here look for the table that you want to restrict access to and just change it to none this will hide that table to anyone assigned to that role so let's preview this before we continue so i'm just gonna save this i'm gonna close tabular editor and we're going to preview our view as that role so basically seeing the report in the eyes of someone assigned to this public role that we've created so if we hit okay so you will see those two visuals that we created the card and the tables have all aired out from the perspective of anyone assigned to this public role this table that we have restricted their access to will look as if the table didn't exist at all in the model so you'll see in the field section is just completely disappeared even if you try to fix these visuals you will see that it will just result to nothing because it can't find the fields in the tables that it's supposed to look for however in this case we don't want that though we just want to hide certain columns or just one column actually annual salary so let's go back by clicking stop viewing now we've uh we just need to revert this so we're just going to drag in the annual salary once more we just drag in first name last name i believe date of birth and salary so this is how it looked like before let's go back now to tabular editor once more and let's go back to our roles and let's remove that restriction in our people so we're going to set that back to default now under the tables you will have a list of all the tables and fields that are in your data model and here you can configure pretty much the same thing you can hide tables using the ols if you want to or you can expand on the tables themselves into the individual fields to configure them so if we look at let's say the annual salary you will have other things other metadata that you can sort of customize under the translations perspectives and security you will see that you have the same option object level security that you can control on this column so let's do the same thing that we did just on the column level basis so we're gonna expand on the object ols it will list out all the roles that we can configure we have the public here we're going to set that to none we're going to go and save this go back to our reports and let's do the preview once more so let's view as public once more if we hit okay here you will see that again it broke both of our visuals but it has a stark difference from the previous one that we've done so as a user assigned to this public role you will still have access to the people table and all of its column aside from the annual salary which we restricted it to none so this means that any visuals that reference the annual salary will break as expected so this one for example it averages out the annual salary it will simply just disappear however this table which has other information about the people if you hit fix it will just remove the error which is the annual salary that didn't exist so you will still have full access to the people in its column aside from what we've set which is the not to see annual salary so let's revert this view and let's bring this back one last time so now we've created a role we've adjusted the object level security so this role doesn't have access to annual salary it's now time to configure this in power bi service so let's start by first publishing this into the service and then i just publish it in one of my workspaces here let's open the reports in power bi service so this is the exact same reports but now we are viewing it in power bi service now we need to assign people to this public role so for you to do that you need to go to the data sets security and there should be a shortcut here so under c related content you will see the data set here you can simply go to more options security and you will see in this option you will have the list of all the rules you've created we have public here and you can simply assign people that you want to have or include in this role so let's just say for example let's assign john you simply assign them click add and save and that's it that's your object level security setup in your power bi report and that's really it for this video i hope you now know how easy it is to set up object level security to hide or show your tables or columns in power bi thanks for watching as usual give this video like if you found it useful give it a dislike if you didn't so i need to do better for next time ask your questions in the comment section box below so i can help you and you can help others if you really like this video we have a patreon page where you can support the channel and get exclusive perks like early access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye

Channel: Solutions Abroad

Views: 8,488

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi object level security, object level security, ols, power bi ols, power bi security, power bi hide column

Id: G3_Gu5FTUhw

Length: 10min 24sec (624 seconds)

Published: Wed Mar 30 2022