Forum Discussion

Santos_Luis's avatar
Santos_Luis
New Member
5 months ago
Solved

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...
Data Protection
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    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.

cengizhanarslan's avatar
cengizhanarslan
Super User
5 months ago

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.