Modeling with new Data Marts vs Composite model

[K_Francus]

Hi,

I was looking into the new functionality of data marts and watched a lot of video's, but all of them don't answer my modeling problem.

Use case:

We have multiple tables containing data for multiple projects.
Today we load them in a workspace via dataflows, connect them in a dataset and do some first clean up and add the necessary RLS.

In a project specific workspace we use the composite model so the end users can work with a slice of the tables (from the source dataset) and add their own reference files etc...

When I read to the possibilities to query T-SQL within a datamart and having all RLS in 1 place, I'm looking for a solution within data marts.
I also would give the departments their own possibilities to create queries in the data mart with the low-code possibility and this means the data mart has to be in their workspace, but then I cannot foresee giving them only a part of the table since they can change it within the data mart and if I place the data mart in a seperate workspace they cannot use this functionality.

Other possibility that I see is following: 

Create a data mart for each department based on specific views on the source database instead of the full table.
But then I loose the possibility to have all data available for myself and other BI admins, while I would like to only load the data once and re-use as much as possible.

Any ideas or suggestions are higly appreciated.

Thanks!

[AlexisOlson]

You 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.

[K_Francus]

Hi @AlexisOlson 

Thanks for your response.

Your proposal is what I was thinking of --> replacing my master dataset in the current existing composite model with this data mart functionality.

I do keep struggling with the fact, that data marts are also designed to serve "departmental self-service data".

-> In that ideology it could be a good idea to create datamarts for each department, but then I just need to live with the fact that I will have redundant tables over the tenant. 
And then the next question pops up. --> Is it a good idea to load all data in dataflows and then distribute over the data marts, or would it be better/faster to load them directly into the department datamarts.

Thanks again for thinking with me!

[AlexisOlson · Accepted solution]

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.