Forum Discussion
Best practice for grouping data performance
Hi Sofinobi ,
Grouping/aggregating data to just Date, Product, and Product Category before sending it to your data model should give you significant performance improvements over just using the data in its stored granularity.
In terms of how/where to do this I would recommend doing it as far away from your data model as possible, i.e. in order of preference:
-1- Have a new table added to your SQL DB with this aggregated data, or;
-2- Write an SQL view that outputs this aggregated data, or;
-3- Do it in Power Query while maintaining query folding
For maximum efficiency, you should also only include Product Code/ID and Product Category Code/ID in this table. The text descriptions should be held in dimension tables that you then use to build a star schema model with.
Pete
Hi Sofinobi ,
Grouping/aggregating data to just Date, Product, and Product Category before sending it to your data model should give you significant performance improvements over just using the data in its stored granularity.
In terms of how/where to do this I would recommend doing it as far away from your data model as possible, i.e. in order of preference:
-1- Have a new table added to your SQL DB with this aggregated data, or;
-2- Write an SQL view that outputs this aggregated data, or;
-3- Do it in Power Query while maintaining query folding
For maximum efficiency, you should also only include Product Code/ID and Product Category Code/ID in this table. The text descriptions should be held in dimension tables that you then use to build a star schema model with.
Pete
