Domain A = SSRS Users Domain B = SSRS and SSAS instances
There are around 5 separate roles to segregate the allowed member set in dimension data. Domain A user(s) cannot be added to SSAS Cube role(s) in Domain B since Domain A is not trusted to Domain B. What is the best way to enable SSAS Dimensional data level security for User(s) from Domain A?
TIA!
The most straightforward solution would be to move the SSAS server to Domain A. That's where the users are. Plus, if it's in Domain A, then Domain B users can connect since Domain A trusts Domain B one way. I would highly recommend this.
If that's not an option, then you might try this. In SSRS, you should be able to set the credentials on the SSAS data source to use DOMAINB\ServiceAccount. Then it should be able to pull SSAS data into SSRS.
To bake role based security into SSAS, I see two options. First, you might just create 5 reports and use SSRS security to control who can see which report. Behind the scenes, each of the 5 identical reports would use a different service account on the SSRS data source to connect to SSAS. Basically each use would be granted access to one of the 5 reports and that report would connect using one of the 5 service accounts to SSAS and role based security in SSAS would show the appropriate data for that one service account.
If that's not acceptable you might consider using dynamic security using CustomData since you can't have Domain A users connecting to SSAS on Domain B.
Typical dynamic security uses one role with an MDX expression and the UserName() MDX function to lookup what the current user can access: http://hccmsbi.blogspot.com/2007/08/implementing-user-specific-security-in.html
Your situation since you can't actually connect to SSAS on Domain B with Domain A users, you could use the CustomData() MDX function instead of the UserName() MDX function. You may have to use an expression based connection string in SSRS. The hardcoded connection string would look like: Data Source=ssasserver.domainb.local;Initial Catalog=YourDB;CustomData=JoeBlow
The one role would only have to have DOMAINB\ServiceAccount as a member. But internally it would use the CustomData connection string property to figure out "who you are" and what dimension members you can see.
As far as Excel, I'm thinking you could setup 5 msmdpump virtual directories and then in IIS grant each Domain A user access to only one. However, I'm not clear on how that would work because I don't know how you would setup IIS to impersonate a Domain B service account when it connects to SSAS. Had you already thought through this?
Let me know if that doesn't make sense. Again, I would recommend moving SSAS to Domain A.