Sign Up for Training |
insightsoftware Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

Configuring Multi-Dimensional Analysis Services (OLAP) Security in the Jet Data Manager


Related Articles...

Overview

NOTE: 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 will cover the following topics:

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. 

Creating and Adding Users to a Role

  1.  Open your project and under the Semantic Layer section of the Solution Explorer pane, double-click on 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.

    mceclip0.png

    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 on the name of the database, select Advanced and then Access Control.

    mceclip1.png
    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.
  2. From the dialog click Add Role

    mceclip2.png

     the Role Setup window will open.

  3.  In the Name field specify a title and then click Add...

    mceclip3.png
  4. 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 OK. Repeat this process as needed.

    mceclip4.png
  5. You will now notice that the user has been added to the role setup dialog. Click OK.

    mceclip5.png
  6. 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 vs 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.

 

mceclip6.png


Configuring User Rights for a Specific Cube

Setting 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.

  1. Click on the cube(s) to which you wish to restrict access.

  2. 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
    mceclip7.png
  3. Select your desired permission and click Deploy Rights to save your changes.

Setting Rights on Measures

In this example our sales team shouldn't have access to the Cost measure.

mceclip8.png

  1. 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.

     

    mceclip9.png
  2. Click Deploy Rights to save your changes.

Setting Rights on Cube Dimensions

In this example the sales team shouldn't have access to any locations in New York.

  1. Select the cube dimension(s) you want to restrict access to. In this example the Location dimension must be restricted.

  2. 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
    mceclip10.png
  3. 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.

  4. 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.

  5. 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.
    mceclip11.png

Configuring 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.

    1. In the Database Dimension area of the Access Control window, click on the Dimension level(s) on which access should be restricted.

    2.  Select your 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.

    3. 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.

      mceclip12.png

      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.

Configuring 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 the section below labeled " Configuring User Rights for Combined Security "

mceclip13.png


Configuring 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.

    1. Our database dimension will have access to all of our locations.

      mceclip14.png

    2.  Click on 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

        mceclip15.png

    3. Click on 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.

      mceclip17.png

      When using the deny rule set you must ensure that the All Member is enabled. This will be the first member of any dimension.
    4. 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.
      mceclip18.png

MDX Based OLAP Security

Instead of using the graphical user interface to configure OLAP Security you can use MDX (Multi-dimensional expressions).

  1. Navigate to OLAP Server User Rights

  2. Add or Edit the Role, navigate to the dimension member node where you want to define an MDX based allowed or denied member set.

  3. Select either Allow or Deny rule set, and click NoMdx button in the grid.

    mceclip19.png

  4. Enter your MDX query.

    mceclip20.png

    The NoMdx button will change to Mdx .

    mceclip21.png

    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.

Was this article helpful?
0 out of 0 found this helpful

Comments