Incremental Refresh - historical data updates

[JoyH]

Hi!

One of our tables has 80M rows.

Each row contains the insert_dtm (the datetime the row was created) and update_dtm (the datetime the row was last updated).

During the incremental refresh, we need to add the new rows created yesterday and update any rows that were updated yesterday.  It's possible that a row from 2018 will get updated yesterday and we need to capture that update.

I have tried many combinations of the incremental refresh policy and the only one that seems to work is:

Store for 5 years on insert_dtm

Update 60 months on insert_dtm

Detect changes for 60 months on update_dtm

...but this seems very inefficient as the database appears to get hit 120 times. Is there a better way to configure the refresh policy?

[V-lianl-msft]

The open-source Tabular Editor 2 tool provides an easy way to bootstrap an initial refresh operation. After publishing a model with an incremental refresh policy defined for it from Power BI Desktop to the service, connect to the dataset by using the XMLA endpoint in Read/Write mode. Run Apply Refresh Policy on the incremental refresh table. With only the policy applied, partitions are created but no data is loaded into them. Then connect with SSMS to refresh the partitions sequentially or in batches to load and process the data. To learn more, see Incremental refresh in Tabular Editor docs.

See if this blog will help:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla 

[mwegener]

Hi @JoyH ,

I think you can not optimize it, because each month partition must be checked for updates.