locked
SSAS 2008 multiple roles not applying RRS feed

  • Question

  • Hi,

    I have two roles and two measures.

    The first role can see all countries with measure 1 (exclude meaure two). This role works OK

    And the second role can only see a few countries with measure two. The second role is not being applied and shows all the countries from role 1.

    I'm using ssas 2008.

    Any idea how to do this pls?

    Thanks

    Friday, November 30, 2012 10:27 AM

Answers

  • That sort of security, where the user can still see all the members, just not certain combinations of cells is achieved with the Cell Data tab. see http://technet.microsoft.com/en-us/library/ms174786.aspx 

    You basically have to write an MDX expression that evaluates to false if you don't want the role to have access to the cell. (SSAS will return a #N/A)

    So you should be able to write some expression similar to the following (this is off the top of my head, I haven't tested this)

    count( intersects( (measures.[income] * {[Geography].[Country].[Spain],[Geography].[Country].[Germany]}, (measures.CurrentMember, [Geography].[Country].CurrentMember))) = 0


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Elvis Long Thursday, December 6, 2012 7:39 AM
    • Marked as answer by Elvis Long Monday, December 10, 2012 1:20 AM
    Saturday, December 1, 2012 7:46 PM

All replies

  • These roles work as expected when I test them individually in the cube browser
    Friday, November 30, 2012 10:33 AM
  • This is how multiple roles are designed to work.

    The allowed set for each roles is evaluated and if the user is in multiple roles the sets of members are unioned together. So the more roles a person is in - the more data they can see.

    You will probably have to create a third role with the exact permissions your require.


    http://darren.gosbell.com - please mark correct answers

    Saturday, December 1, 2012 2:20 AM
  • thanks, ok well it seems like a flawed design to me.

    i dont think a 3rd role will work either.

    below is a basic example of what im trying to achieve. the two individual roles i created work like below when browsed individually. but as you mention, are unioned together when browsed in the cube.

      Bookings Income
    Spain 20 denied
    USA 10 $100
    UK 20 $200
    Germany 10 denied

    any idea how to achieve this example?


    Saturday, December 1, 2012 1:05 PM
  • That sort of security, where the user can still see all the members, just not certain combinations of cells is achieved with the Cell Data tab. see http://technet.microsoft.com/en-us/library/ms174786.aspx 

    You basically have to write an MDX expression that evaluates to false if you don't want the role to have access to the cell. (SSAS will return a #N/A)

    So you should be able to write some expression similar to the following (this is off the top of my head, I haven't tested this)

    count( intersects( (measures.[income] * {[Geography].[Country].[Spain],[Geography].[Country].[Germany]}, (measures.CurrentMember, [Geography].[Country].CurrentMember))) = 0


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Elvis Long Thursday, December 6, 2012 7:39 AM
    • Marked as answer by Elvis Long Monday, December 10, 2012 1:20 AM
    Saturday, December 1, 2012 7:46 PM