Is anyone successfully using Incremental Refresh?

[ctmullins]

I was excited to see that the PBI Service supports incremental refresh.  So I dug into it.

Got everything configured according to the documentation.  Worked great - refresh times plummeted.

But - my data quickly became a mess.  

Why?  Because the Incremental Refresh engine has no knowledge of the concept of a Primary Key.  It just blindly loads rows of data that fall into your defined date range.

So it's unusable for me.  Pity.

Swing and a miss, Microsoft.

[Anonymous]

Hi @ctmullins ,

Based on the testing, incremental refreshes will not mess up the data. Incremental refresh extends scheduled refresh operations by providing automated partition creation and management for semantic model tables that frequently load new and updated data.

The filter on the date column is used to dynamically partition the data into period ranges in the Power BI service. Incremental refresh isn't designed to support cases where the filtered date column is updated in the source system. An update is interpreted as an insertion and a deletion, not an actual update.

You can also view the following document to learn more information.

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Troubleshoot incremental refresh and real-time data - Power BI | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ctmullins]

Thanks - so do your fact tables include rows that get updated?  Mine do.  In my testing, the engine was loading the fresh rows, but not deleting the prior versions of those rows (again, based on a PK).  The documentation mentions nothing at all about primary keys, and my testing confirms that there is no notion of replacing a row with its current, updated version.

[AMeyersen]

Hi @ctmullins,

if the date values relevant for incremental refresh can change, then it's way more complicated. Power BI is not able to track these changes and will indeed mess up data in a simple setup.

You might be able to utilize the "Detect data changes" setting. You'd need to add a [last changed] column to the fact table. And you'd need to make sure to always trigger the incremental update in the correct date ranges by updating [last change] accordingly.
It's a bit more complicated, but it's still possible to make it work this way.

[AMeyersen]

Hi @ctmullins ,

incremental refresh works very well for me.
A common mistake I encountered several times is confusion about >= RangeStart and < RangeEnd. The equal sign must occur exactly once in the filter statement, otherwise data becomes inconsistent.

Another pitfall is using incremental refresh on dimension tables. Although it is possible, it's usually very tricky to get the logic right. Therefore I'd absolutely recommend to only use incremental refresh on fact tables.

Whenever I avoided these two pitfalls, incremental refresh worked extremely well.

[Anonymous]

I have seen multiple successful Incremental Refresh projects. Ours are based on a Fact table where the partitions are like a InvoiceDate or OrderDate or Inventory???Date. The CDC date column is a ModifiedDate or UpdateDate and we have no issues with duplicate rows from primary keys