Forum Discussion

Bokazoit's avatar
Bokazoit
Continued Contributor
1 year ago

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

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...
Need Help
v-karpurapud's avatar
v-karpurapud
Community Support
1 year ago

Hi Bokazoit 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

Bokazoit's avatar
Bokazoit
Continued Contributor
1 year ago

I am actually testing this using the Business Central direct data source within Power BI Desktop and when uploading to Power BI it times out. I have absolutly no idea why such a small amount of data can become such a burden. Nor did the OData feed work, and from my understanding from what You wrote I can't supply a web API call since it will not be able to handle query folding.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi Bokazoit 


    The main challenge is not the 3.5M rows themselves, but how the query executes within the Power BI Service. If query folding is lost or the dataset exceeds Pro workspace limits, the Service attempts to process the entire dataset locally, resulting in timeouts. Since you have already tested both the Business Central connector and OData.Feed, and confirmed that folding is maintained but issues persist, the likely constraint is the Pro workspace capacity (2-hour refresh limit and 1 GB compressed model size). To address this, consider reducing the data history window during testing, removing unnecessary columns to minimize model size, or upgrading to Premium/PPU capacity for more reliable incremental refresh. If these steps do not resolve the problem, it is advisable to raise a Microsoft Fabric support ticket. Please include relevant error screenshots and a summary of your troubleshooting steps to assist the support team in identifying a solution.

    You can use the following link to create a Microsoft Support ticket:

    How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

    Regards,
    Microsoft Fabric Community Team.

    • Bokazoit's avatar
      Bokazoit
      Continued Contributor
      1 year ago

      So what You are saying is that the limit is in the engine, because I have a working load of the dataset using my current data warehouse in a Pro enviroment, but switching to Power BI connection is a bad idea?

      • v-karpurapud's avatar
        v-karpurapud
        Community Support
        1 year ago

        Hi,

        Connecting directly to Business Central is not necessarily a bad approach, but it behaves differently than using a data warehouse. Business Central uses OData APIs, which aren’t designed for large analytical workloads. Even with query folding, API refreshes can be slower and use more resources, often hitting Pro workspace limits. If you want to stay within Pro, it’s more reliable to stage Business Central data in your data warehouse or Azure SQL/Data Lake and load from there. Alternatively, you can move the dataset to a Premium Per User or Premium capacity workspace for better resources and more consistent incremental refreshes.

        Regards,
        Microsoft Fabric Community Support Team.