We have one SSAS Tabular model with all our facts, a security table and a dim_organisation.
The dim_oganisation is THE table that is joined to all the fact tables.
We have created dynamic RLS for specific users on the dim_organisation using the security table through a lookup function..
So far so good.
Now we have users that may see all the HR data of the whole organisation, but only the financial data of their own department.
As the dim_organisation is joined to ALL fact_tables. Once the RLS is implemented not only is the financial data filtered, but also the HR data. And this is not what we want.
Now we can, of course, create a seperate HR SSAS model and change the dynamic RLS for this specific group. But this means duplicating dimensions within the seperate models. This leaves room for errors in managing the models: the same dimensions don't have to be the same between the models. Which in turn can lead to all sorts of questions from users.
We could also bypass the SSAS model an create a data model for the HR datamart star in PowerBi and add separate RLS to this dataset by excluding the HR people. This again creates a seperate entity which we have to maintain and increases the complexity of our solution.
Is there any other way to do this?
We cannot be the only ones having these kinds of challenges.
Or are the only solutions the ones specified earlier?
Thanks!
