Having been working on this for a few days, there seem to be an awful lot of ‘solutions’ on the internet which don’t actually work at all.
I found this one, and having checked it out, it seems to work quite nicely.
Thank you Pieter van Maasdam.
A customer of ours has a security model stored in a database and they wanted to have the security in the cube to be the same, so I came up with te following solution:
Example of the database in which the security is stored:
The DimUser table contains the users and their AD login account. The FactHours contains the hours that they have booked. The security is stored in the many-to-many table FactHoursDimUser, so the data in this table shows which user can see which fact. Go to the “Dimension Usage” tab to set the right relationships between the tables:
Next step is to add a role to the cube and add user groups on the Membership tab.
After that, you go to the dimension data tab, select the user dimension, the loginname attribute and then enter the following MDX expression to the “Allowed member set” section: {STRTOMEMBER(“[User].[Loginname].[” + username+ “]”)}. Also, make sure that “Enable visual totals” is enabled, so the calculations for the totals will only show what the logged in user is allowed to see.
After that, process the cube and go to the browser tab. Select “Switch user” to view the cube data with other credentials to see the results.
Dev Blog 