Forum Discussion

useruserhi91's avatar
useruserhi91
Icon for Helper I rankHelper I
1 year ago

Recommended Incremental refresh settings for BC365 fact tables (like GL Entry)?

I have tried out so many settings for incremental refresh on large transaction tables from BC365 (like general ledger entries, project ledger entries), etc. Nothing works - it only makes the dataf...
Need Help
useruserhi91's avatar
useruserhi91
Icon for Helper I rankHelper I
1 year ago

Thanks for replying, I am using the standard Business Central connector. I am turning off query folding option as it says that my query could not work if it is enabled. Any ideas on what to do? Thanks 🙂

LuitwielerMSFT's avatar
LuitwielerMSFT
Icon for Microsoft Employee rankMicrosoft Employee
1 year ago

I see, I believe that your query uses odata in de backgrounds which should support some kind of folding. Would you mind sharing a screenshots of your applied steps with the folding indicator? there may be a step that causes folding to break.

 

When folding breaks and you allow incremental refresh to work anyways; the dataflow will pull all data times the number of buckets (3 times in your case) and only apply filters after data is pulled. This will cause huge slowdowns as you experience right now. 

  • useruserhi91's avatar
    useruserhi91
    Icon for Helper I rankHelper I
    1 year ago

    Yes, here is my query for GL entries from an advanced/custom BC API (same error occurs if I'm using standard API):

    I basically navigate to BC -> navigate to production -> the correct company -> advanced api -> gl entries table. I then remove some columns and change data types like so:

    Table.TransformColumnTypes(#"Removed columns", {{"systemModifiedAt", type datetime}, {"postingDateTime", type datetime}}).

    Please advise lui 
    • LuitwielerMSFT's avatar
      LuitwielerMSFT
      Icon for Microsoft Employee rankMicrosoft Employee
      1 year ago

      What is the orignal data type that is returned by the connector? there may be some columns changes and navigation steps that is breaking your folding. We do have some guidance listed here: Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn 

       

      Folding is key to get incremental refresh to work and should give you the performance you need. Each situation is different and you may need to adjust your steps or use native queries to get what you need. miguel  would you be able to help here a bit with suggestions? 

      • useruserhi91's avatar
        useruserhi91
        Icon for Helper I rankHelper I
        1 year ago

        You mean what data type the initial (BC) connector has? These:

         

        "Data" naturally contains the data i need. In the next Navigation steps this "Data" column has these data types in chronological order:

        "Table"
        "Any"
        "Table"

        "Table"

        And there goes my final table.. Is the "any" data type causing the error?