Cross-dimension Security Dilemma

Here are my simplified security requirements:

1. The cube has a Customer dimension (some 150,000 members) and Account dimension (over 1 mil members). A customer can have one or more accounts.

2. Complex security rules in the operational database dictate which customers the user can view based on her identity and application role membership. Therefore, I need to implement dynamic security to retrieve the allowed set of the customer members from the source database.

3. Instead of retrieving the allowed members for the other dimensions, I would like to propagate the security filter of the Customer dimension, e.g. to the Account dimension, so the user could only see the accounts of the allowed customers.

What’s the recommended approach to implement this? I tried EXISTS on the Account dimension but I still get all accounts.

EXISTS ([Customer].[Customer].[Customer].Members,

[Account].[ Account].[ Account].Members, 'Accounts')

However, if I use this expression in an MDX query, I get indeed the allowed accounts only. It seems like the security filter is applied before the Customer allowed set filter is constructed.

When expressions for dimension security are evaluated, they are evaluated in the context which doesn't have any restrictions. There is a very detailed blog which describes every step during cube initialization, including the context for dimension security - http://sqljunkies.com/WebLog/mosha/archive/2005/12/31/cube_init.aspx

You will have to do Exists with the allowed set of Accounts - sorry that's your only option here.

|||So sad to hear this. Essentially, with dynamic security this wil require several calls to security layer to get the allowed set filter. It could have been nice if there was [Hierarchy].AllowedMembers/[Hierarchy].DeniedMembers to address such scenarios. Even better, it could have been nice if there was an option to apply a global allowed set filter (similar to setting a filter in the cube browser) which will slice the cube across all dimensions.|||I think you should log this suggestion on connect. In the meantime, you may need to implement some caching inside your sprocs if perf of multiple calls becomes a problem.