Forum Discussion

BenGordonSmith's avatar
BenGordonSmith
Frequent Visitor
2 years ago

Converting text to date breaks query folding in Sap BW source

Hello,   I'm trying to convert a text date to datetime so that I can enable incremental refresh on a report sourced from SAP BW4. When I do the last step of converting the text date to date query f...
Need Help
BenGordonSmith's avatar
BenGordonSmith
Frequent Visitor
2 years ago

Hello, 

 

It's a straight up query from a SAP BW database but where the data fields are in the format 'SEP 2023' for example. 

 

My belief was that even if I do enable incremental refresh on a none-folded query it doesn't really work behind the scenes, for example: https://www.youtube.com/watch?v=RnrCKn5ykCc

So it would activate but provide no gains, meaning I'm still looking for an answer to my original query if possible. 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    That video only shows that the customer didn't understand how Incremental Refresh works in the service.  In their scenario the filtering needs to happen in the native query.  Then it will work, despite not folding.

     

    You need to keep in mind that the ultimate goal of incremental refresh is to flush and fill a partition. Nothing more, nothing less.

     

    Please show a sanitized version of your M code.

    • BenGordonSmith's avatar
      BenGordonSmith
      Frequent Visitor
      2 years ago

      I think you are answering a different goal - my goal is to use incremental refresh and obtain it's performance gains in refresh time not just do it for it's technical description of flushing and filling a partition. I also want the benefit of quicker refresh times and that is what I would like to emphasise. 

       

      It sounds like you sare saying if I implement a non query folded incremental refresh it will not provide the refresh performance time gain that I am after, therefore I will not continue here.

       

      As if now then my belief is that with a text date it is not possible to gain any performance improvement by using incremental refresh. I will end the investigation here unless anyone has an example where they have done it and can post about it, thanks. 

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

         

        my goal is to use incremental refresh and obtain it's performance gains in refresh time

         

        You can specify partitions down to day level.  That should keep your refresh time down.

         

        The real issue with incremental refresh is that it is incremental. What most business scenarios require is differential refresh or CDC. You need to do extra work to accomplish that with the given Microsoft features.