Handle "large" amount of data using API and incremental refresh

[Bokazoit]

I write "large" because the intial load is 3.5 mio rows, and that is imho not large. But my problem is this:

It is Business Central and I fetch data using API.

I would like to use incremental load but no matter how I do it. It times out when uploading to my Pro workspace.

First I tried loading all data into a single table and setup for incremental load, but then the API fetches all data everytime as far as I know and times out.

Instead I then created an initial table (using API) with lines from the past and until 2 days ago that I choose not to be refreshed. I then create a new table that retrieves data 2 days back. Those two tables I merge and set it up for incremental refresh, but it still won't work and time out in my workspace.

I would never have thought it to be a problem, but I am really in need for a good approach to solve loading these data...

Any inputs?

[v-karpurapud]

Hi @Bokazoit 

Thank you for contacting the Microsoft Fabric Community Forum.

The issue you are facing with loading 3.5 million rows from Business Central into Power BI is primarily due to how incremental refresh is configured, not the dataset size itself. In your current setup, merging historical and recent data breaks query folding, which is essential for incremental refresh to work efficiently. Without folding, Power BI fetches the entire dataset during each refresh, causing timeouts in a Pro workspace. To resolve this, you should use a single query against the Business Central OData entity, applying RangeStart and RangeEnd parameters directly to a timestamp column like lastModifiedDateTime. Remove unnecessary columns early to optimize the select clause and avoid transformations that disrupt folding. Confirm folding by checking “View Native Query” in Power Query. Configure incremental refresh to store long-term history but only refresh recent data, and enable “Detect data changes” to avoid reprocessing unchanged partitions. If the initial refresh still exceeds Pro limits, consider exporting Business Central data to Azure Data Lake or upgrading to Premium/PPU capacity for higher refresh thresholds.

For more detailed information please refer to the official Microsoft documentation on
Enabling Power BI integration with Business Central - Business Central | Microsoft Learn, Extract data from Business Central - Business Central | Microsoft Learn
 

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.

[Bokazoit]

Thanks for the answer. But I need some help here. I think I understand that I need a single query to fetch the dataset and not merge datasets. But what I am unsure about is the single OData part.

I can either fetch the data using the online web source (Dynamics 365 Business Central) to connect to Business Central. That source will retrieve the full dataset. Is that the one You suggest me to add the RangeStart and RangeEnd parameter to? If so, how does Power BI know how to only fetch the modified data, when the source always asks for the full dataset so to speak?

The other single source I can think of is to write the m-code equivalent API call using the web source call. But that requeries me to once again set the dates for the API call to fecth all the data at 20.000 rows per call. I could also add the RangeStart and RangeEnd to that, but will that API call as above ask for all the data?

What am I missing?

I am fairly new to API but have several types of API cubes and non have given me that load of trouble as BC.

Thanks again for any help that will guide me in the correct direction 🙂

[v-karpurapud]

Hi @Bokazoit 

Incremental refresh in Power BI works best when the timestamp column filter can be folded back to the data source. For Business Central, this involves using a single query against the OData endpoint either through the Business Central connector or OData.Feed and applying the RangeStart and RangeEnd parameters directly to a reliable column like lastModifiedDateTime or systemModifiedAt.

When query folding is maintained, Power Query generates an OData $filter clause, so Business Central returns only the rows within the date range. The connector manages paging automatically for efficient data retrieval. However, if you merge tables, append queries, or use custom Web.Contents calls with manual paging, query folding is lost, causing Power BI to load the entire dataset locally and potentially leading to refresh timeouts and performance problems.

To optimize performance, set up RangeStart and RangeEnd parameters in Power BI Desktop, connect to the Business Central OData entity, keep only the necessary columns, and apply the timestamp filter before any complex transformations. You can check if folding is preserved by right-clicking the filter step and choosing “View Native Query”; if a $filter clause appears, folding is still active.

After publishing, configure incremental refresh to store historical data and only refresh recent periods. Enable “Detect data changes” on the same timestamp column to avoid extra processing. If refreshes fail due to Pro capacity limits, try reducing the history window, removing unused columns, or upgrading to Premium/PPU capacity. For large-scale needs, staging data in Azure Data Lake or SQL before loading into Power BI may be more scalable.

In summary, there isn't an additional API step required the Business Central connector and OData.Feed already support server-side filtering and paging. The main point is to apply the incremental filter on a timestamp column within a single query and maintain query folding throughout the process.

Regards,
Microsoft Fabric Community Team.

[Greg_Deckler]

@Bokazoit Set it up as you have with the intial load and no refresh. Hard code the date after the initial load into a second query. Merge the queries, don't worry about incremental refresh and just let the 2nd query refresh everything. A few months from now or so, hard code an end date for the 2nd query and setup a 3rd query and rinse and repeat. Not glamorous but should work. Other option is to open a support ticket with Microsoft and find out why incremental refresh is failing.

[Bokazoit]

Thx, but it is done using system modified, so a date in the initial load may be altered...So it is not a valid path to follow