cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

Optimize refresh tme for 40 million rows

Hello community members,

Need help to optimize refresh time for 40 million records and growing. Using the import method from an MS SQL server. Power BI desktop is located on the same server were importing the data. The refresh time is 20 minutes now. Thanks in advance for any help.

 

3 REPLIES 3
Microsoft
Microsoft

Hi @Chapin4u,

 

Since you have 40 million reords in your source table, to optomize refresh time, here are some tips for you reference.

  • Hide or unhide columns in your dataset. Power BI Auto Insights doesn't search hidden columns. So hide duplicate or unnecessary columns and unhide interesting columns.

  • Use a mix of data types such as names, times, dates, and numbers.

  • Avoid (or hide) columns with duplicate information. This takes valuable time away from searching for meaningful patterns. For example, one column with state names spelled out and another column with state name abbreviations.

Regards,

Charlie Liao

@Chapin4u

Another suggestion is to remove as many columns as possible and use lookup tables. As an example, When I first took over our transaction table, the rows had columns with the order date, order year, order month, and order week. I pulled all but the date and transferred everything else to the datedim table. The rows also had item number along with product cat, product lvl2 cat, product endleaf, and sub cat. I pulled those and related to an item table.

You may already be doing this but if not, it can reduce refresh time.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for your suggestions, but if you don't mind me asking, how meany rows did your PBI report had and how much time were you able to improve by doing your suggestions. Thanks for your help.........

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors