SSAS cube restrict data source by role

Question

I have a data source for a SSAS Multi-dimensional (MOLAP) cube that has a column called RegionId where the value will be 1, 2, 3 or 4

I then have some user roles in SSAS where I want to restrict the data a user will see in a measure group by a single RegionId i.e. 1.

Example:

RegionId = 1, Week = 1, Value = 500 RegionId = 1, Week = 1, Value = 700 RegionId = 2, Week = 1, Value = 300 RegionId = 3, Week = 1, Value = 500

If a Region 1 user were to view this data in the cube and group the data by the Week = 1 then they should see a total of 700 + 500 = 1200 Not the total across all regions.

There is a way to restrict the data they can select in a dimension but if they were to use the time dimension ONLY then they would see values in their measures group that are not related to their specific region.

I believe this sort of thing is called row level security? However I have not found a clear way to do this for a Multi-dimensional cube, only in Tabular.. which I am not using.

Anyone know how this can be achieved? easily using the roles permission settings? Can this be done in the role's cell data tab using some MDX ? if so please can you provide an example of that?

Answer 1

Your cube will return only region related data if you check the visual totals box as explained here: http://easyroles.com/2014/02/visual-totals-in-ssas-security/

Answer 2

Correct. The Region dimension is still conceptually present, but its current member is the "All Region" member -- and Visual Totals for the security on the Region dimension still kicks in for the "All Region" member.

So rows in the measure group will be filtered by the security defined on the Region dimension even though Region isn't explicitly used in the MDX query.