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
LuitwielerMSFT's avatar
LuitwielerMSFT
Icon for Microsoft Employee rankMicrosoft Employee
1 year ago

Hey useruserhi91 ! 

 

My name is Jeroen Luitwieler PM for Incremental refresh in dataflows gen2. Super unfortunate to hear your experience is not as expected. 

Something I noticed in your screenshots is that you have "requiere incremental refresh query to fully fold" turned off. Is there any reason why you have turned off this setting? Also, which connector are you using? keen to learn more of your end to end situation. 

  • 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