Forum Discussion
Is There Effective Alternative to Incremental Refresh for Dynamics 365 Business Central Data Source
Hi Community,
I am working with Dynamics 365 Business Central as a data source in Power BI and facing challenges implementing Incremental Refresh.
As far as I understand, Incremental Refresh requires query folding to push the RangeStart and RangeEnd filters back to the source. However, Business Central OData/API sources do not seem to support query folding for many scenarios, making Incremental Refresh difficult or impossible to implement effectively.
This becomes a major concern when working with large transactional tables such as:
* Value Entries
* Item Ledger Entries (ILE)
* G/L Entries
* Customer Ledger Entries
* Vendor Ledger Entries
Currently, I am exploring workarounds such as:
* Splitting historical and recent data into separate queries
* Using cutoff dates
* Creating custom APIs
* Maintaining separate historical datasets
My questions are:
1. Has anyone successfully implemented an effective Incremental Refresh strategy with Business Central?
2. Are there any recommended alternatives when query folding is not supported?
3. What approaches have worked best for large tables such as Value Entries or Item Ledger Entries?
4. Has Microsoft shared any roadmap or guidance regarding native support for Incremental Refresh with Business Central data sources?
I would appreciate hearing about real-world implementations, best practices, and lessons learned from the community.
Thank you.
In that scenario, the Historical + Current split approach is probably the most practical option. A common workaround with standard Microsoft Dynamics 365 Business Central APIs is to keep older fiscal years static and refresh only the recent months more frequently, then append the queries together in Power BI.
RangeStart and RangeEnd may still provide some benefit if the Business Central endpoint accepts the date filter, but since query folding is limited, they usually should not be relied on as a fully effective Incremental Refresh solution for large tables like Value Entries or Item Ledger Entries.
For large BC tables, keeping the queries as simple as possible helps the most. Filtering by posting date early, splitting data by year or period, removing unnecessary columns and avoiding heavy Power Query transformations before filtering can help reduce refresh times.
I have seen practical implementations where separate yearly queries or an “Archive + Active” approach were used to keep refresh times manageable without Fabric or SQL staging, though they are not available to me currently to share them. While more manual operationally, it is currently one of the more realistic approaches when limited to standard Business Central APIs and Power BI only.
You can go through below MS docs and discussions though:
Configure incremental refresh for Power BI semantic models - Power BI | Microsoft LearnWeb Service Performance (OData, API, and SOAP) - Business Central | Microsoft Learn
Solved: Business Central and Power BI loading data - Microsoft Fabric Community
Business Central and Power BI: What can you do to make it better?
7 Replies
- trivedisunita
Continued Contributor
Hi sumitmanjarme ,
Incremental Refresh with Dynamics 365 Business Central is very limited because query folding often breaks on OData/API endpoints, especially for large transactional tables like Value Entries, ILE, G/L Entries, and Ledger tables.
You can do some workarounds-
Check folding: Always verify View Native Query to confirm filters are pushed down.
Use audit fields: If possible, extend BC tables with LastModifiedDateTime.
Split queries: Separate historical vs. current data to reduce refresh load.
Consider staging: For enterprise scale, stage data in Azure Data Lake or Fabric.
Monitor roadmap: Follow the Microsoft Learn Business Central Power BI docs.
The validated best practice is to use custom APIs or staging plus cutoff strategies.
If this reply helped, mark it as a solution! It encourages contributors and keeps the community strong.
Thanks
v-hashadapuCommunity Support
Hi sumitmanjarme , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.
Hi v-hashadapu ,
Thank you for following up.
The issue is still not resolved. My main objective is to implement Incremental Refresh effectively for Dynamics 365 Business Central data in Power BI.
Since the Business Central connector does not appear to support query folding for many standard APIs/OData endpoints, I am looking for guidance on the recommended approach to implement Incremental Refresh in this scenario.
Could you please advise on:
The best practice for implementing Incremental Refresh with Business Central data sources.
Whether RangeStart and RangeEnd parameters can be pushed to Business Central APIs/OData endpoints.
Any supported methods to achieve efficient incremental loading without custom extensions.
Recommended approaches for large tables such as Value Entries, Item Ledger Entries, and G/L Entries.
Any detailed guidance, examples, or Microsoft recommendations would be greatly appreciated.
Thank you.
- v-hashadapu
Hi sumitmanjarme , Thank you for reaching out to the Microsoft Community Forum.
Your understanding is correct. The main limitation is that Incremental Refresh in Power BI depends on query folding, but folding support with standard Microsoft Dynamics 365 Business Central OData/API endpoints is very limited, especially for large transactional tables like Value Entries, Item Ledger Entries and G/L Entries. Because of this, the RangeStart and RangeEnd filters are often not pushed efficiently back to Business Central. In many cases, Power BI still ends up scanning a large amount of data before applying the filter locally, which makes Incremental Refresh unreliable for high-volume tables.
Most large-scale implementations avoid using Incremental Refresh directly against Business Central APIs. Instead, they first stage the data into a foldable source such as SQL, Microsoft Fabric Lakehouse/Warehouse or Dataflows Gen2 and then apply Incremental Refresh there.
If custom APIs or staging are not possible, try splitting historical and current data using posting date filters or separate datasets. It is more manual, but currently it is the most practical approach for large BC tables.
Hello v-hashadapu,
Thank you for the clarification.
Unfortunately, the client cannot afford Microsoft Fabric licensing or a separate SQL staging environment. Therefore, I am looking for the most practical workaround using only Business Central and Power BI Pro/Premium capabilities.
Could you please advise on the recommended approach in this scenario?
For example:
- Is it a good practice to split data into Historical and Current queries (e.g., data before a certain date and recent data) and then append them?
- Can RangeStart and RangeEnd parameters still provide any benefit with Business Central APIs, even if full query folding is not available?
- Are there any Microsoft-recommended patterns for handling large tables such as Value Entries, Item Ledger Entries, and G/L Entries without Fabric, SQL staging, or custom APIs?
- Has anyone successfully implemented a pseudo-incremental refresh approach directly against standard Business Central APIs?
Additionally, have you implemented any such solution in real-world Business Central environments? If so, could you please share the approach that was used?
If there are any Microsoft articles, community forum discussions, blog posts, documentation, sample implementations, or web links that address this challenge for high-volume Business Central tables, I would greatly appreciate those references.
I am specifically looking for proven approaches that help reduce refresh times and improve performance for heavy tables when Fabric, SQL staging, and custom APIs are not viable options.
Thank you for your guidance.
Hello v-hashadapu ,
Thank you for the clarification.
Unfortunately, the client cannot afford Microsoft Fabric licensing or a separate SQL staging environment. Therefore, I am looking for the most practical workaround using only Business Central and Power BI Pro/Premium capabilities.
Could you please advise on the recommended approach in this scenario?
For example:
1) Is it a good practice to split data into Historical and Current queries (e.g., data before a certain date and recent data) and then append them?
2) Can RangeStart and RangeEnd parameters still provide any benefit with Business Central APIs, even if full query folding is not available?
3) Are there any Microsoft-recommended patterns for handling large tables such as Value Entries, Item Ledger Entries, and G/L Entries without Fabric, SQL staging, or custom APIs?
4) Has anyone successfully implemented a pseudo-incremental refresh approach directly against standard Business Central APIs?
Additionally, have you implemented any such solution in real-world Business Central environments? If so, could you please share the approach that was used?
If there are any Microsoft articles, community forum discussions, blog posts, documentation, sample implementations, or web links that address this challenge for high-volume Business Central tables, I would greatly appreciate those references.
I am specifically looking for proven approaches that help reduce refresh times and improve performance for heavy tables when Fabric, SQL staging, and custom APIs are not viable options.
Thank you for your guidance.
