incremental refresh effectiveness

[scabral]

Hi,

After setting up incremental refresh in Power BI for my data source, i don't beleive it really helps with performance, unless i'm totally misunderstanding the functionality or my business case does not take advantage of the incremental refresh benefits as laid out by Microsoft.

i have a claims table that stores claim information such as claim id, claim create date, claim reason, claim status, etc...

Claims can go back in our system to the 1970's, so i setup incremental refresh to use the Claim Create Date as my Filter Date going back 50 years to caputure all the historical data.  I also have a Last Update Date that gets updated everytime something changes with the claim, so i can also use the 'detect data changes' option using the Last Update Date to make the refresh even more grainular.

Now this is where the problem lies with our system.  Sometimes, claims adjusters can go back years and update a claim, maybe even a claim from the 70s or 80s.  I would want these changes to be reflected in my power bi model.  The issue i see is that in order for this to work using the Claim Create Date, i will have to setup the filter to go back 50 years both historically and for the changes, otherwise even if the last update date changes it will not go back unless i set the filter to go back 50 years after every refresh in order to pickup the changed records for that partition.

Now to me that defeats the purpose of incremental refresh because it is going to be running queries for every partition for 50 years with every refresh, which will take almost as long as the original load.  I know only the 'changed' data will be loaded, but all the queries will still have to run every time i refresh the dataset for all 50 years.  

So to me i'm not sure if this is beneficial or not?  I am not understanding how the incremental refresh works in my case?

thanks

Scott

[amitchandak]

@scabral , which date is driving increamental. I am hoping it is update date ?

[scabral]

hi amitchandak,

i was using the Claim Create Date (the date the row gets initially inserted into the table) as the incremental filter date.  According to the Power BI Incremental Refresh documentation, this date should not be a date that gets updated:

"The filter on the date column is used to dynamically partition the data into ranges in the Power BI service. Incremental refresh isn't designed to support cases where the filtered date column is updated in the source system. An update is interpreted as an insertion and a deletion, not an actual update. "

So i use the Claim Create Date as the filter date and then i use the Last Update Date as the 'Detect Data Changes' date to only bring in the rows that have changed.

Is this not the correct way to set it up?  Should i be using the Last Update Date as the incrmental filter date instead?  I also thought that you should not use the same date for the incremental refresh date and the detect data changes date?

thanks

Scott