Forum Discussion
Slow refresh time in Power BI Service
Good day,
My Power BI reports on Power BI Service takes much longer to refresh than on Power BI Desktop. I read a previous forum that suggested various solutions:
1) Utilizing a DirectQuery mode rather than an Import mode - in my case, this will not work as the database used for the reports is not setup to support the DirectQuery mode.
2) Utilizing an incremental refresh - most of my reports require all of the data to be refreshed.
3) Upgrading current plan to Premium - the cost of this is too much.
What other suggestions could be tested and seen an improvement in the refresh time?
Thank you.
3 Replies
Hello ap360 ,
Have you tried to reduce your applied steps in your Power Query? it also effects refresh time, espically if you haven't followed query folding issue(if your datasource is support query folding). Also, if you can do all of the requirement of ETL process wihtin ETL tool and store summarize table for example in SQL. that means you don't neccesariy to transform in Power Query which means faster refresh time.Kind Regards,
Gรถkberk Uzuntaล
๐ If this post helps, then please consider Accepting it as a solution and giving Kudos โ it helps other members find answers faster!
๐ Stay Connected:
๐ Medium |
๐บ YouTube |
๐ผ LinkedIn |
๐ท Instagram |
๐ฆ X |
๐ฝ Reddit |
๐ Website |
๐ต TikTok |HI ap360 ,
In addition to what uzuntasgokberk you may want to try using a Dataflow which can be updated in the Service and then use the Dataflow output as your input for your report.
Hi ap360
To improve Power BI Service refresh times without relying on DirectQuery, incremental refresh, or upgrading to Premium, consider the following alternatives:
Optimization Suggestions:
Optimize Queries:
- Ensure your data source queries are efficient. Use indexed columns and avoid complex transformations or unnecessary data loads.
- Utilize query folding where possible, as it pushes transformations to the data source for faster processing.
Reduce Dataset Size:
- Remove unnecessary columns and rows from your dataset.
- Filter out historical data if it's not actively used in reports.
Optimize Power BI Model:
- Simplify DAX calculations or precompute them if feasible.
- Use aggregations or summarized tables for large datasets.
- Avoid excessive relationships or high-cardinality columns.
Refresh Schedule Optimization:
- Stagger refresh schedules for reports to reduce competition for shared resources.
- Avoid refreshing during peak usage times.
Partitioning Data (for large datasets):
- Manually create smaller partitions to limit the amount of data processed in each refresh.
Adjust Power BI Service Settings:
- Disable "auto date/time" for your dataset, as it can create unnecessary hidden tables.
- Enable parallel loading of tables, if applicable.
Improve Data Source Connectivity:
- Optimize network speed between Power BI Service and your data source.
- Use a dedicated gateway for better connectivity and performance.
Monitoring and Troubleshooting:
- Monitor refresh logs in Power BI Service to identify bottlenecks.
- Check and optimize the performance of your data gateway if you're using one.
By addressing these areas, you can potentially improve refresh times without resorting to costly upgrades or architectural changes.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" ๐
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
