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

Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)