Incremental refresh for larger datasets

[Rockz]

Dear champions!,

I need an urgent help and good advice from you!!

In my case, within one of my Mart, I have data spanning from January 1, 2023, to December 31, 2025. I have considered this timeframe as the range start (January 1, 2023) and range end (December 31, 2025) period.

Subsequently, I selected a table containing a large volume of data and applied relevant filters. Initially, the impact date column in this table was in text format, so I converted it into a date-time column. I then applied the range start range end parameter filters to this column.

After making these adjustments, I loaded the data into Power BI Desktop. Within Power BI, I selected the table where I wanted to implement incremental refresh. By right-clicking on the table, I accessed the incremental refresh settings, enabled them, and specified 36 months for archived data and 2 months for incremental refresh data. After applying these changes, I published the dataset to the service.

However, the process encountered timeout issues. Could you kindly advise on how I might resolve these issues and successfully implement the incremental refresh? and my data is from sql server database.

This is my M query for that table.

please explain me step by step clearly!

Thanks in advance!

[v-nmadadi-msft]

Hi @Rockz ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks 

[v-nmadadi-msft]

Hi @Rockz ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @Rockz ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @Rockz ,
Thanks for reaching out to the Microsoft fabric community forum.

It's possible that model refresh queries aren't being folded, leading to excessive refresh times and query mashup engine resource utilization. Use a tracing tool at the data source to confirm that each partitioned query includes filters based on RangeStart and RangeEnd. If not, check for query folding in Power BI Desktop by loading a small, filtered dataset. If folding isn't occurring, adjust the model to support it. Then, either perform a metadata-only update via XMLA (for Premium) or delete and republish the model (for Pro), and try the initial refresh again.
Troubleshoot incremental refresh and real-time data - Power BI | Microsoft Learn


Another possible cause might be the timeout is caused by the amount of data that must be queried and loaded into the model partitions exceeds the time limits imposed by the capacity. Reduce the size or complexity of your model, or consider breaking the model into smaller pieces.
Troubleshoot incremental refresh and real-time data - Power BI | Microsoft Learn

Additionally, For models published to Premium capacities, if the model grows beyond 1 GB or more, you can improve refresh operation performance and ensure the model doesn't max out size limits by enabling Large model storage format before performing the first refresh operation in the service.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[ROCKZZ]

my data is from sql server database. which complex view.

This is my M query for that table.

let
    Source = Sql.Database(ServerName, DatabaseName, [CommandTimeout=#duration(0, 2, 120, 0),Query="SELECT * FROM "& SchemaName &".EIP_DMS WITH (NOLOCK) ",CreateNavigationProperties=false]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Impact date", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Impact date] >= RangeStart and [Impact date] <= RangeEnd)
in
    #"Filtered Rows"

please explain me step by step clearly!

Thanks in advance!

[Deku]

Native queries don't like to fold. Please see the docs on how to address this

https://learn.microsoft.com/en-us/power-query/native-query-folding

If timeout is still a issue. You can publish to the service. Instead of doing a full refresh, use tabular editor to apply the policy on the table. The use SSMS and reach each partition one by one or in batches