Avoiding long data load times for static data on Refresh All

[troyhimes]

I require 3 years worth of data (600,000 rows) from a IBM db2 tableview.  Each day an additional 100-200 lines are added to the database.  In order to speed up data load processing times I split the data betwen two queries...the first is a query of an excel file of exported years 2020 through 2021 data, connection only, "Refresh this connection on Refresh All" box unchecked.  The second is a query of the tableview which provides the dynamic 2022 data.  I then merge the 2 data sets and export to data model.

I expected this to be faster than a query of all 3 years worth of data from the tableview but am experiencing very similar data load times between the two methods.  Am I approaching this the right way?

Thanks

[v-angzheng-msft]

Hi, @troyhimes 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat · Accepted solution]

Unfortunately, whenever you refresh, the append step is triggering refresh of your static data too. If not already, I would look into incremental refresh on your original IBM db2 query (no Excel).

Pat

[troyhimes]

Hi Pat, thanks for the info regarding the append process.  What do you mean by 'incremental refresh'?

Instead of appending the two queries is there a downside to 'linking' the tables in the data model through a common date table?

[AlexisOlson]

Incremental refresh is a special kind of refresh you can set up in the Service.

See these resources:
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only