Can we combine multiple simple models, in to purpose built, derived models?

[tkboisvert]

Consider the following situation:

Inside an organization, each Business Unit has requested specific, separate models. After some time, some high level members over Finance and Sales have requested a model containing multiple data sets. This is fine but now we have to maintain three models and duplicate both sets of measures.

• Model Finance can only be seen by Finance
• Model Sales can only be seen by Sales
• Model C can only be seen by Execs over both units and has all the logic and measures from Finance and Sales.

Assuming this was an issue across the whole organization, please can you clarify which of the following is best practice:

• Many, many, models containing the same data, measures and relationships. Just mashed up in different ways
• A single, massive model with complex RLS.

From my experience, a hybrid approach is preferred where 60% of the org can be fed by a single Model, and the other 40% is fed by purpose build models. However, this seems to go against my perceived BI Ideology, ONE VERSION OF TRUTH. Will we ever be able to, (or can we already?), combine multiple simple models, in to purpose built, derived models?

Is there another technology?

Is the answer to use flattened, imported SSAS Tabular models?

It seems like an easy answer.

Semi-Potential solutions:

• Cross report Drill through
  • No good because you can't do cross model comparisons.
• One big model
  • Sluggish, difficult to measure change impact across all BUs.
• Many, many permutations of all BUs
  • Just no.

[tkboisvert]

Inside an organization, each Business Unit has requested specific, separate models. After some time, some high level members over Finance and Sales have requested a model containing multiple data sets. This is fine but now we have to maintain three models and duplicate both sets of measures.

• Model Finance can only be seen by Finance
• Model Sales can only be seen by Sales
• Model C can only be seen by Execs over both units and has all the logic and measures from Finance and Sales.

Assuming this was an issue across the whole organization, please can you clarify which of the following is best practice:

• Many, many, models containing the same data, measures and relationships. Just mashed up in different ways
• A single, massive model with complex RLS.

From my experience, a hybrid approach is preferred where 60% of the org can be fed by a single Model, and the other 40% is fed by purpose build models. However, this seems to go against my perceived BI Ideology, ONE VERSION OF TRUTH. Will we ever be able to, (or can we already?), combine multiple simple models, in to purpose built, derived models?

Is there another technology?

Is the answer to use flattened, imported SSAS Tabular models?

It seems like an easy answer.

Semi-Potential solutions:

• Cross report Drill through
  • No good because you can't do cross model comparisons.
• One big model
  • Sluggish, difficult to measure change impact across all BUs.
• Many, many permutations of all BUs
  • Just no.

[collinq]

Hi @tkboisvert ,

Why not build one (or maybe two datasets) - just the dataset.  And, publish that dataset as a dataset with no permissions to it by anybody but the report writer folks.  It might be a big one as you mention, but it can refresh at night so maybe that isn't a big deal.  This also allows you to have one single place to maintain and one set of measures to build. 

Then, you can write reports based on the dataset. You can have each report for its own group so that when you publish it and just give permissions to that group (finance reports for finance, sales for sales, etc.).  And, for Model C as you mention, you give Executives access to both OR, preferably, you build a dashboard for Executives that use both.

This approach as I mention above gives you one place to maintain, one source of the truth and the ability to split things while not having to do RLS on each and every field/visual/etc.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[tkboisvert]

As far as I'm aware, there is no way to combine models like you say ( model C ) with the exception of Tabular Models.

I know that you can import mutiple Tabular models from SSAS but that "Flattens" each imported model, which defeats the point of having a nice star schema with purpose built measures.

[v-lili6-msft]

hi  @tkboisvert 

Although you can import mutiple Tabular models from SSAS, it will only import data, you still need to do modeling in power bi desktop.

I would suggest you put the different model into different workspace to manage them.

combine them into one big model, you could use RLS in it, but it will be a massive model with complex RLS.

Regards,

Lin