I am quite new to SSAS, so please forgive me if there is an obvious answer to my question - I have done a lot of research today and cannot find the answer myself.
I am trying to apply Dimension Data security (in a SSAS DB role that I have created) to my SSAS 2012 cube. Essentially, what I am trying to do is that users assigned to the role that I have created, should not be able to see any data for a specific dimension member.
So, when I edit the cube in SSDT (SQL Server Data Tools or Visual Studio with BI add-on), I go to Roles --> open the role --> go to Dimension Data tab --> select the dimension in the drop-down --> Un-tick the dimension member(s) that the users in the role should not be allowed to see).
Once this is set up, I test the role by deploying the cube, then in SSDT I open the cube --> go to the 'Browser' tab --> click on 'Change User' --> select the role that I have created.
The result of my test is this:
If I select the dimension which I have restricted, and any of the measures, the data is displayed correctly, i.e. only the dimension members which I allowed are displayed with the values for the measure(s) displayed correctly for each dimension member. However, if I do not select the restricted dimension (e.g. select only one of the measures on its own), the total for the measure is incorrect, i.e. it does not appear to be excluding the dimension member(s) hidden for the role.
What am I missing here?
I have seen some posts / blogs online talking about 'Dynamic SSAS security' which seems to involve mapping individual Windows logons to the fact rows that they are allowed to see (which involves creating additional, hidden tables in the data source), but I can't get my head around why I would need to do that if the SSAS role should do that as well? I do not need to define security at the user level - users are grouped into active directory groups and so I want to map a role to the relevant AD group and that should work too, shouldn't it?
