locked
Filter cube data with SessionID (Get SessionId with MDX?) RRS feed

  • Question

  • Hi,

    I have a cube which is queried by multiple users at the same time.
    The goal is to filter a given dimension so that each user can see, via the frontend client, what they asked for. Note that a user can ask for more than one report which means that the username alone will not be sufficient to filter the reports.

    So the idea is to filter the dimension using the sessionID. How can I get the sessionID of the current connection at the SSAS server level? Is there an MDX function that can do this? Apart from the sessionid, is there another solution to acheive my goal?

    Regards,
    Didi..er
    --
    Wednesday, November 25, 2009 10:05 AM

Answers

  • Hi,

    This is exactly what I want.

    Actually, my problem is that I don't know in advance the members that are allowed for each users. This is known when a report is asked.

    Here is an example :
    Dimension : Country
    Attribute Hierarchy : CountryName, CityName

    If the user ask for a report for USA, he must see only USA and the cities of USA.

    Idea :
    1) Get the parameter (USA) when the report is asked
    2) At the cube level, use the parameter to set the restriction dynamically

    So, my questions are :
    1) How can I transport my parameter? CUSTOMDATA() in the connection string?
    2) How do I set the filter?

    Thanks,
    Didi..er

    --

    This is not a security issue and you would not use CUSTOMDATA() for this.

    This is just basic querying and report parameterization. If you are using a report tool like Reporting Services you would do the following: http://msdn.microsoft.com/en-us/library/ms156303.aspx
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Saturday, November 28, 2009 3:54 AM
  • It sounds like you may want to look at security roles and restricting access using the Dimension Data tab so that you restrict certain users to only seeing certain members in a given dimension. This will let you assign users a set of one or more members from a dimension that they are allowed to see.

    Is this what you are after?
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Friday, November 27, 2009 1:22 AM
  • "If the user ask for a report for USA, he must see only USA and the cities of USA." seems to me that
    a) it's more of SSRS querying a cube than Excel browsing a cube (i.e. a report parameter that gets its value from a member of a hierarchy)
    b) it sounds like attribute relationship i.e. say 2 levels: country-city, once this attribute relationship is defined and the dimensional security is defined (users1 belong to role1 which have access to country1) the cube browser/Excel should be able to a) filter which country the users can see, and b) list down the cities under that country when the user drilldown.

    Friday, November 27, 2009 2:59 PM

All replies

  • Hi,

     It seems you want to filter the data in report level? So, could you try to create the filter in frontend client? And what’s the frontend client you are using?

     

    Regards,

    Raymond

    Thursday, November 26, 2009 9:50 AM
  • Hi,

    Thanks for your reply.

    I'm using Excel 2003 as frontend client and I can't filter data at client's level.

    I am now studying the case of CUSTOMDATA(). Actually I can send a parameter to the server via the connection string CustomData parameter.

    If you have other ideas, please advise.

    Thanks,
    Didi..er
    --
    Thursday, November 26, 2009 11:11 AM
  • It sounds like you may want to look at security roles and restricting access using the Dimension Data tab so that you restrict certain users to only seeing certain members in a given dimension. This will let you assign users a set of one or more members from a dimension that they are allowed to see.

    Is this what you are after?
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Friday, November 27, 2009 1:22 AM
  • Hi,

    This is exactly what I want.

    Actually, my problem is that I don't know in advance the members that are allowed for each users. This is known when a report is asked.

    Here is an example :
    Dimension : Country
    Attribute Hierarchy : CountryName, CityName

    If the user ask for a report for USA, he must see only USA and the cities of USA.

    Idea :
    1) Get the parameter (USA) when the report is asked
    2) At the cube level, use the parameter to set the restriction dynamically

    So, my questions are :
    1) How can I transport my parameter? CUSTOMDATA() in the connection string?
    2) How do I set the filter?

    Thanks,
    Didi..er

    --
    Friday, November 27, 2009 2:03 PM
  • "If the user ask for a report for USA, he must see only USA and the cities of USA." seems to me that
    a) it's more of SSRS querying a cube than Excel browsing a cube (i.e. a report parameter that gets its value from a member of a hierarchy)
    b) it sounds like attribute relationship i.e. say 2 levels: country-city, once this attribute relationship is defined and the dimensional security is defined (users1 belong to role1 which have access to country1) the cube browser/Excel should be able to a) filter which country the users can see, and b) list down the cities under that country when the user drilldown.

    Friday, November 27, 2009 2:59 PM
  • Hi,

    This is exactly what I want.

    Actually, my problem is that I don't know in advance the members that are allowed for each users. This is known when a report is asked.

    Here is an example :
    Dimension : Country
    Attribute Hierarchy : CountryName, CityName

    If the user ask for a report for USA, he must see only USA and the cities of USA.

    Idea :
    1) Get the parameter (USA) when the report is asked
    2) At the cube level, use the parameter to set the restriction dynamically

    So, my questions are :
    1) How can I transport my parameter? CUSTOMDATA() in the connection string?
    2) How do I set the filter?

    Thanks,
    Didi..er

    --

    This is not a security issue and you would not use CUSTOMDATA() for this.

    This is just basic querying and report parameterization. If you are using a report tool like Reporting Services you would do the following: http://msdn.microsoft.com/en-us/library/ms156303.aspx
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Saturday, November 28, 2009 3:54 AM