Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have a dataset with almost 50 tables and still adding, having SQL Server as the data source.
We have configured Incremental Refresh to the tables which have millions of data. Right now, we have configured Incremental Refresh as below:
I have configured '4 years' for both, "Store rows" and "Refresh rows", because the data of any time can be modified. So, it would only fetch only those where Modified Date has been changed. As we need to refresh rows for 4 years, it is taking almost half an hour for each scheduled refresh to refresh the dataset.
Does anybody have any workaround to reduce the refresh time of the dataset?
Please let me know if you need further clarification.
Thanks in advance.
Can you guys please help? @Greg_Deckler @parry2k @mwegener @v-lionel-msft
Solved! Go to Solution.
Hi @rajulshah,
It seems like your refresh still traces a long time period, they should affect the performance of refresh.
In my opinion, I'd like to suggest reduce the date period that power bi trace.
Any other date fields stored in your tables that marked the 'change date' instead of record 'create date'? If this is a case, you can choose to set 'incremental refresh' in those fields with short date period. They should obviously improve refresh performance.
Notice: If this field does not exist in your tables, you can also consider adding a redundancy 'updated date' field that updates every time the record changes.
Regards,
Xiaoxin Sheng
Hi @rajulshah,
It seems like your refresh still traces a long time period, they should affect the performance of refresh.
In my opinion, I'd like to suggest reduce the date period that power bi trace.
Any other date fields stored in your tables that marked the 'change date' instead of record 'create date'? If this is a case, you can choose to set 'incremental refresh' in those fields with short date period. They should obviously improve refresh performance.
Notice: If this field does not exist in your tables, you can also consider adding a redundancy 'updated date' field that updates every time the record changes.
Regards,
Xiaoxin Sheng
Hi @rajulshah ,
Referring to this post.
https://community.powerbi.com/t5/Service/Working-of-Incremental-Refresh-when-detect-data-changes-is/...
Would I try to change the refesh period from 4 years to 14,650 days so that each period (days) is checked for changes.
Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
@rajulshah , What I was Saying if you use created date then you need refresh 4 year.
My thinking is that with modified date you do need to update data for 4 year. If read the screen correctly
@rajulshah ,This is what my idea is
You are taking 4 year data and then 4 year of refresh rows in last (based on modfied date) that is as good as Inital. If it created date, this can be right approch as you do not know what is getting modified
@amitchandak , Well, in incremental refresh we cannot configure both refresh rows and detect data changes on 1 column. Do you have any ideas this can be handled in different way?
@rajulshah , but will the modified date will last 4 years? it should like 8-10 days. This setting as good as initial load
@amitchandak ,
I understand that it would only refresh those rows which has Modified Date changed. So can you please explain why is it as good as the initial load?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |