Incremental Refresh by Audit Fields

[danielroca]

Hello everyone,

I am configuring the incremental refresh for a fact table that models forecasted data. This means that it has future dates. The documentation (Incremental refresh - JSON for future dating) states that for this scenario, we should change the following JSON using SQL Server Management Studio, setting the effectiveDate to a future date so that the dataset can be partitioned for future dates:

The problem is that we have some forecasted dates that vary from the following month up to lots of years in the future. If we set this effective date far away in the future, let's say 2030. We would have to configure the refresh the following way:

With this configuration, we would lose the benefits of incremental refresh.

Workaround 1: Is there a way to dynamicly set the effective date from this JSON file? 

Another option is to use the audit fields from the table, there is a field called createdDate, this field has the date when the record was uploaded to the fact table. This will work because we are not using future dates instead we are using past dates.

Workaround 2: Has someone had the experience of working with audit fields as the dates for the incremental refresh? Do you recommend using this approach instead of the previous one?

Any other workaround you could think of? 😀

Thanks a lot for taking the time of reading and helping! Any advice will be very helpful!!! 

[lbendlin · Accepted solution]

Incremental refresh is designed for (very) slow changing data, close to immutable.  Stuff that you receive once and that then doesn't ever change again.

Why don't you put your historical (immutable) data in one table with incremental refresh and your forecast data and other mutable data in another table without incremental refresh?  You can union the tables in Power Query if needed.