Forum Discussion
Is There Effective Alternative to Incremental Refresh for Dynamics 365 Business Central Data Source
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?
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.
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?