I could not figure out why the following thing happens and how to implement it correctly :-).
I created a simple cube that is based a on a star schema with one fact-table and four dimensions. What I then wanted to do is to allow access only to a specific sales organisations (which is one of the dimensions) using a role definition. After defining the allowed sales organisation in the "dimension data" tab I thought that all measure data related to the denied sales organsations won't be visible anymore. But they are still there... From my Excel client I'm not able to see the other sales organisations but to retrieve just the data related to my allowed sales organisation I still have to filter manually.
In my case these measure data are sales quota numbers. So when accessing the cube in Excel I still see the accumlated number of all sales quota data. After setting a filter to the allowed sales organisation the figure changes to the correct value.
But am I doing wrong?
Sounds like you need to [enable visual totals] when configuring dimension security in the role definition.
Here's a nice blog post with more detail... http://blogs.microsoft.co.il/blogs/barbaro/archive/2008/02/06/visual-totals-in-mdx-and-role-security.aspx