MDX, Pragmatic Works, SSAS

Implementing Security With SSAS

5 Comments

Pragmatic Works just published a video on their YouTube channel put together by yours truly on implementing security in your SQL Server Analysis Services cube.

The video covers implementing basic dimensional security, cell security, as well as an extended look at implementing dynamic data driven security. The video is about an hour long so grab a bag of popcorn, sit back, and hopefully learn how to make your cube as secure as if it were guarded by a squad of Segway riding special forces commandos.

So check out my video Implementing Security With SSAS and feel free to post any questions or comments!

5 thoughts on “Implementing Security With SSAS”

  1. Hi Dustin, I am working on cube where I need to implement cell security as not measures need security but only few.
    I saw your video and its very well explained.But I am not getting what I am trying to attempt\achieve.
    Below is my structure..
    dUsers
    UserID UserAlias
    1 Laxmi
    2 Chris

    UserSecurity ( this is bridge table which links to dGeo on areaid )
    UserID AreaID
    1 1
    2 1
    2 10

    dGeo
    GeoKey AreaId AreaName
    1 1 UnitedStates
    2 10 Germany

    I have factTable1 (GeoKey, A, B , C,D)

    I need to have output where if I view the report I should see only measure A for United States as you see in usersecurity table my permissions.. and for A, C and D I should see data for all areas.. ( no security)

    I didn’t use dimension security as we need dynamic cell security based on who is login it should see the permission and allow that user to see data..

    I create scope in calculations tab of cube
    CREATE MEMBER CURRENTCUBE.[Measures].[HideA]
    AS False ,
    VISIBLE = 1 ;

    SCOPE ([Measures].[HideA]);
    SCOPE (StrToMember(“[d Users].[User Alias].& [” + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),”\”)) + “]”));
    THIS = ([Measures].[User Security Count] = 0 );
    END SCOPE;
    SCOPE ([d Geo].[Area Name].[All]);
    THIS = True;
    END SCOPE;
    END SCOPE;

    And then added this code to celldata tab of role..
    NOT ( Measures.CurrentMember IS Measures.[A]
    AND HideA
    )

    When I browse the cube
    I shows me data for A and B for all areas instead of US..

    Any idea\suggestion as to what am I missing ?
    Thanks,
    Laxmi

    1. I think you may have a syntax issue with your cell security expression in the role. Try something like this:

      NOT MEASURES.CURRENTMEMBER IS MEASURES.[A] AND NOT MEASURES.CURRENTMEMBER IS MEASURES.[HIDEA]

      If that doesn’t work, keep playing with it but I think that’s where the issue may be. Let me know what you find out.

  2. Thanks Dustin.
    I tried still no change..

    I tried slightly different created below in calculation tab of cube

    SCOPE ([measures].[a],[d Geo].[Area Name].MEMBERS);

    SCOPE ([d Users].[User Alias]);
    IF [Measures].[User Security Count] < 1 THEN THIS = 0 END IF;
    END SCOPE;

    END SCOPE;

    And then for role added below code in cell data tab:
    StrToMember("[d MS Sales Users].[User Alias].[User Alias].& [" + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),"\")) + "]")

    and in Dimension Data tab checked Enable Visual totals for dGeo dimension
    also added logic to Users diemsion as
    StrToSet("[d MS Sales Users].[User Alias].[User Alias].&[" + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),"\")) + "]")

    and checked Enable Visual totals for Users dimension too.

    When I run the cube with all these changes..
    I can see Measure A for only US but Gran Total is all for all area instead of US
    + When I see my User Security count it shows #N/A for all areas , for US it shows 1 and GrandTotal = 1 too
    which is correct.

    But for Measures A I am not sure why my grand total is sum of all areas instead of US only ?

    1. Sorry, just saw you said you did enable visual totals. I think your scope expression is not applying the calculation to the all level. Instead of
      SCOPE ([measures].[a],[d Geo].[Area Name].MEMBERS);
      Try this:
      SCOPE ([measures].[a],[d Geo].[Area Name].ALLMEMBERS);

Comments are closed.