Configure Multi-Dimensional Analysis Services (OLAP) Security in the Jet Data Manager
This article applies ONLY to multi-dimensional Analysis Services. If your installation is using tabular analysis services, please see the appropriate article.
End users must be granted rights before they can access the cubes. These rights are configured using the Jet Data Manager.
This article covers the following topics:
- Create and add users to a role
- Database dimension versus cube dimension
- Configure user rights for a specific cube
- Configure user rights for multiple cubes
- Configure user rights for role-playing (renamed) dimensions
- Configure user rights for combined security
- MDX-based OLAP security
You must have administrator rights to the Analysis Services instance to make changes to security within the Jet Data Manager. Additionally, as an administrator in Analysis Services you will not need to explicitly define user rights for yourself as you will already have full access to the database.
Create and add users to a role
-
Open your project and under the Semantic Layer section of the Solution Explorer pane, double-click your multi-dimensional OLAP database. The icon will appear as it does in the below screenshot. The other databases that can be seen are tabular models and require a different configuration.
Double-clicking on the multi-dimensional database will open up a new pane in the Jet Data Manager which contains your OLAP database. Within this pane, right-click the name of the database, select Advanced and then Access Control.
If Access Control is grayed out, you will need to save the project and try again. You can only make changes to OLAP security when the project is in a saved state.
-
From the dialog click Add Role.
The Role Setup window will open.
-
In the Name field specify a title and then click Add....
-
The Add... box will pop up another box that is an active directory picker. The users or groups added here MUST be active directory users or groups. Type the name of the user(s) or user groups you wish to add and click Check Names to ensure proper entry. Once the correct users are added, click OKRepeat this process as needed.
-
You will now notice that the user has been added to the role setup dialog. Click OK.
-
Click the Deploy Rights button to grant the user(s) access.
The default set of permissions for a role is full access to all cubes and dimensional values. Keep reading to learn how to restrict this access. Once users have been granted rights they will need to connect to the cubes from Excel. To do this see: Connecting to the Cube Note: OLAP Server Rights are deployed to the *entire* OLAP server: If you wish to Deploy and Execute only one cube at a time, you will need to Deploy the security rights to the entire OLAP server, after you have completed that process.
Database dimension versus cube dimension
Every cube will contain dimensions and the same dimensions can be shared across more than one cube. Notice how our Sales cube has its own Dimensions node. These are referred to as cube dimensions. The dimensions in the lower node, outside of the cubes, are referred to as database dimensions. This allows for definition of rights for a single cube, or for every cube that contains the associated dimension.
Configure user rights for a specific cube
Set rights for the entire cube
In this example we have a role for our sales team. We want to restrict their access to only see information from the Sales cube.
Click the cube(s) to which you wish to restrict access.
-
Click the drop-down for the role for which you wish to change permissions.
The three most common settings are:
- None: The role will not be able to access or see the cube
- Read: The role can pull information from the cube, but can not perform drill-through actions
- Read with Drill-through: The role can pull information from the cube and can also perform drill-through actions
Select your desired permission and click Deploy Rights to save your changes.
Set rights on measures
In this example our sales team shouldn't have access to the Cost measure.
-
While in the Access Control dialog, within a cube there will be a "Measures" node. Clicking on this will bring up a list of all standard and derived measures in the cube with checkboxes next to them. By unchecking the box next to the measure(s) you want to restrict access to, not only remove is that role's access to that measure removed, but any other (calculated) measure that uses the denied measure will also be denied. In this case, the Cost measure is denied and so then would any other calculated measure based upon the Cost measure.
Click Deploy Rights to save your changes.
Set rights on cube dimensions
In this example the sales team shouldn't have access to any locations in New York.
Select the cube dimension(s) you want to restrict access to. In this example the Location dimension must be restricted.
-
Once the cube dimension that requires restriction is selected, change the inheritance setting to NoInheritance
Below is a short description of what each inheritance setting does:
- Inherited: The cube dimension will follow the permissions defined at the database dimension level.
- NoInheritance: The cube dimension will not follow the permissions defined at the database dimension level.
- Combined: The cube dimension will follow the permissions defined at the database dimension level unless further restricted.
When NoInheritance or Combined are selected, further restrictions may be applied to specific levels of the dimension. In this example there is only one level, Location.
-
Select the Rule Set (the default is Deny ).
-
Deny:
New members added to the top level will be allowed. This means the box will be ticked for newly added members.
For example: if we add a new member (location) to our dimension, our role will be able to see it. We do not need to grant it access manually. In other words, unchecking the box Explicitly Denies access to the member.
-
Allow:
New members added to the top level will be denied. This means the box will be un-ticked for newly added members.
For example: if we add a new member (location) to our dimension, our role will not be able to see it until we grant it access manually. In other words, checking the box Explicitly Allows access to the member.
-
Deny:
New members added to the top level will be allowed. This means the box will be ticked for newly added members.
-
Deselect any members that you wish to hide from the role. For example if we want to make sure our role can not access the New York members we would select the Deny rule set, tick the box for select All Location (the ALL MEMBER), and tick only those members we wish to see. When using the deny rule set you must ensure that the All Member is enabled. This will be the first member of any dimension.
Configure user rights for multiple cubes
User rights can also be defined at the database dimension level. This allows you to set a global permission that will be inherited by any cube in which dimensions have Inherited or Combined set in the inheritance settings.
Changes made at the database dimension level will be inherited at the cube dimension level, unless NoInheritance is selected.
Changes made at this level are similar to those made at the cube dimension level, except they are further reaching.
For example, perhaps the sales team further needs access restricted to only allow to be seen transactions with a Global Dimension 1 value of CORPORATE.
- In the Database Dimension area of the Access Control window, click the Dimension level(s) on which access should be restricted.
-
Deny:
New members added to the top level will be allowed. This means the box will be ticked for newly added members.
For example: if we add a new member (location) to our dimension, our role will be able to see it. We do not need to grant it access manually. In other words, unchecking the box Explicitly Denies access to the member.
-
Allow:
New members added to the top level will be denied. This means the box will be un-ticked for newly added members.
For example: if we add a new member (location) to our dimension, our role will not be able to see it until we grant it access manually. In other words, checking the box Explicitly Allows access to the member.
- Deselect any members that you wish to hide from the role. For example, if the Sales role should only be able to access records that have a Global Dimension 1 value of CORPORATE the Allow rule set should be selected, and only the box for CORPORATE should be checked.
Select your Rule Set (the default is Deny ).
This explicitly allows access to only the CORPORATE member, unless otherwise updated. New values would not be permitted. If instead, the Deny rule set was selected, and all other boxes unchecked, new members would still be permitted.
Configure user rights for role-playing (renamed) dimensions
In Analysis Services, inherited dimension security can only be used when the dimension name in the cube matches the dimension name of the database dimension.
Notice how the database dimension "Customer" is renamed as Sell-to Customer and Bill-to Customer in the Sales cube. This means security must be defined on the cube dimension itself using the combined option. To do this, see Configure user rights for combined security .
Configure user rights for combined security
A combined permission set allows the cube dimension to inherit permissions from the database dimension plus allows you to further define permissions on the cube dimension.
For example: We have a role called Sales Region A. In our Sales Cube we only want the users associated with the role to have access to our Atlanta Warehouses, but in our Inventory cube these same users will need to see all of our locations. To handle this situations we can configure combined security.
- Our database dimension will have access to all of our locations.
-
Click the cube dimension(s) and select
Combined.
- Inherited: The cube dimension will follow the permissions defined at the database dimension level.
- NoInheritance: The cube dimension will not follow the permissions defined at the database dimension level.
- Combined: The cube dimension will follow the permissions defined at the database dimension level unless restricted further.
- Click the cube dimension level(s) you wish to further restrict access to. In this example we have changed the permissions for Sales Region A to only see our Atlanta Warehouses.
- When using the deny rule set you must ensure that the All Member is enabled. This will be the first member of any dimension.
-
Click the
Deploy Rights
button to save your changes.
These permissions were only set on the Sales cube. If we were to look at the Inventory cube, we would see that our Location dimension is still set to Inherited . This means the location dimension within our Inventory cube will follow the permission set at the database dimension level.
MDX-based OLAP security
Instead of using the graphical user interface to configure OLAP Security you can use MDX (Multi-dimensional expressions).
Navigate to OLAP Server User Rights
Add or Edit the Role, navigate to the dimension member node where you want to define an MDX based allowed or denied member set.
-
Select either Allow or Deny rule set, and click NoMdx button in the grid.
-
Enter your MDX query.
The NoMdx button will change to Mdx.
If a member set is defined, any check marks in the individual members on the dimension are ignored. Only the MDX part is deployed to Analysis Services.