none
Mdx for power user

    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 03, 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 09, 2013 5:52 AM
    Wednesday, October 09, 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,


    Tuesday, October 01, 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 08, 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 09, 2013 5:52 AM
    Wednesday, October 09, 2013 5:51 AM