locked
dimension security on a dependent dimension RRS feed

  • Question

  • We have an existing secured dimension called scenario. Now we want to add security to another dimension called compensation but compensation is dependent on scenario.

    we have a dimension table for compensation. Also a user table which has scenario id, comp id and user id.

    What i need is to secure compensation for all users . Also when we query based on scenario only the compensation members data for the corresponding scenario for the user should be accessible.

    Any help is really appreciated...

    Monday, August 10, 2020 11:29 AM

All replies

  • Hi,

    2 possibilities:

    1) use cell security: not suggested because of performance reasons

    2) build up a new Dimension with one attribute and composite key scenario and comp. Hide this dimension. Create the measuregroup for your security, connect the new dimension and the user dimension. Built up the whole security on this new dimension. Activate VisualTotals on this new dimension. see: https://blog.crossjoin.co.uk/2011/12/22/replacing-cell-security-with-dimension-security/

    Kind Regards

    Monday, August 10, 2020 1:59 PM
  • Thanks for the reply. I tried using the second option .

    So basically this new dimension will be hidden. How do I apply on the security on the existing compensation dimension. So that if I try accessing the compensation members, it will show me only the data which I have access to based on the user and scenario.


    • Edited by sudi107 Monday, August 10, 2020 5:29 PM
    Monday, August 10, 2020 3:57 PM
  • No i was not able to.

     I tried using the second option .

    How the new hidden dimension will be linked to the existing compensation dimension on which I want to apply dimension security is not clear. So that if I try accessing the compensation members, it will show me only the data which I have access to based on the user and scenario.

    Friday, August 14, 2020 4:06 PM
  • Users are authorized on the new dimension based on scenario and compensation. As u have selected VisualTotals on this dimension, users will see data only for the combination of scenario and compensation (VisualTotals aggregate up to the All node only the allowed members) . Ofcourse, they will see also members in the compensation dimension but without data. 

    If the compensation dimension is fully dependent (1 compensation member only dependent on 1 scenario), then u could add the compensation information direct to scenario dimension

    Tuesday, August 18, 2020 7:03 AM
  • Hi,

    Here is the issue in details.

    Problem Statement - Apply dimension security on a dependent dimension

    We are trying to implement dimension security on [CompensationItem] which is having dependency on [Scenario].

    So an user should be having access to only those members of  [CompensationItem] which are mapped to corresponding [Scenario] members .

    Mapping table created with the following attributes  (Mapping Table : olap_user_comp_security) :

    [CompensationItem]. ID

    [Scenario]. ID

    [User].UserID

    Dimension Tables :

    HR_DIM_COMP - CompensationItem

    OLAP_SCENARIO – Scenario

    OLAP_USER - User

    Tried below approach :

    Current Implementation :

    1. Added mapping table to DataSource View

    2. Defined the relationships as described above.

    3. Measure Group created in the cube with the Mapping/Bridge table

    4. Added the relationships in the Dimension Usage Tab

    5. In the security Roles, added the below condition in the allowed member set :

       Exists([Dimension B].[Attribute Name].Members, CrossJoin(StrToMember(CustomData()),[Dimension A].CurrentMember), "[Mapping Table]")

    6. Checked Enable Visual Tools Checkbox

    Issue :

    Security is not evaluated based on [Scenario], its only evaluated with respect to the user data passed in customdata(user data) connectionstring.

    Security should be evaluated based on user and scenario both for CompensationItem.


    Tuesday, August 18, 2020 7:50 AM
  • We would like to sum the same case together at : https://docs.microsoft.com/en-us/answers/questions/71052/mdx-issue-for-multilevel-dimension-hierarchy.html

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 21, 2020 7:55 AM
  • Friday, August 21, 2020 9:10 AM