restriction data to be loaded from extractor

dataservices sap-erp-bw
1

Hi,

How to restrict data to be loaded to my sql server target using SAP extractor. I tried in query which is linked to the SAP extracto 0FI_AP_4 in the where tab. I have below code:

“0FI_AP_4_BO_DS_SAP”.BELNR = ‘5200007027’

But this didn’t work. Where did I do wrong here?

Thanks,
S.

sxu (BOB member since 2012-05-07)

2

what does happen?

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

3

Hi Werner,

What do you mean by “what does happen”?

We are running Data Service 4.0 and we are using SAP extractor to load the data. We are able to get data using init and delta. But there “fitler” condition in the where tab is not working.

The filter condition was placed in the 'where" tab of an query transformation which is linked to the SAP ECC extractor 0FI_AP_4. This is the init load job. It seems that DS still loaded all single records even though there is “where” condition. Where did I do wrong here? Please advise.

Here is the code in the “where” condition:

“0FI_AP_4_BO_DS_SAP”.BELNR = ‘5200007027’

Thanks,
S

sxu (BOB member since 2012-05-07)

4

Really…I am going to pull this and check…Me too using 0FI_AP_4.

It should really filter!!! I bet FI_AP_4 is a Delta Extractor for you… So I guess CDC is enabled in your Extractor Import!!!

ganeshxp :us: (BOB member since 2008-07-17)

5

Thanks for your quick reply.

Yes, Delta has been enabled. See the attachment on a screenshot of the property of the extractor.

sxu (BOB member since 2012-05-07)

6

I think you missed the screenshot 8)

ganeshxp :us: (BOB member since 2008-07-17)

7

Here it is…

S
0FI_AP_4 extractor.zip (18.0 KB)

sxu (BOB member since 2012-05-07)

8

Hoo managed to look at this now.

I am able to filter the records. However it reads all the records which is expected!!! and then applies the WHERE Filter on it.

I am on DS 14 SP1

ganeshxp :us: (BOB member since 2008-07-17)

9

Where do you do the filter, in the query transformation or somewhere else? What do you mean it loaded everything and then filtered? So the fitler is not happening in the where tab of the Query tranformation?

Thanks,
S.

sxu (BOB member since 2012-05-07)

10

Okay
0FI_AP_4 --> QRY_FILTER --> TARGET TABLE

So for “0FI_AP_4 --> QRY_FILTER” it will bring n number of records in source and then it applies the filter when it does “QRY_FILTER --> TARGET TABLE” This is what will happen in normal table loads also.

Are you having this in a ABAP Dataflow to enhance it or something?

ganeshxp :us: (BOB member since 2008-07-17)

11

If you are using query, I assuem you are using the “where” tab to filter? Could you share what’s in you where tab? Are you loading this as init full load?

I am only using extractor, no abap dataflow. Is this is how you did?

Thanks,
S

sxu (BOB member since 2012-05-07)

12

Yes exactly. And I am also applying a filter on BELNR.
And also doing the Full Load

ganeshxp :us: (BOB member since 2008-07-17)

13

What make you to say that it first loaded everything? Where do you see it loaded everything? And when it started filtering on just that document?

Thanks,
S.

sxu (BOB member since 2012-05-07)

14

Haha. First it reads all data and applies the filter. If you would have a simple job, like the one I had posted, you would see 3 entries in the monitor…2 would show a number of all records and 1 will show the filtered records.

ganeshxp :us: (BOB member since 2008-07-17)

15

Keep in mind, a filter in a query can be pushed down into the Extractor if

  • The Extractor was imported as in “Query” mode, not CDC mode
  • The Extractor has the capability to filter on the given column

In all other cases it reads what it is supposed to read and the query filter condition is executed in the engine. Still, just the rows matching the condition should arrive in the target.

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

16

Thank you both for your replies.

Now I do see how it was filtered. It first extracted everything and then filtered in the Data Service query transform. I was expecting the filtering should be done at the extractor level so ultimately reduced the size of the data to be extracted. I guess it is not the same as BW on how data source firter condition works, right?

Thanks,
Sharon

sxu (BOB member since 2012-05-07)

17

In BW you are limited, that makes things less flexible but easier.

If you want to achieve what you are trying, then import the Extractor into your datastore and select the Query mode. Then the Extractor is treated like a select statement on a table and the query filter condition is merged if possible. In case of a delta you cannot restrict the data to one document and expect all delta records to be received. In BW you would get an error then.

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

18

Thank you. This seems to be a better option if one nees to limit # of row to come across.

Just to clarify, neither fitering on the query transform where tab nor using the select statement in the extractor itself will restirct the delta to be loaded. This means if we have filter only loads one quarter worth of data, the delta will still load the changes on the other quarter. Am I right?

Thanks so much in advance!
S

sxu (BOB member since 2012-05-07)

19

Not sure I understand your question but let me put it this way:

CDC Mode:

  • Provides you with delta
  • filter conditions can be specified in the extractor properties/same dialog as when you do the import of the extractor
  • filter conditions apply to initial and delta, e.g. when you use the filter SALES_CHANNEL=1 that’s what you get, at initial load all orders of SALES_CHANNEL=1 and in delta all changes with SALES_CHANNEL=1
  • where clauses in the query are executed after reading, but keep in mind, the rows you have thrown away you will not get again (except you recover last delta run)

Query Mode:

  • no delta
  • no filter condition at the extractor itself
  • where clause in the query is tried to be pushed down into the Extractor if it supports that

Also remember, you can import the same Extractor multiple times with different project names. So you could have instance one you loaded the current quarter of data plus all changes - and these happen on current data only not years back. And the second instance is a query-mode instance, one where you read the past quarters one after the other to load the historical data step by step.

Clear?

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

20

Thank you! Everything become a lot clear now.

S.

sxu (BOB member since 2012-05-07)