Best Approach for Incremental Refresh When Historical Records Can Be Updated

[Mayankb024]

I have a Power BI report that currently imports around 7 years of historical data. During each refresh, only about 1% of the records are actually updated, so I'm exploring whether incremental refresh would be a suitable approach to reduce refresh time.

The challenge is that records from any point within the 7-year history can be updated, not just recent records.

One idea I had was to configure incremental refresh based on the Last Modified Date and set the refresh window to 1 month. This would refresh only the data modified in the last month. However, I'm concerned about records that were originally created more than a month ago but are updated today. If the incremental refresh appends the updated record without replacing the existing one, wouldn't that result in duplicate records?

Would it be possible to handle this by filtering on the primary key and keeping only the record with the latest Last Modified Date (for example, using the primary key together with MAX(LastModifiedDate))? Or is there a better approach for handling this scenario with incremental refresh?

I'm not a Power BI expert, so I'd appreciate any guidance or best practices from those who have implemented similar solutions.

[v-saisrao-msft]

Hi @Mayankb024,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[Mayankb024]

Hi @v-saisrao-msft Not yet if we follow this approc then we will be refreshing the complete 7 years of data again andit will not reduce the compute cost.I am looking in to XMLA endpoint solution if it can help. Thanks

[v-saisrao-msft]

Hi @Mayankb024,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @Mayankb024,

According to Microsoft, you can refresh individual partitions selectively, run targeted refreshes instead of processing the full table, update specific historical partitions, and adjust Detect data changes behavior.

Advanced Incremental Refresh and Real-Time Data With the XMLA Endpoint in Power BI - Power BI | Micr...

Thank you.

[Mayankb024]

Hi @v-saisrao-msft  Yes, I have gone through it but again it will not serve the purpose of reducing the compute cost as it will still scan the complete table. Now I am thinking to prepare the data set in S3 and connect S3 bucked to my power BI.   Still analysing the cost part with this solution.

Thanks  

[v-saisrao-msft]

Hi @Mayankb024,

Thank you @MattiaFratello, fo your insights.

If you have a dependable LastModifiedDate (or another audit date/time column), you can pair Incremental Refresh with Detect data changes so that only the periods with updated data are refreshed. Once the initial refresh is complete, Power BI will refresh only the data in the configured refresh window, so this is appropriate only if your refresh period includes the timeframe in which records can be updated. Microsoft also recommends using a separate date/time column for Detect data changes instead of the same column used for RangeStart and RangeEnd.

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn

Thank you.

[Mayankb024]

Hi @MattiaFratello  Thank you so much for responding. My doubt is Detect data changes can update archived records as well ? My understanding is, it can only make changes in the incremetal refresh partition not in archvied one. My primary motive is to reduce the compute cost at DB level when refreshing the report.  

fFor second option we can create a staging table with only updated records but how we can merger them with 7years of data imported in Power BI ?

[MattiaFratello]

Hey @Mayankb024, sorry got pretty busy today

I would try to set the incremental refresh for the full 7 years (every time you refresh 7 years) with 1 day partition and Detect Data Changes active.

Like this you would not lose any modified data but you will refresh only small partitions?

I would give it a try and see if it works before going into the staging table which will require more effort.

[Mayankb024]

Hi @MattiaFratello   So you are sayinh  we will set archive data as one day and keep 7 years as incremental refresh with detect data changes. Let me try this approch.  Thank you

[MattiaFratello]

@Mayankb024 Let me now if it works. If not, I can try to think about the DB solution but maybe during the weekend.

[MattiaFratello]

Hey @Mayankb024,

what if you create very small partitions, like 1 day, and you refresh the full period (7 years), but activate the Detect Data Changes? In this way if Power BI detects a change in a row, it will refresh only 1 day and not a full month.

I'm a bit unsure about how it will handle so many partitions but you could give it a try?

Other option is to get a staging table in your source data with only the latest version of a record but maybe you would need helo of your DEs?