locked
Dynamic Security in SSAS 2008 RRS feed

  • Question

  • Hello SSAS Experts,

    I have basic issue of dynamic security. My Cube has SalesRep Dimension (Recursive) with parentid (representing their managers), SalesRep Dimension has also thier username, login id and other information. SalesRep Dimension is connected to Sales fact table. I need help how can I implement security so;

    1) Each sales rep can see only its own data.

    2) Each Manager can see his data and employees under him.

    I appreciate all your help in advance!

    Thank you

     

     

     

     

     

     

    Monday, February 21, 2011 12:41 AM

All replies

  • Vinuthan,

    Thanks for the reply but my situation is bit different than the link you have provided. I do not have any reseller (or reseller type) entity in between. I have a user table (sales reps in my case) directly connected to Fact Table. There is no bridge table involved in between.

    Thank You

     

    Monday, February 21, 2011 3:56 PM
  • In theory you should just have to secure the "Parent" attribute in the P-C hierarchy and then that will automatically take care of giving access to that member and any descendants.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Tuesday, February 22, 2011 4:55 AM
  • Just add to Darren, for dynamic security, you need to assign the permission to the Role according to the MDX expression dynamically. In your scenario, you can still use the same concept. For the link Vinu posted, the model is:
    User table < - -  bridge table - -> Reseller <- -  fact table

     

    However, you merged three tables into one table: SalesRep. You can try to split the table and then use the link posted, or you can follow the same concept without using any bridge table but change the MDX expression like following:

    nonempty([SalesRep].[ SalesRep].[ SalesRep],

    StrToMember("[SalesRep].[UserName].&["+UserName()+"]"))

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Tuesday, February 22, 2011 7:17 AM
  • I appreciate your response Raymond and Darren,

    Please correct me if I am wrong, I apply the MDX expression you mentioned to my SalesRep Dimension Data Tab?

    If yes, then I am assuming that all the fact tables my SalesRep dimension is directly attach, will be secure in a way that Sales Rep would be able to see only thier own data?

    Taking it a bit further,

    My Sales Rep Table has following columns:

     SalesRepID, SalesRepName, SalesRepLogin (domain\username),ManagerID

    Will the above MDX would take care of this parent child relationship, i mean Sales Managers would be able to see thier own data and also thier employees data, while employee would be able to see only thier data?

    Thank You

     

     

     

     

     

     

     

     

    Wednesday, February 23, 2011 9:58 PM
  • Raymond,

    Your query works fine, but if the tables is parent/child. It does not work. Do you have any knowledge how can I implement it on parent child table?

    Thank You

    Arif

    Wednesday, March 9, 2011 4:43 PM