Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
In Analysis Services you restrict access to data by creating roles, and then setting row filters controlling what each role can see. Read on for how to do all of this!
Suppose that you have the following product groups:
Product manager | Can see |
---|---|
Furry | Mammals |
Scaly and slimy | Amphibians and reptiles |
Feathery | Birds |
You want to create roles so that (for example) members of the Scaly and slimy role can only see this data:
People in the Scaly and slimy role would see amphibian and reptile data only.
This is the easiest way to create roles. First go into the roles menu:
Select this menu option while editing your model.
Our eventual aim will be to create 3 roles as required:
The 3 roles we're trying to create.
Start by clicking on the New button:
Click on this button to create a new role.
Give this role a name, (optionally) a description and say what members of the role should be able to do:
We'll allow people in this role to view data, but not to process it (ie not to load it into the model).
The next stage is to say for each role what data it should be allowed to view.
By default, members of roles will be allowed to view any data in your model (in this respect SSAS Tabular works in the opposite way to SQL Server, where a user starts with no rights).
To do this, against each table to which you want to restrict access type in an expression:
You can use double-headed arrows to widen columns and (as shown here) to make rows higher.
Type an = sign to begin your expression (you're trying to create a formula which evaluates to either True or False):
After typing =, you can use the usual DAX punctuation characters to make filling in a formula easier.
Here's an expression which would return True for any transaction if and only if it was for an amphibian or a reptile:
Note that use of the double-pipe || to denote "or".
That is, allow people in this role to view any row of data if it's for an amphibian or a reptile.
To check if your role works, choose to create a pivot table based upon your model in Excel:
Assuming you've set up 3 roles, choose to use the Scaly and slimy one.
You'll then only be able to view data for the two species in this role:
No matter how you slice your data, it will be as if Mammal and Bird data didn't exist.
The role that you use when connecting to a model is embedded in the connection string:
You can manually edit the connection created for a pivot table to use a different role, as shown above.
As well as creating roles in Visual Studio, you can also create them in SSMS:
Right-click on the Roles category within a model, and choose to create a new role.
Now give your role a name, description (perhaps) and permissions:
Here the role will be able to read data only.
You can now set row filters for the role, using the Row Filters tab in the above dialog box:
The problem is that you don't have the intellisense available in Visual Studio, so you have to type in the formula without help.
Here's what the final formula might look like:
Fingers crossed I've got it right!
You probably don't want to keep going into Visual Studio to manage your roles, so the next thing I'll show is how to create a permissions table in SQL Server controlling who can see which rows.
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:
CONTAINS (
tblPermission,
tblPermission[RoleName],
"Northern region",
tblPermission[RegionName],
Region[RegionName]
)
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.
DAX contains a function called CustomData, the sole purpose of which seems to be to make a connection string dynamic. The example below shows how to create a role which can be configurable to show the data for any MAM region.
To create a dynamic row filter to solve the problem above, first create a role as follows:
If the custom data flag is not passed in, this will return False; otherwise, it will return True only if the region for the current row equals the one passed in via the connection string.
If you're wanting to copy this formula, here it is:
IF (
CUSTOMDATA () = "",
FALSE (),
Region[RegionName] = CUSTOMDATA ()
)
If you now create a pivot table using this role, you'll see no data:
If you use this role when viewing a pivot table based upon your model, you'll see nothing!
This is because the custom data flag hasn't been set, and so the row filter formula for the region always returns False. To get round this, amend the connection string:
One way to edit the connection string used is to choose this ribbon option.
Choose to show the Definition tab in the dialog box which appears:
Click on this tab first.
Add a value for the CustomData flag:
You don't have to make this the last attribute, but it's easiest to read if you do.
When you select OK, your pivot table will show data for the London region:
For transactions occurring in the London region, the row filter formula now returns True.
I suspect that this is only useful if you have a client application in which it's easy to change the connection string (an example might be Reporting Services).
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:
=CONTAINS (
tblRegionPermission,
[UserName],
USERNAME(),
tblRegionPermission[Region],
Region[RegionName]
)
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 ...
In practice you'd probably want a security role to be shared by a number of different users of your model:
You've created a role called RegionRole to show only London data, but now want to assign this to specific people.
To choose people for a role, start by clicking on the Members tab in the dialog box above:
Click on the Members tab, and then choose to add a member.
In the dialog box which appears, choose Locations:
Choose which location to use.
Expand the Entire Directory to choose which domain to use:
Click on the + shown to expand the category to see the groups available.
Choose a group:
Choose a group which should be assigned to this role.
As an alternative to the above, you could search for a person or group:
Here I'm searching for a user containing Andy.B within the Entire Directory. Clicking on Check Names as shown will tell me whether the user exists (and if the user does exist, will fill in their full name).
Notes on how to add an Active Directory user group are shown in this StackOverflow article.
And with that, you've reached the end of this blog on security roles within SSAS Tabular!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.