I have a SSAS project (simplified) with 3 dimensions: Employer, Location, Time, Department. I have applied security on the Employer dimension, so that users can only see the employers that they are associated with. But the requirement mandates that when the users browse Location dimension, they only see Locations associated with the employers that they have access to. Employer and location dimensions share many to many relationship, and my attempts to use reference dimension to restrict Locations based on the security imposed on Employer have been futile.
I suspect this is a general issue across multiple business cases. Any idea on how we can inter-dimensional dependency would be highly appreciated.
Thanks Sajan