SSAS roles - OR rather than AND

Question

we are using SSAS 2008. We have a data related permissions issue. We are successfully able to apply SSAS roles to an incoming user's (Active Directory [AD]) context. i.e. the results returned are based on the user's SSAS role and limit the data returned from a dimension based on the role.

As far as I am aware, you can apply multiple roles to a user so that dimensions take the relevant role and limits it's results - however this condition is applied as an intersection (i.e. an AND) - is it able to be applied as a union (i.e. an OR)

There are a couple of things to note.
- we are accessing our cube via Excel, so relying on the above intelligence within some MDX is not necessarily achievable because the user can query on any dimension (some may be limited by role while others not)
- we have toyed with the idea of having two cubes coupled with two different userIds (in different AD groups) per user; the user would extract data from Excel depending on the data they would like to see (and hence the cube they would be querying); this is messy because we would like the users results all wrapped up in one resultset rather than two separate ones

Has anyone experienced and/or a resolution to the above - is it possible - is there an alternative ?

Answer 1

You can add the Roles property to a SSAS connection string that hold a comma-delimited list of database roles to be evaluated. Only the roles applied in that list will then be applied by the server.

Data Source=localhost;Initial Catalog=MySSASDb;Roles=RoleA,RoleB

Answer 2

If you need a OR condition on 2 dimensions, you can add another dimension with 2 levels being the same level of the 2 initial dimension:

For instance, you want to limit the role to [Country].[France] or [Currency].[USD], then you can add a [CountryThenCurrency] dimension and allow the 2 following paths: [CountryThenCurrency].[France].[*] and [CountryThenCurrency].[*].[USD]

In other words: add dimensions in order to be able all your OR conditions in a single dimension.

Answer 3

I have been searching for a solution for this problem and found a great article of Chris Webb.

http://cwebbbi.wordpress.com/2011/12/22/replacing-cell-security-with-dimension-security/

The idea is to create a junk dimension named DIM_Security and put all distinct combination of both dimensions in there. Now via an MDX expression you get the UNION of all Members having either dimension1 = 'allowedvalue' or dimension2 = 'allowedvalue', thus you have OR permissions.

Moreover Webb explains how to do this with dynamic security.

Not copying all the MDX as the original post is several pages long with many screenhots and linked many times all over the web.