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 
      • 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?