easyROLES the fastest way to set up SSAS roles

Cross-dimension security

So far we have only looked at basic examples how to create SSAS security roles by limiting the users access rights to a member of one dimension. In real life situations we would often need to have a bit more advanced setup. Usually user right assignment is done on at least two dimensions within a cube. A good example of this would be a person working in a certain geographical area and only on one type of product. In such case we would need to secure both geography and product dimensions and the resulting behavior of the query would be similar to having two filters one for each of the discussed dimensions:

cross-dimensional-security-6

SSAS roles are very flexible and can accommodate such setup without any problem. The only concern might be that you will have to click even more compared to setting up security based on just one dimension…

The basic idea behind cross dimensional security setup would be that each role should allow access to the dimension member which it is responsible for and disallow access to all other role playing dimensions. So if we get back to our example, the geography role would give access to a certain member from geo dimension and disallow access to all products, whereas product role would slice the product dimension and disallow all access to geography dim.

Here is what we need to do in steps. First, lets modify the Pacific Sales region role which we created previously. We need to find the product dimension, choose Category hierarchy and click Deselect all members radio button. This way our role grants access to data under Pacific Sales Manager member and denies the users to see anything that is in the product dimension.

Removing access rights from one dimension with a role

Now we need to create a new role. Start as in previous example and when it comes to dimensional security, we want to allow the members of this security role to see only Bikes category from Product dimension:

cross-dimensional-security-3

And deny seeing any title-related data:

cross-dimensional-security-2

Don’t forget to check in the visual totals!

In the end, every user should have access to at least two roles in order to be able to use this cube: one role that allows to see a part of geography dimension and one role that gives access to a part of product dimension. We can test the double-role functionality (more on testing role functionality can be read here):

  cross-dimensional-security-4

And here is our sales count now:cross-dimensional-security-5

It totals to 91, just like we saw in the first screenshot of this post. From now on our Pacific sales managers data is being filtered on two separate dimensions with the help of SSAS security roles. Now you can go through all of this clicking or just install easyROLES and finish your project in only a few copy/paste actions.

 

Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookShare on StumbleUponEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

We want you to be comfortable with your purchase and therefore offer 30 day 100% money back guarantee.