Incremental Refresh or alternative

[Haiko]

Hi,

I have a table situation I don't know how to solve, and hope someone could point me in a good direction.

At work we have One single cube, processed backend in Azure SQL, and loaded into Power BI import mode, refreshed daily.

One of the fact tables is a timereporting table containing aprox 4 million rows. The time range of these rows are for back in 2019 up til today, and some of the rows are for current month where there for one day to the other can be new and deleted rows. Since we do a total refresh every day it's not a problem at the moment.

The problem lays ahead, that this table will grow and grow and take longer time to refresh. And even today the timerows for 2019 is from an old system not in use anymore so those rows will never change. And that the same with almost all the rows previous last month. Maybe a row will be deleted and added for a 4 months old period but not very often.

Our licens is pro and will not be premium.

How can I implement some kind of incremental refresh where some rows never need to be refreshed, and others can be "detected and changed" (delete + insert since the primary key change)?

If I understand the refresh incremental function i PBI correctly I can't really control this in a good way with a pro licens, or can I?

I have totalt control over backend, so I can dived and what not tables/view loaded into the model.

[Haiko]

Thank you for your quick response.

In stage table the rows are hard deleted and also hard deleted later in the etl process, but since I can control that I can make it a soft delete when doing the crud operations, so the final table have a updated column and a deleted datetime column.

But won't this create whole lot of rows for the current month, since there's a lot of changes within the current month?

Ideally would be if say current month and previous two would be truncate and load in PBI but older months would be handled with detect data changes at refresh. Or to do some kind of union in power query where one table is 'not refreshed' on daily basis but on monthly basis, but I guess that's not possible...?

[KNP · Accepted solution]

Without a full understanding of the ETL/Database etc. it's difficult to say, but the lastUpdate DT will only cause the data to be refreshed if that date is within the defined incremental refresh period. E.g. if your incremental period is set to 1 month, it also needs to have changed from the last cached version of that date.

With the access you have, I would probably look at simplifying the data in the database before Power BI connects. Always "As far upstream as possible..."

If you haven't read it already...

https://learn.microsoft.com/en-nz/power-bi/connect-data/incremental-refresh-overview#detect-data-cha...

The other thing to consider, what is the performance like now, and how quickly is the data growing?

You may have a working solution for the foreseeable future. You can always make a plan to revisit later.

[Haiko]

Thanks for your clarifications!

As I wrote in the initial question, it's not really a problem today cuz the data isn't growing that fast. But it will be a problem within a year or two, and now I have an idea how to move forward.

Thanks

[KNP]

That's great if you have control over the backend.

Do you have a lastUpdated date?

Is it a hard or soft delete?

If soft, you can use detect data changes with a lastUpdated column.

Alternately, if the business can define a period where data will NEVER change you could use that as your incremental range but performance may not be great depending on how far back that is.

You will need to figure out how to deal with the deletes either way. Ideally it'll be a soft delete or could be captured in some way so they can be excluded on refresh.