Forum Discussion
Incremental refresh with polling expression
Go with the timestamp that has the most even distribution (likely the created date). Create a large enough "hot" window to cover most of the jitter (modifications happening soon after creation). For example make your "hot" partitions the last three months instead of the last month. Then use the Modified date to catch any entries that fall outside the "hot" window and manually refresh their partitions if needed. Plan on doing a full refresh (or a sequential manual refresh of all partitions) every now and then.
No, based on what you've said the problem is with the performance of the SQL queries themselves and nothing to do with Power BI. You need to get a DBA or someone familiar with SQL performance tuning to help you here.
Based on what you told me in the discussion on your other thread, no, I don't think this will help much - what you need to do is tune the underlying data source so that the queries generated for each partition are faster.
Thanks a lot cpwebb
Currently, we have a 6-month dataset, including a large table with over 2 million rows. The entire dataset is refreshed in the Power BI service every 30 minutes, taking maximum 2-3 minutes.
For the other dataset (3 years), which has refreshing issues, I partitioned the large table and each partition contains a maximum of 700k rows. but, each of these partitions takes not less than 2 minutes to refresh, which doesn't make sense.
Could this be because the Power BI service allocates more resources than PowerShell in Azure Runbooks?
Thanks again.
These runtimes and row counts do not warrant incremental refresh. Your partitions should hold about half as many rows as you can fetch without hitting either a data source timeout or the 5hr limit for partition refresh.
Thank you cpwebb and lbendlin.
I asked the team to optimize the SQL queries, but they didn't come up with any solutions.
The problem was with refreshing our largest table, as I kept facing memory capacity errors.
I tried removing all the calculated columns from the table, which led to a successful refresh. (There were over 40 calculated columns.)
In an attempt to pinpoint the problematic columns, I removed 7 columns with seemingly complex queries, and again, the refresh was successful.
But, it appears that these specific columns might not be the root cause, as I kept them and removed the rest, and the refresh still succeeded. It seems like the table has a memory threshold that we shouldn't exceed!!
Is there a way to determine exactly which columns are causing the issue? Unfortunately, we can't remove them from the table.
Thanks again.
No, based on what you've said the problem is with the performance of the SQL queries themselves and nothing to do with Power BI. You need to get a DBA or someone familiar with SQL performance tuning to help you here.
