Incremental refresh with non-rolling archived data

[OB]

Hello,

It looks like when defining a policy for incremental refresh, the user can set up a rolling window for data archive and then a rolling window for incremental refresh as described here. 

Is there a way to set up a cumulative window for data archive? 

I want to load data from January 1, 2018 onward and keep it growing with the incremental refresh going one year back from today.

Thank you!

[OB · Accepted solution]

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! 

[Anonymous]

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.

[OB]

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).

[OB]

Sorry, but could you elaborate @parry2k ?  

This is not what I see when I'm testing based on a small range.

Let's say I want to have data starting April 15th (fixed date) and I want my archive to keep growing every day, while making incremental refresh on the last 5 days.  What I could do on April 15th is set archive to 10 days and refresh the last 5 days.  Both ranges are rolling. 

I ran this report refresh on April 25, 26 and 28th and it doesn't store data starting April 15th. It rolls it every day, so as of today, I can only see data starting April 18th but I'd like to have April 15th onwards.   Is there a solution for that?  Thanks!

[parry2k]

@OB you can set incremental data set refresh for 1 year and that will take care of it, detail is in the same document.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.