cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chapin4u Member
Member

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
Moderator v-caliao-msft
Moderator

Re: Optimize refresh tme for 40 million rows

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

kcantor Super Contributor
Super Contributor

Re: Optimize refresh tme for 40 million rows

@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 Datanaut!




Chapin4u Member
Member

Re: Optimize refresh tme for 40 million rows

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
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,899)