Semantic model architecture best practise

[IanCockcroft]

Hi guys,

i   joined a team that is very SQL heavy.

Basically, they have a SQL query that runs in a stored procedure building a table in the format or structure needed.

PowerBi then uses this table as its source

if there are 50 reports, a table is built for each one. Using   the same data presented differently.

I think this was just because DAX skills were limited at the time.

Another reason was because the gateway was failing refreshing 50 models off the same source.

My thought is to have 1 semantic model with the data and all the reports using that model and any presentation changes done using DAX.

are there any PROS and CONS

PROS

only 1 model to refresh taking pressure off gateway

less technical debt.

CONS

performance hampered by 50 reports/users accessing same semantic model at once?

any  other suggestions?

cheers

Ian

[Sahir_Maharaj]

Hello @IanCockcroft.

Here's my view:

Pros: Having one semantic model means less maintenance, fewer refresh failures, and a single source of truth for all reports. It also takes the load off the gateway since only one model needs to refresh, improving reliability (also using DAX instead of SQL for data shaping gives more flexibility)

Cons: With many users and reports pulling data from the same model, performance can slow down, especially during heavy usage. Since the team is SQL-heavy, learning DAX might be a challenge, requiring training. In some cases, having a few smaller models for specific reports could help keep things running smoothly.

If performance becomes an issue with a single model, consider one core model for common data.

[MattiaFratello]

Hi @IanCockcroft , if your tables are pre-built in SQL and the only thing you need to do is a simple select, this should not overload the gateway. However, trying to aggregate is not a bad idea, especially if in every report you need to rebuild the same DAX etc...

It really depends on how complex the semantic model will be as well as how big it will be.

Remember that with Power BI Pro you have 1GB for Semantic Model, 10GB with Power BI Premium.