cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
donaldm
Helper III
Helper III

Thoughts on Best Practice to load a large dataset?

I want to ask if someone could share their best practices on loading / converting large csv files into Power BI to reduce refresh times.

 

I have a 20mb monthly csv file and would like to append each monthly file into one table to perform time intelligence calculations.  First, I used Power Query to append each csv into a large file.  The refresh time is very long with only 2 months of data (20+ minutes)

 

On second approach, I used python to merge the 2 csv files outside of Power Query (using Juypter Notebook), and the size of the csv file with 2 months of data is 40mb. The refresh time on the Power BI is also very long (20+ minutes).

 

What approach should I use to improve refresh time? Should this data sit in a database (eg. Dremio or SQL server or MS Access)?

1 ACCEPTED SOLUTION
donaldm
Helper III
Helper III

Thank you for everyone's suggestions. Your responses allowed me to learn when to use data flow in the future!

 

I solved my problem: the problem was the csv file was stored on the network drive and it was throttling the refresh.  The solution now is to have store the file on OneDrive (or local drive) so I can refresh the file locally!

 

The change reduced refresh time from 20+ minutes to 10 seconds! 🙂

View solution in original post

4 REPLIES 4
donaldm
Helper III
Helper III

Thank you for everyone's suggestions. Your responses allowed me to learn when to use data flow in the future!

 

I solved my problem: the problem was the csv file was stored on the network drive and it was throttling the refresh.  The solution now is to have store the file on OneDrive (or local drive) so I can refresh the file locally!

 

The change reduced refresh time from 20+ minutes to 10 seconds! 🙂

Greg_Deckler
Super User
Super User

@donaldm Are you doing a lot of heavy transformation? I have loaded 700MB CSV files in 2 minutes. Try taking out your default change type step if you have one. 

 

And yes, always better with an actual database backend like SQL.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

FrankPreusker
Advocate III
Advocate III

I have good experience utilizing Power BI Dataflows to inject and append several hundert (chunky) Excel files. This decouples the first step (merging) from the second one (data load to PBI).

Loading even big dataflows into PBI is a seamless experience in PBI Service.

speedramps
Solution Sage
Solution Sage

Hi donaldm

 

If you must use CSV in Power Query then try remove any unwanted rows or columns as soon as possible in your query before you do any other transformations.

 

SQL will be much quicker that CSV because of "Query Folding".

 

Power BI is not fast and will have to do all the CSV heavy lifting and shifting.

 

Whereas if you use SQL then Power BI will "delgate" the heavy lifting and shifting back to the SQL server which is super fast.

 

Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors