Forum Discussion
Incremental refresh with non-rolling archived data
For anybody interested, this is the solution I came up with.
The source is a SQL DB, and the table/view is set to accumulate data from January 1, 2018 onwards. So instead of going back 4 years to capture 2018, 2019, 2020 and 2021 into the archive in PBI, and loosing 2018 next year, I'll set that period to 7 years and will lose my 2018 only in 3 years.
What this will do is that because the source starts from 2018, the report won't have data before 2018 and the fact that it'll keep rolling for 3 years will not make me lose any data in the report dataset.
It doesn't have to be 3 years and can be any time period more than 4 years that I require in my dataset's archive. I'm taking 4+3, assuming that the report will have to be enhanced or modified during these additional 3 years anyway and then I can re-set the rolling period for the archive part again.
Here's the chart for the incremental plan:
If anybody has a better or an alternative solution, please share!
You could set a primary filter in the transformation steps (like 01/01/2018), and then set the rolling incremental archive to 100 years. This way, you will always have data from the start of 2018 and still get daily increments to the archive.
Thanks Anonymous, what I eventually implemented it what you suggested, if I understood you correctly. Please see my own explanation added above. I just didn't go 100 years back for the archive, but additional 3 years back (when the report will get new pages or other changes added and the archive can be reset).
The only drawback of this solution is if the source table has more data than what should be present in the report. In that case, the archive will actually be loadinng unnecessary data, so either a special table/view has to be created in the source to feed PBI report (like in my solution) or, I assume, the archive has to be kept small and the refresh has to be reset something like once in a couple of years (that's when we're talking about tens of millions of rows of data every year).