locked
SSAS Security - Multiple User Profiles/Groups RRS feed

  • Question

  • Hi,

    I have a requirement from a client wherein my team has to implement security that is not inline with the additive nature of SSAS Roles.

    Security is to be implemented on 2 dimensions; Department(D1) and Time(D2) such that ::

    Profile 1: User A can view data for Department A (D1a) for 2016 (D2a) only.
    Profile 2: User A can view data for Department B (D2b)  for 2017 (D2b) only.

    I have about 300 such profiles so I want to avoid creating 300 different roles. Also, I want to avoid cell security as it causes performance issues.

    In short, is it possible to achieve this in SSAS security: (D1a and D2a) OR (D1b and D2b)

    Thank you in advance!

    -Dv




    • Edited by dv_09 Wednesday, March 14, 2018 3:45 AM
    Wednesday, March 14, 2018 3:11 AM

All replies

  • Yes, you can, but you will need to use "Cell Data" security.

    This item will give you a starting point.

    https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/grant-custom-access-to-cell-data-analysis-services

    You just need to write an mdx expression to ensure its

    Department A for 2016

    or

    Department B for 2017

    or, anything else that you want.

    Note, Cell Data security does have an overhead on every mdx query.

    Hope that helps,


    Richard

    Wednesday, March 14, 2018 3:33 AM
  • Since cell-level security has horrible reputation for performance, you could try adding a hidden dimension to use dynamic dimension security. It will have to be customized for your security model, specifically to be keyed on Department and Year, which could mean dimension keys for the D1 and D2 dimensions if only these can be made available in the fact table(s).

    Expect me to help you solve your problems, not to solve your problems for you.

    Wednesday, March 14, 2018 11:17 AM
  • This works great! Thank you very much Alexei. 

    Currently we have security on 4 dimensions. The only issue we encounter is, if a user has full access there billions of rows with all combinations of the security dimensions. Have you come across this issue?

    Is there an alternate solution to resolve the security issue via MDX in SSAS layer?




    • Edited by dv_09 Thursday, March 15, 2018 7:04 PM
    Thursday, March 15, 2018 7:04 PM
  • For SSAS MD, dimension and/or cell security is exactly the MDX/SSAS layer for security.

    For full access users, you could just add another role. Consider also whether you could leverage combination allowed/denied sets to bring bridge tables to manageable sizes. And of course, the initial Department/Year example certainly didn't look like billions of rows - perhaps you're setting filters on attributes that are too granular for the security model?


    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, March 15, 2018 9:20 PM
  • Hi Dv,

    Thanks for your response.

    I am glad to know that Alexei's solution works great for you. Please kindly them as answers, by doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 16, 2018 1:08 AM
  • Hi, were you able to solve this.

    I have a similar problem so wanted to check.

    Wednesday, August 5, 2020 2:17 PM