Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Haiko
Regular Visitor

Incremental Refresh or alternative

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.

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

4 REPLIES 4
Haiko
Regular Visitor

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

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Haiko
Regular Visitor

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
Super User
Super User

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.

 

KNP_0-1669315070581.png

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.