being able to manage the permissions of your
users as they interact with your tabular cube is really important
and in this video we're going to take a look at using roles to help facilitate that process
now in a previous video when we were talking about processing a cube we had jumped into sql
server management studio and created a role directly through here it was as simple as
right click new role and we went through that the problem with that is as soon as we make any update
to our model here in visual studio and deploy it it's just going to override that and in this case
it actually deleted it so what we need to do
is be able to create them within our project and that's what we'll look at here
so roles are ways to group users and limit their permissions based on
operations maybe reading processing as well as row level security or specific columns or tables that
you want visibility to now within here there are two different ways you can get to that obviously
we have roles over here you could right click and go to roles and it'll bring up the role manager
the other option is go to model roles it'll get you to the same place and this screen will look
very similar to what we saw in management studio so what we'll do here is we'll add a
new role we'll add a user to it and then view the results in excel by logging in as different users
so we'll start off here we'll create a new role i'll just call this test role
permissions here you can see are we giving you permissions to read read and process just
process do we want them to be an admin so this is nice as you build it out for your organization
you can group individuals or services or whatever into different roles and be able to consolidate
where you're setting those permissions in this case we'll just do read we're going to create a
read only role you can put a description i won't in this case and now down here we can see it's
recognizing the tables that we have and you can implement row level security if you're curious
about row level security there's a separate video that covers this we won't add anything here in
this case members this is where you can specify your users or groups for this role so maybe you
have an active directory group of users you could do that here or like it says individual users
in our case i'm going to add another user on my machine that's called admin and add this here
so this will be the only member that is going to inherit the permissions of this role the next tab
here is tables and columns and this is actually something different compared to what you would
see here in management studio when if you try to create let's say a read only role you don't have
the ability to restrict visibility to specific tables and columns outside of row level security
in this case we can actually easily go through and select them and what i'll point out here is
this is indicating what should not be visible to users so by default everything is turned on but
if you want something not to be visible you would select it here so let's say for this case we do
not want internet sales visible so it's going to select every single column and we'll select ok
now we can see this role over here and who's in it we can see admin
is within this test role we'll save and let's go ahead and deploy this
so again now that we're deploying it's going to include this role that we created
and it would override anything that was there with whatever set in this project
file close let's hop back into visual studio and refresh and we can see here now this role exists
and it was deployed with read access here in our server on management studio
so now let's actually see what this looks like as a result
so now let's take a look at the result from a user perspective i'm going to open up a brand
new excel sheet and go to data get data and we'll connect to our analysis services
server here this is the user that will not have any restrictions
we won't select this one we'll select the tabular demo which is the
deployed demo this one is what's in our working environment and we'll do the full cube finish
so we can see here we have every model here including specifically internet sales but
what i'll do now is open up a new sheet and connect again but this time i'll connect
as the user that admin user that had the restricted permissions cube finish okay
let's minimize these and we can see here we don't have
that internet sales view here if we go back to the other one here it is we go to admin and it's gone
let's go back now and make another change just to see how this flows through let's go back to
this role and this time what we'll do is allow visibility to just a few of these columns here
just to show you how this works like i mentioned before anything that's selected here is not going
to be visible we are going to give visibility to these but any of these other ones that are still
checked will not have visibility for anybody in this role okay now we'll save and deploy again
we're back in excel i have not refreshed it but what we'll do here is right click and refresh
and now over here we can see internet sales we have those few columns but we don't have
the ones that we excluded if we go to the original one refresh we go to my mjk user
this one has all of them admin just a few
that's an introduction to using roles within your tabular model it's another way to manage
your users and permissions some visibility as well and can be used in conjunction with
some of the other tools such as row level security perspectives and things like that