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?
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/