Filtering Extractors in Query mode

dataservices sap-erp-bw
1

I am working with one of our ABAP developers to devlop some custom extractors. We have run into an issue where my filters are not being passed through to the extractor, thus all the data is pulled into DS and then thrown away by the Query transform.

My Query transform has AEDAT <= $lastDate
and the developer has as far as they can tell set this to be a filter.

We are both unsure where the issue is…
when the developer tests the extractor on their side (I think she uses a program called RSA3 ?) it successfully applies the filter.

One challenge I have is there (as far as I can tell) is no way of seeing from the metadata in DS which fields on the extractor are valid to filter. I’m inclined to put in a feature request to expose this meta data to DS as it would be quite useful in these circumstances.

Any suggestions here ?

Thanks all

UPDATE: I had a look at the Optimized SQL for the sap datastore and it shows:

SELECT ROCANCEL , VBELN , ANGDT , AUART , AUGRU , BNDDT , BUKRS , ERDAT , FAKSK , HWAER , KUNNR , KURST , KVGR1 , KVGR2 , KVGR3 , KVGR4 , KVGR5 , LIFSK , PVRTNR , STWAE , VBTYP , VDATU , VKBUR , VKGRP , VKORG , VTWEG , WAERK , SPARA , VGTYP_AK , ANZAU , PERIV , SPART , VGTYP , AEDAT
FROM ZSO_VAHDR_SAP_DW

So it is definitely not passing the filter through. The question is why ? Is there something my ABAP developer has to do to expose the metadata of the filters to DS ?

Leigh Kennedy :australia: (BOB member since 2012-01-17)

2

I’ve confirmed the issue and I found this thread which relates to the same issue:

http://www.forumtopics.org/busobj/viewtopic.php?t=182262&view=next&sid=a7f1d4f730a6252341f0edffbc79b038

It seems like this is an outstanding bug/issue with passing parameters to extractors.
What I have found is that this works:

AEDAT = ‘2009.01.01’

but not:
AEDAT >= ‘2009.01.01’
AEDAT = $gvDeltaDate

or any other variation.

Assuming I am correct, then my options seem to be:

  1. Change the extractor (its a custom one) to take a single data and internally process that as “>=”

  2. re-write using an ABAP data flow. Will this pass the parameters through any better ?

  3. Wait till 4.1 and re-write using the base tables… (any chance to get onto the early adopters list ?)

Any input is appreciated.

Thanks.

Leigh Kennedy :australia: (BOB member since 2012-01-17)

3

Regarding the abap DF: it will work with any variable and and any combination such as =, <=, >=, <>, !=, IN () NOT IN ().

I do not know how complex your table structure is and if there is a decent field indicating the change, but I’d say to go for a abap DF.

4.1 is still in ramp-up and to be released in December, which is still a loooong time away.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

4

Well the complexity has to go somewhere !

I’ll talk to my ABAP developer re - what out options are.

Thanks.

Leigh Kennedy :australia: (BOB member since 2012-01-17)

5

Leigh, sorry for not responding sooner but I wanted to document this in the wiki but haven’t found the time to do so yet.

Hence please take my statements with caution.

I state that an Extractor does not support >= conditions. What your SAP person did in RSA3 was using the between syntax by specifying between 2009.01.01 and nothing.

So if I am right, change your where clause to

AEDAT >= ‘2009.01.01’ and AEDAT <= ‘9999.01.01’

and you will see a pushdown.

AEDAT = $gvDeltaDate should have worked as well as pushdown.

Werner Daehn :de: (BOB member since 2004-12-17)

6

No need to apologise !

My abap developer re-wrote it to work around this… I’ll see if they kept the earlier version so we can test this…

the $gvDeltaDate was a datetime. When I changed it to a date the pushdown worked !

Strangely I found if I did anything else in the query (i.e. and order by, or added calculated columns on the output side, or tried to calculate the date with a function) it stops pushing down. It is very sensitive !

Is there any way in a future release to show what operations are supported ?

Does DS have access to this metadata ?

Thanks.

Leigh Kennedy :australia: (BOB member since 2012-01-17)

7

You can open the extractor, rightclick the AEDAT column and check the class attribute properties - it way say if the column supports filitering and what kind of filters. So yes, DS does import that information and is using it.

Yes, these conversions are a tough thing. On the one hand it is simple, a comparison from date to datetime needs to be converted to the highest datatype, so your comparison is casting the date to a datetime and hence the extractor column has a function, not the global variable. So it is obvious why this happens. On the other hand, from a user perspective, I agree, it is tough to figure out. I didn’t even have the idea of asking you that!

Werner Daehn :de: (BOB member since 2004-12-17)

8

Great - I never would have found that myself - one for the wiki for sure !

And after talking to my developer we changes the code back and specified the range as you recommended and it pushed down !

NB: I had an issue (unrelated to the extractor) so I ran my job in debug mode. Debug mode stopped the pushdown %$*! Not sure if that’s intended behavior, but without restriction the extractor I was testing takes 57 hours !

Leigh Kennedy :australia: (BOB member since 2012-01-17)