Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Loading Excel workbooks (some static, some new) - performance improvement sought

Hi there,

 

In Power BI Desktop, it's taking 12+ minutes to load 50 Excel workbooks (with the same columns). The workbooks are complete and are unlikely to change, but more will be loaded. I thought of splitting them into two folders ('Complete' and 'New') and then having the 'Complete' folder with the setting "include in report refresh" unticked. Two fact tables then arise. However it seems to me that when the two tables are combined, then it still refreshes them all back to the source. Thus there is a hierarchy of refresh. If the query on the 'top' requests a refresh, then the those queries feeding into that query are forced to refresh. Is that right?

 

In other words, imagine that Query A and Query B grab data from different Excel workbooks, and then Query C grabs data from A and B. If Query C has 'include in report refresh' ticked but Query A doesn't, then it seems that Query A is still refreshed. Whether Query C uses 'reference' or 'duplicate', it stills refreshes the data source.

 

One alternative solution might be to consolidate those 50 workbooks into one or two (total rows exceed 1m) although that will take time and is vulnerable to any changes in them (depends on client review). I'd rather keep them separate for now. Equally running a refresh during this design & build which takes 12+ minutes is painful and inefficient!

 

Has anyone some suggestions on this? 

 

Thanks!

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Just to add to this: In Query A and B, if you turn off Enable Load and Include in Report Refresh for both of them, but leave both on for Query C, then it overrides Query A and B's settings and forcing a refresh back to source for both of them. 

     

    Even if I add Table.Buffer() to the end of Query A and B, it still forces the refresh.

     

     

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      Hi Anonymous 

      Incremental refresh enables very large datasets in the Power BI Premium service with the following benefits:

      • Refreshes are faster - Only data that has changed needs to be refreshed. For example, refresh only the last five days of a ten-year dataset.

      • Refreshes are more reliable - It's no longer necessary to maintain long-running connections to volatile source systems.

      • Resource consumption is reduced - Less data to refresh reduces overall consumption of memory and other resources.

      Best Regards
      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks v-juanli-msft 

         

        I'm aware of Premium and Incremental Refresh - only it is not suitable for my client (price).

         

        My own solution is to consolidate the workbooks manually beforehand and then buy more memory for my PC (increasing to 24GB) - that reduced the time from over 30 minutes to 9 minutes. 

         

        I feel that Power BI doesn't process large Excel files very quickly, but perhaps that's true for all data extraction from Excel....