Forum Discussion
Agregate table data on app server or inside power bi
We have an ERP system and each module has many data tables, reference tables with codes and lookup values etc.
I have been creating SQL database views for users to access (via XL) entire raw data sets of infrastructure assets combining asset details, financial data, etc. SQL database views can include between 12 to 20 different tables and other views. For data slicing in XL it is very convenient and practical for users.
My question is from a PowerBI dashboard perspective would it be better design and 'optimisation' to have star models in Power BI, or accessing the aggregated database views from the ERP database server or create semantic models on the PowerBi server or ...
Any recommendation or pointer to best practice guidelines would be much appreciated.
Thank you
Best practice is to prepare data in SQL (clean joins, standardize fields) but build a star schema in Power BI for reporting.
Avoid using one large denormalized ERP view directly in Power BI, as it hurts performance and model usability.
Instead, separate your model into fact tables (transactions) and dimension tables (lookups like asset, date, location).
Keep heavy transformations and data shaping in SQL, but define business logic and measures in DAX.
Only pre-aggregate in SQL when there is a proven performance need; otherwise, let the Power BI semantic model handle analytics.
2 Replies
Thank you !! Much appreciated.
Best practice is to prepare data in SQL (clean joins, standardize fields) but build a star schema in Power BI for reporting.
Avoid using one large denormalized ERP view directly in Power BI, as it hurts performance and model usability.
Instead, separate your model into fact tables (transactions) and dimension tables (lookups like asset, date, location).
Keep heavy transformations and data shaping in SQL, but define business logic and measures in DAX.
Only pre-aggregate in SQL when there is a proven performance need; otherwise, let the Power BI semantic model handle analytics.
