562 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 four 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.
DAX exposes a function called USERNAME(), which - as the name would suggest - returns the name of the current Windows user. You can use this to customise what a user sees.
Throughout this blog page, assume that my Windows user name is TREETOPS\Wally.Owl.
Let's start with a really simple role, which would show frogs to me and nothing at all to anyone else:
Client tools using this role will only see data for frog products, viewed by people who are logged on as me.
Here's a typical pivot table viewed using this role:
Because I'm logged on as me, I get to see frog data; no one else would even see this.
You could adapt the permissions table that I showed earlier in this blog to filter who sees what. To do this you'll need a permissions table - you could download and run this script in Management Studio, although it might be quicker to create the table manually:
Don't forget you'll need to change the user name to your own logon name!
Now import this into your model:
Import the table in the usual way.
Create a role as follows:
The role will show data for a transaction, for example, if it can find a row in the region permission table which matches both the currently logged on user and the region for the transaction.
Here's the expression should you want to copy it:
You can now analyse the model using an Excel pivot table, assuming the role you've just created:
Show a pivot table using this role.
The result makes it all worth it!
The only data I can see is for the two regions assigned to me in the region permissions table.
This is almost perfect - but it would be nice if I could control access by groups, rather than users ...
|Parts of this blog|
25 Aytoun Street