locked
Mdx for power user RRS feed

  • Question

  • Hi 

    I am implementing dynamic security in the cube for that i have one user table which keep track of power user to give access to all the reseller

    user      poweruser
    user1         N
    user2         Y

    I want to write MDX if user  has poweruser column value 'Y' then show all reseller.
    something like below

    iif(        ),
    [Reseller].[Reseller].Members,
    EXISTS(
        [Reseller].[Reseller].Members,
        STRTOSET("[User].[User].["+Username+"]"),
        'Reseller User'
        )

    or any other way to write it?

    Thanks,

    Thursday, October 3, 2013 6:28 AM

Answers

  • here is the script its working now.

    iif(isempty((STRTOMember("[User].[User].["+mid(username,instr(username,'\')+1)+"]"),[User].[Power User].&[Y])),
    NonEmpty(
        [Reseller].[Reseller].Members,
         (STRTOSET("[User].[User].["+mid(username,instr(username,'\')+1)+"]"),
        ([Measures].[Fact Reseller User Count],[DimFactResellerUser].[Scope Type].&[RES]))
        ),
    [Reseller].[Reseller].Members)

    Thanks,

    javed

    • Marked as answer by javedkha Wednesday, October 9, 2013 5:52 AM
    Wednesday, October 9, 2013 5:51 AM

All replies

  • Hi,

    I have implemented dynamic security in the cube using below expression in role allow member set section.

    EXISTS(

        [Reseller].[Reseller].Members,

        STRTOSET("[User].[User].["+Username+"]"),

        'Reseller User'

        )

     if I want to give access to all reseller member for a user. I have inserted one value in the Dimreseller table is "ALL Reseller" and mapped it in the bridge table.

    my problem is how to modifies above expression so that when user who mapped to "ALL Reseller" in the bridge table able to see all the reseller members.

    OR is there any other work around ? I don't want to insert all the reseller member for a user that is why I am trying "ALL Reseller"  kind of approach.

    Thanks,


    • Edited by javedkha Tuesday, October 1, 2013 8:31 PM
    • Merged by Elvis Long Tuesday, October 8, 2013 1:21 PM duplicate issue
    Tuesday, October 1, 2013 8:30 PM
  • Hi javedkha,

    I don't know we can achieve this requirement in your scenario and SSAS dynamic security is based on window authentication.

    serena,

    Tuesday, October 8, 2013 11:56 AM
  • here is the script its working now.

    iif(isempty((STRTOMember("[User].[User].["+mid(username,instr(username,'\')+1)+"]"),[User].[Power User].&[Y])),
    NonEmpty(
        [Reseller].[Reseller].Members,
         (STRTOSET("[User].[User].["+mid(username,instr(username,'\')+1)+"]"),
        ([Measures].[Fact Reseller User Count],[DimFactResellerUser].[Scope Type].&[RES]))
        ),
    [Reseller].[Reseller].Members)

    Thanks,

    javed

    • Marked as answer by javedkha Wednesday, October 9, 2013 5:52 AM
    Wednesday, October 9, 2013 5:51 AM