Improve refresh times (Avoid timeout error)

[Anonymous]

Hi everyone,

For the past couple weeks we are running into a timeout error for a specific dataset in our premium workspace. It does not happen everyday, but the frequency of the error is increasing.

It's a datset with incremental refresh that loads a lot of tables (most of them in dataflows, a couple by querying athena), it stores data for the past 8 quarters and refreshes the last 2. 

I think we may have a bit of performance gain if we avoid loading some unused columns, but are there any other things we can do to avoid reaching the timeout error (usually token expired)?

Thanks!

[ppm1]

Removing unneeded columns is always a good step. You could also consider moving to monthly partitions instead of quarter. The other thing would be to try to optimize your M code, if you are doing any complex transformations, merges, etc. If so, you can share your M code here and get suggested optimizations from the community.

Pat

[cassidy]

It can be reallly challenging to understand what portion of a Dataset refresh is causing slowness.  I've relied heavliy on the method described in this blog post to analyze my refresh and pinpoint bottlenecks.

https://dax.tips/2021/02/15/visualise-your-power-bi-refresh/

Also, using the "Best Practice Analyzer" in Tabular Editor is incredibly helpful at guiding you (and helping you fix) inefficiencies in your datasets.

https://www.sqlbi.com/tools/tabular-editor/

I've been able to reduce model sizes by half (!) and reduce refresh times by more than half (!) just by using the two tools above.

[Anonymous]

Thanks a lot! I will review this and see what I find 🙂