Sound advice on improving Query Refresh

[Anonymous]

Is there any sound advice on uploading large datasets (5 million rows from SQL server 2016) into Power BI?

It is tough for me to aggregate the data as I need it at the granular level.  I removed my parameters and disabled parallel loading, etc etc but it still is incredibly slow...like hours and hours to load a SQL query in import mode.

Any suggestions are appreciated.

[Anonymous]

Hi @Anonymous ，

Loading large dataset in import mode always takes a lot of time. Do you consider to use DirectQuery mode instead?

Best Regards,

Jay

[CNENFRNL]

First of all, I'm a hardcore fan for PQ given so many fantastic native functions to manipulate dataset; but in practice, I still resort to sql for better perfomance either by query folding or by sql on the db side, i.e. join/aggregate/filter dataset by sql rather than by PQ query.

Frankly speaking, sql is more relaible than PQ query in terms of performance.

[Anonymous]

@CNENFRNL I am trying to aggregate some columns but I need all of them as they interact together.

[HotChilli · Accepted solution]

Correct and hopefully it will be part of your long-term solution otherwise the refresh will take as long as the initial load.

Other things to consider : query folding,

auto date/time settings,

not returning fields from the db that are not required,

using table.buffer if the same table is being loaded repeatedly in query transformations,

what's going on in the db when the load is being run, and so on

[HotChilli]

Incremental refresh?

[Anonymous]

In order for me to set up Incremental refresh I need to load my dataset that I recently changes which is taking hours and hours