Incremental Refresh taking much more time

[rajulshah]

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 

[v-shex-msft · Accepted 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

[mwegener]

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.

[amitchandak]

@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

[amitchandak]

@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

[rajulshah]

@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?

[amitchandak]

@rajulshah , but will the modified date will last 4 years? it should like 8-10 days. This setting as good as initial load 

[rajulshah]

@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?