I know this must have been run into from others.
I have implemented dynamic dimensional level security in the cube using an account dimension that is hidden from the user. This works fine but what I also would like to do is to hide the members on the other dimensions that the user has no relevant information to. For example:
A salesman is assigned to the Eastern region within that region is a collection of stores. If we place security on the stores (account) dimension the user will ONLY see the stores they have access to, this is correct. What is missing is if you have a geography dim when they can drill down to the stores this salesman has no need to see or sift through all of the other stores in other areas of the country. I would like to filter ALL dimensions to show only the data that is relevant to this user again using dynamic security. I think the correct way is to apply dynamic security on ALL of the dimensions but this means that a fact table noting permissions to these members in the dim would have to be defined.
Has anybody seen a need for this or have tackled this problem in the past?