Data Mart vs separate Semantic Models

[harrisfs]

Hello everyone,

We are currently using an ETL query process on each semantic model (loading data from an API connection) from which we create a report. So let's say 10 models with 10 reports and each one refreshes every night.

Would it make more sense to do all of the loading and querying in a Datamart and simply connect each report to the repsective tables in the Data Mart?

How would you say is the most efficient way to do it in this case?

[mohitkumawat · Accepted solution]

Hi,

You're right, using a central Data Mart for your scenario would be much more efficient than running separate ETL queries on each semantic model.
Some points need to consider :-Complexity of Transformation,Data Security,Data Volume.
Overall, using a Data Mart is the recommended approach for your scenario with multiple reports relying on the same API data

[mohitkumawat · Accepted solution]

Hi,

You're right, using a central Data Mart for your scenario would be much more efficient than running separate ETL queries on each semantic model.
Some points need to consider :-Complexity of Transformation,Data Security,Data Volume.
Overall, using a Data Mart is the recommended approach for your scenario with multiple reports relying on the same API data

[harrisfs]

Thanks for the answer! In case you have worked with data marts, what would you say is the most efficient ways to handle measures? Put them in the mart or have them separately?

[v-jialongy-msft · Accepted solution]

Hi @harrisfs 

Best Practices:

Common Measures in Data Mart: For measures common across multiple reports and not subject to frequent changes, pre-calculate these in the data mart. Examples might include total sales, average revenue per user (ARPU), or year-over-year growth.

Report-Specific Measures in Reports: For calculations that are specific to a report or need to be dynamic/interactive (such as filters affecting calculations), define these measures within the report's semantic model.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.