easyROLES the fastest way to set up SSAS roles

Add a new role in SSAS

The conventional way to add a new security role to SSAS, assign Active Directory users or groups to the role and limit the role to only certain members of specific hierarchies is usually a rather long and time-consuming process. Not to mention all those clicks will wear your mouse out in no time at all… If you still want to know how to do it the manual way, here is a quick guide for you.

Note: we will use “Analysis Services Tutorial” SSAS database provided by Microsoft as an example, assuming our user is a Pacific region manager and should have his/her access limited to this area only.

Lets start by creating the role itself. Connect to your SSAS database. There you should find “Roles” folder. Right-click this folder and choose ‘New Role…’.

The first step of creating a new SSAS database security role

In the new window that appears, name the role. Do come up with some kind of naming convention as this will make your own life easier in the end.

create-new-ssas-role-step-2

Note: We are not checking the Read definition checkbox in this example because it is not necessary to have for the end users if they are using excel or pre-defined reports to query the cube. The only setback would be when you want to see the cube in tools like Microsoft SQL Server Management Studio. In order to see the cube listed there, a user needs to have Read definition checkd in.

Once the role has a name, lets move to the next page (on the left side of the dialog window) called Membership. Here you should add all the users and/or groups from Active directory that should have the capabilities assigned to this role. I am adding a user called “Pacific Manager” for this example:

create-new-ssas-role-step-3

Now that we have added the users and groups to our newly created role, it is time to allow them to do things. We start with allowing them to read the cube itself. Go to the Cubes page and allow the role to read the Analysis Services Tutorial cube:

create-new-ssas-role-step-4

Now when the role members can read the whole cube, we should limit the access to only certain members of selected dimensions. Move further to a page called “Dimension Data”. In the first drop-down menu select the dimension which will be used to limit the access for the user. I chose Emploee dimension as it contains the sales force hierarchy. Use Title as Attribute Hierarchy and there we find Pacific Sales Manager member. I choose “Deselect all members” radio button and then select the member with caption “Pacific Sales Manager”. In the end Dimension Data page looks like this:

create-new-ssas-role-step-5

At this point our basic role setup is finished. Click OK.  “Pacific Manager” will now be secured and only able to see figures that are attached to member Pacific Sales Manager. As you can see, there are quite a few clicks involved and when you have to add hundreds of security roles in one go there is a huge risk to miss clicking a checkbox here or there. Once you’ve done this enough times and start to feel tired like we did before we created easyROLESgive our tool a try to see how fast you can get this boredom out of the way!

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.