In an SSAS database I am using the dimensions "agencies" and "campaigns". There are about ten agencies and about 2000-5000 campaigns per agency. The requirement is to permission users on agency level. When a user is in a role that is permissioned for agency 1 only, then he must not be able to view any other campaigns than the campaigns of agency 1.
Of course this could be done by simply giving the role access to campaign members that belong to agency 1. A very long MDX statement would be required for that. However, I guess the scenario is quiet common and I spotted some approaches but I don't know which is a good practice:
- Using factless facts between agencies and campaigns (it seems unnecessary, since there is an agengyID in the campaign table)
- Using an MDX statement in the campaign permissions that selects the campaigns through the parent agency members
- Set up a hierarchy between agency and campaign (actually I tried that and it seems a logical thing to do, but the hierarchies seem not to deal with permissioning at all)
- Something completely different maybe?
You can set permissions on Dimension Hierarchies by opening a role in Visual Studio and going to the Dimension Data tab. There will be a drop-down to choose the dimension, and then another to choose the hierarchy, and then you can specify what data that role can see.