Converting text to date breaks query folding in Sap BW source

[BenGordonSmith]

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 folding breaks and disables the possibility of incremental refresh.

So far I have tried:

1. Using the Home - Transform - Data Type: DateTime button. No change, breaks query folding

2. Editing the M query with Date.From and DateTime.From on the specific text field instead of a seperate step. No change, breaks query folding.  

3. Change the source query to create a datetime field in SAP BW - not possible in my organisation as the entire BW is frozen for query edits and transformations can only occur in the next layer due to historical security issues.

Is there any sort of workaround I can do to enable this? It could save the org hundreds of hours of query refresh time.

Thanks,

Ben

[BenGordonSmith]

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]

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]

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]

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.

[lbendlin]

query folding breaks and disables the possibility of incremental refresh.

That is incorrect.  While Query folding is desirable, it is not required for incremental refresh.

Please show a sanitized version of your M code.