563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to use security roles to restrict access to your SSAS Tabular model
Part two of a five-part series of blogs
Perspectives in Analysis Services let you show different parts of your tabular model to different people, but they don't have any security. To ensure that the right eyes see the right bits of your model, you'll need to create and manage security roles, as explained in this blog.
The obvious disadvantage of roles so far is that they take ages to set up, and whenever you need to change them you have to make design changes to your model. Time to improve this!
Much better if you could create a table in SQL Server like this:
This table allows you to specify which roles can see data for which regions.
For example, here's what you should see if you view sales by species and region using the Northern region role:
Anyone in the Northern region role should only see data for 3 regions.
You can follow the rest of this page by downloading and running this file in SQL Server Management Studio (it will create a table called tblPermission which should look the same as the table shown above).
First create the permissions table (perhaps using the script above) in Management Studio, then load it into your model:
Load the permissions table into your model in the usual way.
You should now hide this from client tools:
You really don't want the contents of this table becoming part of the pivot table field list!
Note that this table shouldn't be linked to any other (so don't create any relationships for it).
To get our role to work, we need to find out for any row of data whether the region to which it belongs exists as an item in the permissions table for the role currently in use. We can use the CONTAINS function, the syntax for which is:
To get this to work, create a role (I'm using the example of the Northern region role) with permissions set as follows:
The DAX expression returns True for any row of data if we can find a region matching the row's region name for the Northern region role.
For a transaction within the Trafford Centre (a big - at least by UK standards - shopping centre near Manchester), this formula would return True because of this row:
If viewing a pivot table using the Northern region role, the CONTAINS function would (for a Trafford Centre transaction) find a region matching the current one as highlighted here.
Should you wish to avoid having to type in the DAX expression, you could copy this:
It's probably a good idea to stop any role from seeing any of the data in the permissions table:
Don't rely on hiding the table from client view, but also state that this role shouldn't be able to see any of the permissions rows.
You can now check that your roles work by using them to show pivot tables:
Choose to present your model's data in a pivot table, as usual.
Now choose a role to use:
I've created roles for each of the regional managers in the permissions table.
You should now see data just for the region you chose!
What you might get if you chose the Northern region role.
The great thing about a permissions table like this is that you can now change your permissions in Management Studio, without editing your model.
|Parts of this blog|
25 Aytoun Street