Skip to Content

Extracting data from SAP ECC

Many SAP Data Services (DS) applications use SAP ECC data as a source. It now happens that DS supports multiple mechanisms to extract data from SAP ECC. Which one to choose? There often is a preferred method, depending on the functionality required and on the capabilities offered within the actual context of the source system. In this blog, I discuss all different options.

Start with configuring a data store for the SAP source system:

Figure 1: SAP Data store definition


Mandatory settings are User Name and Password, the name (or IP address) of the SAP Application server, the Client and System (instance) number.

1/. “Regular” data flow

When extracting from a single table, do that in a standard data flow. Import the table definition from the Metadata Repository in the Data store Explorer and use it as source in a data flow.

An extraction from the KNA1 customer master data table looks like this:

Figure 2: Data flow – extract from KNA1


Note the little red triangle in the source table icon. It’s an indication of a direct extract from the SAP data layer.

This is the simplest method. No special measures are necessary at the level of the source system. The extraction will run as a SAP dialog job. Note that there is a time-out setting (system-configurable, typically 10, 30, 60 minutes) for dialog jobs. The DS job will fail when data extraction takes longer.

The where conditions are pushed down to the underlying database. This is beneficial for the job performance. Also, make sure to extract only the columns that are really required. The extraction process duration is linearly related to the data volume, which equals number of records * average records size. The less data is transferred, the faster the DS job runs.

Figure 3: Query transform – extract from KNA1, excluding obsolete records

Figure 4: Generated SQL code – the where-clause is pushed down


This approach is especially beneficial when implementing incremental loads. When the source table contains a column with a last-modification timestamp, you can easily implement source-based changed-data capture (CDC). Keep track of the timestamps you used in the previous incremental extraction (use a control table for that), initialize global variables with those values and use them in the where-clause of your Query transform.

Figure 5: Query transform – extract recently created or modified records from MARA

Figure 6: Generated SQL code – source-based CDC


2/. ABAP data flow

Although where-conditions are pushed down to the underlying database from a normal data flow, the joins are not (sort and group-by operations aren’t either!), often leading to abominable performance, especially when dealing with larger data volumes.

When you want to extract material master data from MARA and supplement each record with the English material description from MAKT, you can build a data flow like this:

Figure 7: Data flow – extract from MARA and MAKT

Figure 8: Query transform – join MARA and MAKT on MANDT and MATNR columns

Figure 9: Generated SQL code – no join

DS generates two SQL statements. It first extracts all current records from MARA. And then for each individual record it retrieves the corresponding English description (MATNR = AIVariable_1 and MANDT = AIVariable_2). That approach leads to as many round-trips to the underlying database as there are records in the MARA table! It is only a valid one when dealing with smaller sets of data.

You may improve performance by changing the properties of the source tables.

The default settings are:

Figure 10: Default source table properties


Making MARA the driving table (by giving it a higher Join rank than MAKT) and caching MAKT in memory leads to a completely different generation of SQL code, without singular round-trips to the database. The MARA table streams through the data flow, the MAKT table is cached, and the join is resolved in DS memory.

Figure 11: Modified source table properties

Figure 12: Generated SQL code – cache MAKT


The feasibility of this approach is impacted by 2 parameters:

  • The amount of memory available for caching
  • The time it takes to cache the MAKT table

This works perfectly for smaller tables. But it’s not a well performing solution either when MAKT and MARA are too large

The recommended solution for extracting from a join of SAP tables is through the use of an ABAP data flow.

Figure 13: ABAP Data flow – extract from MARA and MAKT


DS generates ABAP code corresponding to the properties of the source tables and the logic of the dataflow. The table with the highest Join Rank becomes the driving table. Also in this case, the duration of the extraction process is linearly related to the data volume: the less data is transferred, the faster the DS job runs.

Figure 14: Generated ABAP code


The ABAP code is pushed to the SAP system and executed there. The program results only are sent back to DS for further processing. This approach only works when the SAP system is open for development! Also, make sure that:

  • The ABAP execution option is set to Generate and Execute.
  • The Execute in Background (batch) property is set to Yes to avoid the time-out in SAP dialog jobs.
  • The Data transfer method is set to RFC. The RFC destination must be defined in the SAP system. The other Data transfer methods are there for upward-compatibility reasons only and must not be used anymore. They all lead to inferior performance.

Figure 15: SAP Data store definition


To execute the same DS job in non-development tiers of your landscape, transport the ABAP program from DEV to TST, PRD… first. Set the ABAP execution option to Execute Preloaded and run the DS job. It will not generate the ABAP code again, but execute the transported code.

ABAP data flows are also a convenient solution for implementing incremental loads for ECC tables that do not contain a last-modification timestamp column. Inserts and modifications in KNA1 are registered in the CDHDR and CDPOS tables. Use an ABAP data flow to join KNA1 to those tables. Make sure CDHDR gets the highest Join Rank, KNA1 the lowest, in order to get the most efficient code generated. And include the where conditions to:

  • Filter out current customers
  • Get the recently modified records only
  • From the correct entries in the log tables

Figure 16: ABAP Data flow – extract from KNA1

Figure 17: Query transform – join KNA1 with CDHDR and CDPOS

Figure 18: Query transform – extract recently created or modified records from KNA1

3/. SAP Extractor

SAP Extractors are tailored for BW Business Content. They contain all the logic for common business transformations, possible aggregations, and also how to identify changes already, so they are very suitable for implementing incremental loads.

The DS Extractor feature is built on top of the Operational Data Provisioning (ODP) API. DS supports all types of ODP sources supported by the ODP API, including CDC functionality.

In a regular data flow, DS uses RFC to call the ODP data replication API. The where-conditions are not pushed down to the extractor. That means all data are pulled from the extractor and filtering is done afterwards in DS. Import the ODP object definition from the Metadata Repository in the Data store Explorer.

Figure 19: Import extractor 0CUSTOMER_ATTR


Make sure to set the Extraction mode to Query. Then use it as source in a data flow. An extraction from the 0CUSTOMER_ATTR ODP object looks like this:

Figure 20: Data flow – extract from 0CUSTOMER_ATTR


If you want to extract a minor subset of the data only, use the ODP object as source in an ABAP data flow.

Figure 21: ABAP data flow – extract from 0CUSTOMER_ATTR


Add the where-clause to the Query transform.

Figure 22: Query transform – extract current American customers from 0CUSTOMER_ATTR


DS generates the ABAP that calls the ODP data replication API. The generated code contains the logic for filtering out unnecessary records.

Figure 23: Generated ABAP code


Implementing CDC is really a piece of cake for those extractors that are “delta-enabled”. Make sure to set the Extraction mode to Changed-data capture (CDC). When import the ODP object.

Figure 24: Import extractor 0PROJECT_ATTR


Then use it as source in a data flow. No need to add any time-based condition in the where-clause. The extractor logic will guarantee that only new and modified records are passed to DS. Just make sure that the Initial load property of the ODP object is set to No. Only set it to Yes when you want the target table to be re-initialized.

Figure 25: ODP object properties


Include a Map_CDC_Operation transform to automatically synchronize the target table with the source object. The transform will translate the Row Operation value into the corresponding DS row type:

  • I > Insert
  • B & U: before and after-image of an Update
  • D > Delete

Figure 26: Data flow – delta extract from 0PROJECT_ATTR


4/. ECC function

DS can call RFC-enabled ECC functions returning tables as data flow sources, as well. If a standard ECC function is not RFC-enabled, you need an RFC-enabled wrapper function that passes the parameters to the standard function, calls it and forwards the results to DS.

You can only import a function’s metadata by name. Call it from a query transform by selecting New Function Call… from the pop-up menu in its output schema. Select the function from the ECC data store. Define Input Parameter(s) and select Output Parameter. The function call is added to the output schema.

Figure 27: Query transform – call an ECC function


Then unnest the return results in a next Query transform before writing it into a target table.

Figure 28: Query transform – unnest the function output schema

5/. SAP LT (SLT) Replication Server

DS can use SLT Replication Server as a source. This is a very neat and elegant way to build CDC jobs in DS. Working with SLT objects is similar to the way DS works with SAP extractors.

Define the SLT data store like any other SAP data store. Just make sure you select the right ODP context in the definition.

Figure 29: SLT Data store definition


You can then import the tables you need to extract data from.

Figure 30: SLT table metadata


Use the ODP object as source in a data flow. The first time the data flow is executed it will do a full extract of the underlying table. All successive runs will automatically perform an incremental one and only forward the delta.

Figure 31: Data flow – delta extract from MARA through SLT


6/. IDOC

DS real-time jobs can read from IDOC messages and IDOC files. DS batch jobs can only read from IDOC files.

Import the IDOC metadata definition by name from the SAP data store. Use the IDOC as a file source in a data flow within a batch job.

Figure 32: Data flow – delta extract from IDOC File Source


Double-click on the IDOC icon to open its definition and specify the name of the IDOC file. You can use wildcards (? and *) or list multiple file names separated by commas if you want to process multiple files in a single data flow.

Figure 33: IDOC properties


Generate the IDOC file(s) from SAP and run your DS job.

You must be Logged on to comment or reply to a post.
  • Hello Dirk,

    how did you created the DEBMAD06.txt in "Figure 33: IDOC properties" ?

    I tried it with .xml files downloaded form SAP GUI but no success here.

    Thank you!

    • I didn't, I received it :).

      Google "how to generate an idoc file from sap", and you'll find literally thousands of hits.

      • Hi Dirk,

        Is this IDOC file containing actual data or structure only? Actually I tried with IDOC (ORDERS05) file generated from SAP (tcode - WE63) and used the same but no success. Also I tried with some XML source file aligned with the same IDOC structure but there also no success. Error message is below

        Processing the IDoc from the file <G:/BODS/Batch_files/89d9448_1754HTML000003.xml> failed 
        because Data Services no longer supports its format. Regenerate the IDoc file in SAP and 
        replace the file.

        Kindly suggest if I am missing anything here. Thanks in advance.

        Thanks & Regards


  • Can you please explain a bit on the connection details between SAP ECC and BODS?

    We have a requirement for connecting BODS to SAP ECC for ODP extraction.

    I am very thankful if you could share the connection details, any pre-requisites required and what sort of authorisation the service RFC user should have?

    Please explain a bit.



  • /
    • Make sure that your input schema contains the attributes and/or sub-schemas to map to the input parameters of the function.

  • Hi Dirk,

    I am trying to data from extractor (delta enabled). Full load is working fine where as delta load is not working properly.Selcted Change-Data-Capture option while importing the extractor and passed 'No' value Initial Load property. Any inputs for this error?

    The delta extraction is keep on running with out any error in BODS console


  • Hi Dirk,

    I have been trying to implement the combination of SLT with DS. I have read through your blog and followed the below document.

    I have used a separate SLT server to fetch data from SAP system. I am facing some issues and request you to throw some light on it.

    – I am seeing the tables of SLT system in DS system. Shouldn’t I be seeing only the ODP  objects?

    – If I search for a table in external metadata of datastore it won’t show any results when the table is actually present under ODP objects. I imported the metadata of a table  and tried viewing data, it will show the status” fetching data” but will never show any.

    I can see entries in ODQMON in SLT. Tried creating a job in DS to fetch data and write it to a target system. The table gets created in the target system without data.

    On further investigation it showed that in SLT, ODQ jobs are getting released but not running. Do you see any reason why this happens?



    • /
    • Hi Liju,


      I am also working on connecting SLT to BODS but unable to find any proper document on it. Could you please suggest me some content and help me to establish the same.


      Thank you in advance.


    Hi ,

    I am working on "BODS reliable asynchronous web service call" but getting

    error while communicating with server".

    BODS Error log :-

    1012410508XML-Unknown error communicating with server.

    Error at target system -

    " SRT: plain soap: reliable messaging (rm) configured, but no message id and no wsrm assertion provided. "

    is their any way to populate Header node (with Action, Addressing, unique Message ID) while calling Web Service from BODS.

    I found on the target system that some extra elements that are getting on to Request message when we trigger that web service from SOAP UI by selecting those WS-A elements in case of Async Reliable communication.

    usually, Request structure starts from Body but in this case we have extra node Header

    when we are connecting this web service using SOAP UI

    BUT it is only working when we are enabling this WS-A checkbox in SOAP UI , not working with BODS designer.


    any help on this ? please let me know if you have any documents related to reliable asynchronous web service call .


    Rubal Taneja

  • Hi DIrk,

    Thanks for the reply. The issue was the SLT work process and it is resolved.

    I have another query

    Every time I schedule a job from DataService, I am able to extract data from ODQ of SLT.

    Will I be able to extract data from ODQ on a real time basis? i.e s as soon as SLT writes to ODQ, Data service extracts it from ODQ and writes to a target?






      Almost. You can make a DS job poll for changes. Embed your data flows in an eternal loop, reading from the queues and writing to the target.

  • Please answer the below question

    if I want to extract the only delta records into HANA from SAP or Non-SAP sources through BODS, What is the process and which transformation we need to use in BODS?

    • In general you can consider 5 following options:

      • Use last_modification timestamps in the source tables. Keep track of the timestamp(s) that you used in the previous incremental extraction (use a control table for that), initialise global variable and use them in the where-clause of your Query transforms. If there's no timestamp available, but the records are numbered sequentially, you can apply the same approach using the sequence numbers.
      • Use a log table. Many systems log modifications in so called log-tables. The actual table modified does not contain the timestamp, but the log table does. Join the tables and proceed as above.
      • Use the built-in CDC-mechanisms of the source database;
      • Use specific CDC software, like SLT Replication Server.
      • If none of those apply, do a full load, keep the copy of the previous run, and calculate the delta yourself. Then apply transformations to the delta only.

      With SAP as a source, there’s a 6th option:

      • Leverage delta-enabled Business Content Extractors

      As of the official documentation, SAP Data Services Supplement for SAP, section Source:

      In an ABAP data flow, a source can be another ABAP data flow, an SAP application table, a file located on the SAP application server, or an SAP ODP source.


  • Hi Dirk,

    Thanks for this write up, very helpful.  I'm trying to use the "Regular" method above to pull delta information from the faglflexa table.  As I understand it from our functional team, the only "last modified" type dates I can rely on for this table live at the header level - bkpf.  What I'm hoping to do is inner join the two tables directly in the data flow and use the where clause to filter on the dates I need, but what I'm seeing is that it generates two queries that pull everything from both tables.  I'm wondering if what I'm trying to do is even possible?  If so, how can I get it to push down the join and date filtering condition?






      As I have tried to explain


      Although where-conditions are pushed down to the underlying database from a normal data flow, the joins are not (sort and group-by operations aren’t either!)


      joins are only pushed down from ABAP data flows.

  • hi Dirk, great article

    I'm working on something similar at the moment as you described in this blog, I try to consume ODP extractor and save data in the table, but it's not working as expected

    My Z-datasource is a table based with delta enables on a numeric pointer (table MDKP), I've imported it with extraction method CDC. When I execute the job with Init set as Yes, I see the init request in ODQMON and data are correctly loaded, unfortunately when I switch the init to No (I want to receive delta), although there are new units in the monitor, the job doesn't raise new request and seems data are loaded from pagable cache
    I've switched off all statistics

    do you know what can be the problem?




  • Hi Dirk,

    We are using ABAP dataflows in our project, we have applied some filters on date column.

    To load some missing records, we have removed the filters in ABAP dataflow and ran the job without generating ABAP code, it is execute preloaded set in datastore, but strangely that missing records due to filters are loaded into target. We need to know, is the abap code generation not required if we change anything in abap dataflow.

  • Thank you for putting everything into a simple single document. Very helpfull.

    Have a question.

    1.  Have found that when I set up the Initial Load of data that in the  odqmon, all  the data has been extracted but data services is still loading this data. I'm trying to see if I can speed up that process. In my situation, I have 1 million rows that took about 10 minutes to show in the odqmon but data services is still processing and wont be finished for another 30 minutes based on the numbers. Is there a limitation on the number of rows or bytes of data that can be extracted between SAP and Data Services?
  • Hi Dirk,

    First of all i would like to thank you for sharing this useful informaion.


    Dirk i have few question can you please help me on this.

    1) can we call abap report/programme in bods if yes then how please explain .

    2) can we call function module through scripts in BODS.

    • Any program, report, function... you can call from the command line, can also be called from a DS script using the exec() built-in function.

  • Hi Dirk,

    thank's a lot for this post.
    I try to implement the combination of SLT with DS.
    I create the datastore for the SLT and i import correctly the ODP Object in this Datastore:

    I try to view data of one of this ODP Object but i have reported this error:

    I try to execute a job, where the source is the ODP Object TVKO_DWHE, but when execute this job i have reported this error:

    Data Services Management Console

    (14.2) 03-21-18 17:49:57 (E) (128900:2690184960) VAL-030977: |Data flow TDF_TVKO_STG|Reader Qry_Extract_TVKO ODP source returned error: <E: MESSAGE - DataSource SLS002~TVKO does not exist in version A. MESSAGE_V1 - SLS002~TVKO. MESSAGE_V2 - A.>. (14.2) 03-21-18 17:50:03 (E) (128889:3762796352) VAL-030977: |Data flow TDF_TVKO_STG|Reader Qry_Extract_TVKO ODP source returned error: <E: MESSAGE - DataSource SLS002~TVKO does not exist in version A. MESSAGE_V1 - SLS002~TVKO. MESSAGE_V2 - A.>.

    What's the reason why I have this error?

    Thank's a lot.



  • Hi Dirk,

    How to generate  SAP Transport  (Request Id and Task Id)  by generating ABAP code  and upload generated abap code to sap server.



  • Hi Dirk,

    With SLT is possible identify change records in S/4HANA directly without use of the configuring in RSA1 ?

    And with BODS reading the SLT ODP/ODQ queue directly ?

    Thank you


  • Hi Dirk,

    thank's a lot for the post.

    I have a problem with the execution of job from a datasource in delta 2lis_03_bf, i want repeat a failed extraction in delta i set in dataflow the extractor with a global variable $G_DATETIME data type datetime, i attach the image:

    I want execute the job repeating a delta request. In odqmon transaction i have two request for 2lis_03_bf  and i execute the first of two request. I take the value of the pointer filter in table RODPS_REPL_RID with the value of the first composite request. When i execute the job with this POINTER value i extract the data from the second request of odqmon, but i used the pointer of the first.

    How i retrieve the delta from the oldest request in odqmon? We need a documentation for this argument.

    Thank's a lot for the help.

    Best regard


    • You can only redo the last delta load, not the one(s) before.

      From the documentation:


      Extract from datetime

      For changed-data capture (CDC), optionally enter a global variable of type datetime that defines from when to extract changed data.

      If the datetime value is the same as or before the value from the last execution, Data Services repeats the changed-data extraction.

      If the datetime value is later than the value from the last execution, Data Services returns the new data.


  • Hi Dirk,
    we have a BODS job that extract from 2lis_12_vcitm. This job is gone in error for system problem.

    If we want repeat the extraction in BODS jobs, we add a global variable type of datetime ‘$g_DATETIME’ into field “Extract from datetime” of datasource:

    then we go to the transaction ODQMON in ECC system and we catch the last composite request of 2lis_12_vcitm extractor:

    that we want repeat the delta execution.

    We go in the content of the table RODPS_REPL_RID filtering with this composite request retrieved and take the value of the field pointer:

    this value is insert in the execution of the job in the global variable of type datetime, this value is writed without “braces”:

    The value of the global Variable ‘$g_INITIAL_LOAD’ = ‘R’ represent that the job executes the dataflow that contains the global variable ‘$g_DATETIME’ into field ‘Extract from datetime’.

    This execution doesn’t repeat the extraction, but it executes a new delta of this extractor.
    This wrong execution is represented from the creation of a new request in transaction ODQMON and not the repetion of the request underlines over:


    Best regards.


    The value of the POINTER field is 2019-03-10 10:20:09? Does it contain the "datetime that defines from when to extract changed data"? It isn't, because in the last run data was extracted from 2019-03-10 01:00:11.

    "If the datetime value is later than the value from the last execution, Data Services returns the new data."

    • Hi Dirk,


      we try to replicate your case in quality system.
      We have a request extraction delta for 2lis_12_vcitm datasource:

      We try to repeat this extraction delta with the value contained into field "Lower Limit for TSN", in this case is {2019-03-19 10:00:33 002961 CET}.
      This job's execution goes in error:

      Which value we take to repeat this delta extraction?

      Thanks a lot.

      Best regards,

      • You can only rerun the last delta load. I am afraid it’s too late, now, because you’ve executed another one in the meantime.

        I always keep track of Extract from datetime in DS, I store the previous value together with the execution status in a control table. In normal circumstances, when the previoius run was successful, I just use sysdate. When it failed, I use the one from the control table.

  • Hi Dirk,

    And thanks for this blog.

    Would you be able to comment on the data volumes that each of these options especially via ODP extractors, SLT and IDOC could handle. What are the limitations and how one can overcome these limitations.

    With IDOC's especially are there any limitations regarding the IDOC statuses getting messed up with network issues ?

    Many thanks

  • There are no tight data volume limitations with ODP and SLT. Obvioulsy, all depends on the underlying infrastructure.

    IDOCs as input are only supported in real-time jobs, typically dealing with a record at the time.

  • Hi Dirk,

    Can we load data from SAP DS (BODS) directly into the staging table (/1LT/CF_E_HEADER, /1LT/CF_E_ACCT  etc.) of SLT?

    If yes can you please give me some idea and if no, how can we possibly add data into staging table if we have non-sap system as our source.

    Can you help me on this asap.

    Thanks in advance !!

  • Hi Dirk

    Am so glad !! You aricle confirms that the way i have pulled from ECC so far is ok (using ABAP DF & Tables/Functions).

    We have now moved to Hana database and have created a few calculation views. How can i source from these calculation views ? Additionally, can i source using Native CDS views ?

  • Hi Drik,


    I have an issue with one of my job it fails sometime with error

    Cannot convert data <T14Y2436> into type <DECIMAL> in file  <SapDB::srcDB_table_batch_stream>, row number <0>, column number <23>. and sometimes it executes successfully any suggestion on how to fix this issue.

    My second query is we are using non-ABAP dataflow for extracting Purchase order tables like EKKO, EKPO, EKEB, EKET & EKES we have extract all the columns for this tables and dump complete data to Oracle DB is there any way to improve the performance. Currently, we have enabled an Array fetch size to 5000.

    • Using /nse16, can you validate the source table if it contains the text "T14Y2436" in this decimal field? What is the ABAP datatype actually? Really decimal and not one of the character based numeric data types?


      Performance you can increase via three ways:

      • Optimize the throughput of a single stream - array fetch size and the such like you said.
      • Analyze the bottleneck and do something about it. If the reading is slow, increasing the load performance will not make a difference and vice versa.
      • Go into parallel/partitioned processing.
  • Hi Dirk,

    we have a problem with extraction of 2LIS_12_VCITM from ECC to BODS, using ODQMON queue.

    The odqmon queue has more cases of DELIVERY without Document Number like the following example:

    We want try do debug the execution of job of BODS to understand which program or job call in ECC to create a new ODQMON request, with this problem.

    Do you know the name of program of job in ECC called from BODS job when it extracts from 2LIS_12_VCITM extractor?

    Thanks a lot.

    Best regards.


  • Hi Dirk

    I just wanted to ask if you have to have specific licenses to extract data from SAP ECC / BW to Non SAP related systems