Setting up SSAS Multidimensional Security in TimeXtender
When you have cubes set up, you would like access to all of it to be restricted, for some people. This could be that salespersons, can only see their own data and not their colleagues.
Here below is a guide to how you can do this, or other things.
Please note that any user who has Administrator rights on the OLAP server will be able to see anything, no matter the rights you set up. It is a requirement for the users running TimeXtender that they have this role.
To set up security in TimeXtender go to the Cubes tab, right click your OLAP server and select OLAP Server User Rights
To be able to set up the rights, the cube must have been deployed and executed since the last changes has been done.
In the dialogue that opens, you can set up rights for every object and value in the cubes.
To do so, click Add Role and a new dialog will appear from where can assign a name for the security role:
You can add users/groups to the role in two ways, either by picking the user/group from the standard windows user/group picker or you can enter the users manually:
Click the Add… button to pick users/groups:
If you also want to choose a group, but you might need to turn it on first.
Click the Add Manually... to enter users/groups manually:
When you add a role, all members of the role will have read access to everything, you can then defer from that setting on any single object in the tree.
On a cube you have the following options:
No rights on the cube
Read rights on the cube, the cube can be browsed
Read rights, and the users can perform writeback to the cube
Read with drillthrough
Read access, and on standard measures, the user can drillthrough and see the data warehouse records that the value consists of.
Write with drillthrough
Same as above with writeback support.
Read with drillthrough and Local Cube
Additionally gives the ability to save a local copy of the cube.
Write with drillthrough and Local Cube
Same as above with writeback
The access to measures and dimensions are handled in a similar way. Additionally it is possible to set up rights for any member of the dimensions, for instance give certain roles access to specific companies, only rights to see specific performance values and so on.
There are two ways of setting up user permissions on dimension members in TimeXtender.
If the permissions are global, meaning they should apply to multiple cubes where the dimension is used, then setup the permission on the dimension itself and set the permission level to Inherited on the cube. This is the default setting.
If the permissions are specific for a single cube, then define the permissions on the dimension node on the Cube and set the permission level to NoInheritance.
Lastly there also is a Combined choice. This means that the settings are the same, except where there are differences.
In other words, the way of handling permissions allows you to set up a default behaviour for the dimension that will be inherited on all cubes. You can defer from the default setting by setting the permission level to NoInheritance, or Combined and setup local permission for the cube/dimension combination.
This example will restrict members of the role to see data from just one company on all cubes where the “Company” dimension is used:
First, setup security on the global dimension:
The All member (All Company) should always be Checked or you permission setup will not have any effect!
If Enable Visual Total is not checked, then the total will be the grand total for the all companies, if set to Yes, the total will be calculated only on the companies where the role has read access.
If the local cube dimension is set to NoInheritance then it will inherit the settings on the global dimension:
I have only set the Users role to Inherited on the dimension and NoInheritance on the Admin role. I have also set up a different array of rights. The User role will be ignored and the Admin role will have different rights than the global dimension.
To deploy the rights to the OLAP Server click the Deploy Rights button. After this, the rights will be deployed whenever the OLAP Server is deployed.
If no error occurs, the following message will appear:
When using the Multiple Environment Deployment feature of TX, the security setup can be differentiated per environment. When TX detects that it is running in a multiple environment setup, if offers the option to setup users/groups for roles differentiated Multiple Environment Role Members or not Local Role Members.
If Multiple Environment Role Members are selected, then you can select users and groups per environment.
The green dot indicates whether the environment can be contacted or not.
If an environment is deleted, then it will appear as “Unknown Environment”.
If the environment in known by TX but can not be contacted, then it will appear with a Red dot. Users and groups can be added and removed even though the environment cannot be contacted:
Using background processing
When you use this feature and want to set up security, you now have two fields to choose between.
When adding the changes you should always deploy them to the Front Database.
After that, to make sure they become the general security settings, deploying the cubes will make sure it is not removed, on the next execution of the cubes.
DimensionPermission The dimension was not found when the string was parsed
This is an thing that happens with Excel, it does not happen in PowerBI.
If you set up rights on the Dimension level like so.
Set the cube dimension to Inherited from the dimension (Default setting) and giving it a different name than in the Dimension.
Then you deploy the role and may experience the following error in excel when you attempt to preview the dimension.
This is because you cannot use Inherit on role playing dimensions (Cube Dimensions which have different names than its source dimension).
The solution is the following.
Change the cube dimension to NoInheritance.
Set the Cube Dimension to have the rights.
Now if you deploy the role and preview it in Excel it looks like so.