Dynamic Hierarchy Levels

  • I am relatively new to ssas and am having trouble with something.

    The scenario:

    A cube with a company hierarchy (region, sub-region, country, company)

    Dimension security is applied by filtering the company dimension by linking username to a list of allowable companies.

    Enable Visual Total is switched ON so that you can only see totals at each level of the hierarchy for those companies for which you have access.

    The problem:

    It has been requested that if a user can only see companies for one country (for example) then they should not be able to see the higher levels in the hiearchy (as the totals will be the same). i.e., if you can only see UK companies you should only see the country and company levels of the hierarchy and not the region and sub-region levels.

    Does anyone have any ideas on how this can be accomplished, or even if it can be done? We can manage a solution to work in the reporting layer, but the requirement is this should be handled in the cube to allow for future ad-hoc reporting.

    Please let me know if you need to post further info/clarification or some sort of sample data.

    Thanks,

    Tom

    Life: it twists and turns like a twisty turny thing

  • Does anyone have any ideas about this? Or, even know if this is possible? I need to know by the end of the day if it can be done (not necessarily how, but just if it is possible), so if anyone can help I would be ever so grateful.

    Do I need to post any extra info/data?

    Cheers,

    Tom

    Life: it twists and turns like a twisty turny thing

  • Ideas/things I have tried:

    • Trying to see if setting default member has any effect on the levels of the hierarchy you can see (it doesn't)
    • Implemented multiple perspectives that are identical apart for the company hierarchy they use; each perspective uses a hierarchy that has starts at a lower and lower level.

      [p]* this works up to a point, but i can't see how to restrict a user to only one perspective[/p]

    • HideMemberIf - as far as I can see this is used to create a ragged hierarchy and hides lower members not the top levels of the hierarchy.

    So, in conclusion, hmmm.

    Life: it twists and turns like a twisty turny thing

  • have worked out how to implement this using a hierarchy bridge and multiple hierarchies. see the section on multiple hierarchies in this pdf document for more information.

    happy to discuss this if people are interested in my solution.

    tom

    Life: it twists and turns like a twisty turny thing

  • Hi,

    You can achieve this by implementing Security Module via Assemblies…

    Thanks

    Bhudev

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply