Measure Security


  • Hello guys!

    Here is the deal:

    I wish to make the measures just show the data that is related with the logged user permissions.



    Fact table


    User id




    User Dimension


    User id


    Data examples



    Userid = 1 Measure1 = $5,00

    Userid = 2 Measure1 = $7,00


    I get the current user permision as a member set like {[userdim].[userid].&[1],[userdim].[userid].&[2]}, almost like if it was logged as multiple users at a time.


    In this example lets say that user1 have permission just to him, so i have this {[userdim].[userid].&[1]}.

    The user2 have permission to him and user1, so i have this {[userdim].[userid].&[1],[userdim].[userid].&[2]}.


    So if the user 1 is logged i want him to see only the $5 when browsing the cube.

    If the user 2 is logged i want him to see the $12 when browsing the cube.


    I can make this by query, but i want to apply this to server so he can create his own reports using excel.

    I thouhgt about creating a calculer member to that but dont know how can filter the measures values that are related to the user.


    Here is how it works but using mdx querys:


    SELECT [Measures].[Valor Original da Fatura] ON 0

    FROM [NFE] WHERE {[Emissor].[NUM SEQ EMIS ATUAL].&[1],[Emissor].[NUM SEQ EMIS ATUAL].&[2]}


    Thank you for your time.


    Wednesday, October 13, 2010 2:44 PM