locked
Data level security on cube with no measures RRS feed

  • Question

  • Hello,

    Does anyone know how I can implement data level security based on Country, on a cube with no measures? To try and simplify this, let me explain and my apologies if this is not clear..

    I have a dimension table called contracts which has the columns Contract Name, Country, date, approved, etc.

    I have a users table (just has UserID, Name) and a security table that has the UserID and Country name that they user has access to.

    The Fact Table is basically just a table with some meta-data based off of the Contract table.

    • DIMENSION TABLE is linked to SECURITY table based on COUNTRY_ID
    • SECURITY table is linked to USERS table based on USERID
    • FACT table is linked to DIMENSION table based on FCT_KEY

    Below is the MDX query that I am using in the allowed set of the CountryID...

    NonEmpty(
    [DIMENSION TABLE].[Country ID].Members,
    (
    StrToMember("[Users].[User Name].[" + UserName() + "]"),

    [measures].[FACT_TABLE Count]

    )
    )


    There are no measures in this cube. My company is using Performance point and supposedly you cannot create reports based off of stored procedures in performance point, so I am stuck with using an SSAS cube for this.


    Any help would be appreciated.


    Dave SQL Developer


    • Edited by DaveDB Tuesday, November 13, 2012 3:03 AM
    Tuesday, November 13, 2012 2:48 AM

Answers

  • From what I think, you need dimension security and not cell security.

    You could create a measuregroup based on the security table. Let it be named as "Security". The dimensionality of this measuregroup is country and users.

    You can hide this measuregroup from users.

    You can update and put the following sample MDX in the allowed set for the country id attribute

    Exists([DIMENSION TABLE].[Country ID].Members,StrToMember("[Users].[User Name].[" + UserName() + "]"),"Security")


    ZA

    • Proposed as answer by Elvis Long Tuesday, November 20, 2012 2:36 AM
    • Marked as answer by Elvis Long Friday, November 23, 2012 10:01 AM
    Wednesday, November 14, 2012 5:56 PM

All replies

  • Thanks for all your help guys. Apparently using MSDN forums for SSAS help is a waste of time.

    Dave SQL Developer

    Tuesday, November 13, 2012 7:27 PM
  • From what I think, you need dimension security and not cell security.

    You could create a measuregroup based on the security table. Let it be named as "Security". The dimensionality of this measuregroup is country and users.

    You can hide this measuregroup from users.

    You can update and put the following sample MDX in the allowed set for the country id attribute

    Exists([DIMENSION TABLE].[Country ID].Members,StrToMember("[Users].[User Name].[" + UserName() + "]"),"Security")


    ZA

    • Proposed as answer by Elvis Long Tuesday, November 20, 2012 2:36 AM
    • Marked as answer by Elvis Long Friday, November 23, 2012 10:01 AM
    Wednesday, November 14, 2012 5:56 PM