Best practice for grouping data performance

[Sofinobi]

hello all,

please i'm looking for advices about the best practices to group data
so, i have a sales table with more than 6 millions rows, that store all the sales operations by (date time, seller, costomer, product, product category , ....)
- in my report i'll need only the date (not  the time), the product and the product category
my question is : if i group the data by these 3 columns, the report will be more performant?
if yes, it's best to groupe in SQL server, write sql query in power bi (when getting data), grouping data in power query or grouping data with DAX?

thank you  for your advices 

[BA_Pete · Accepted solution]

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

[Sofinobi]

@BA_Pete  thank you so much for your answer, thats help me alot

thanks again