Dataflows incremental refresh: keep 12 end of months and all daily current month

[mBiella]

I would need some advice related to dataflow incremental refresh (I read the documentation but I did not find an answer for this).

I have created a dataflow which get data out of an Oracle database.

The situation that I would like to have is the following:

have in the dataflow always only:

- end of month snapshot data for the last 12 months

- end of day snapshots for all days of the current month only

Thus, for example:

- If today is 5th january 2024, i would like to have in the dataflow 12 end of month snapshot plus 5 january daily snapshot

- if today is 20th january 2024,  I would like to have in the dataflow 12 end of month snapshot plus 20 january daily snapshot

- if today is 1st february 2024,  I would like to have in the dataflow 12 end of month snapshot plus 1 daily februrary snapshots (thus all january daily snahpshots should disappear from the dataflow)

Since data are quite large and, day by day, i need just to load the new data (the new daily snapshot), I want to do that leveraging on incremental refresh loading indeed just the additional daily snapshot.

Is there a way to have the situation aformentioned?

The key point is that, when a new month starts, all the daily snapshot (which are the daily snapshot of the previuso month) must be deleted and removed from the dataflow.

Many thanks for any advice you might provide.

[edhans]

You will need two dataflows for this I believe, one for the monthly snapshots, and one for the daily snapshot. The Daily if I am understanding your needs, would not need to be incremental refresh, just always pulling today's records. If you need these in a single dataflow, then you'd need a third dataflow that would essentially "union" these. You can also do the union in the semantic model if the only place you need them is in Power BI.

[mBiella]

Thanks edhans for the suggestion.

Anyway please consider that, if i split the dataflow, also the daily one need to be incremental.

Indeed, I would need to have all the daily snapshots for the current month (not only the last snapshot).

It needs to be incremental so that, each day, i just incrementally add the last daily snapshot.

Thus, even considering to include in a dedidated dataflow just the daily snapshot, I would still need to have it incrementally refreshed and I would still need, at the end of the month, to delete the daily snapshots of the month just ended.

This is what I do not know how to do.

Thanks

[edhans]

You'd set up two dataflows. One that was monthly. The other would be daily. The issue with the daily is it is based on day counts, not a month. So you'd need to set it for 31 to cover all days in all months, but for 30 and 28 day months, it would have too much info in it. You'd need to then in the 3rd dataflow, or the Power BI semantic Model, have more granular control in Power Query to keep exactly what you want. You cannot have that kind of control in the incremental refresh policies.