Optimize refresh tme for 40 million rows

[Chapin4u]

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.

[v-caliao-msft]

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]

@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.

[Chapin4u]

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.........