Forum Discussion
Modeling with new Data Marts vs Composite model
You have multiple competing values and it's always a balancing act. The best approach is dependent on your specific situation.
The more the departments have self-service control over data, the more likely you are to have redundant and/or fragmented data. The more you centralize shared data, the more you have to maintain and the less control each department has.
My rule of thumb is that if a data table is needed for multiple reports and/or multiple departments, then it should be maintained in a single location (e.g. a dataflow or datamart). If you need to load that table into multiple workspaces or other datamarts, that's OK. If you connect to that table via DirectQuery, then using it in multiple places doesn't even require storing more than one copy of the data.
Super UserYou can build reports and datasets off of a dataset within another workspace.
I think the solution would be to build a complete datamart (containing all the stuff that more than one department will need) in a workspace that you control, add the RLS on the tables in the datamart, and then have the departments build their stuff in their own workspaces by connecting to the dataset that's automatically created from the datamart. The departments won't be able to modify the datamart tables but they can build composite models including them. If they want stuff added to the datamart then it needs to go through you since stuff in the datamart needs to serve all the departments you support. The RLS in the datamart will ensure that they can only connect to the subsets of the data that you determine.