locked
MDX query issues RRS feed

  • Question

  • Hello,

    I am very new to MDX and I need some help from you pros...

    I have a working SSAS cube. I implemented data level security on this cube by using 2 tables that join to the dimension tables. One is called users (list of all users) and assignments (shows which data the user has access to). The 3 levels of security are Country, Agency and Media Type. So, for example, if we have a user named John Doe we can assign him all of the Agencies and Media Types in the USA. So John Doe should only be able to see the country USA.

    Let me try to explain my issue...

    We are using excel and Performance point to access the cube. When we open the reports, using our John Doe example, we can see all of the countries, BUT we can only see data for the USA. Is there a property or something that I can change that will prevent this from happening? I need John Doe to only see countries that he has access to, and not see all countries but only see data in the countries that he has access to.

    Is there a way to alter this MDX query to filter out data that he does not have access to? alternatively, is there a way to add a filter that only shows measures that are greater than 0? This may solve the issue.

    NonEmpty(
    [Dim Local Drilldown Cube].[Fact Key].Members,
    (
    STRTOMEMBER("[Security Users].[User Name].[" + UserName()   + "]"),
    [Measures].[CUBE SECURITY Count]
    ))

    Thank you all in advance and please let me know if I was clear enough.

    Dave


    Dave SQL Developer


    • Edited by DaveDB Thursday, August 2, 2012 6:52 PM
    Thursday, August 2, 2012 6:48 PM