Extract data from SAP ECC (2023)

Many SAP Data Services (DS) applications use SAP ECC data as a source. Now it turns out that DS supports several mechanisms to extract data from SAP ECC. Which to choose? There is often a preferred method based on the functionality required and the features offered in the actual context of the source system. In this blog I discuss all the different options.

Start by setting up a data warehouse for the SAP source system:

Figure 1: SAP Data Warehouse Definition

The mandatory settings are the username and password, the name (or IP address) of the SAP application server, the client and the system (instance) number.

1/. "normal" data flow

If you're pulling from a single table, you're pulling in a standard dataflow. Import the metadata store table definition into the data store browser and use it as a source in a data flow.

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

Figure 2: Data flow: KNA1 extraction

Note the little red triangle on the source table icon. It is an indication of a direct pull from the SAP data layer.

This is the easiest method. No special measures are required at the source system level. The extraction runs as a SAP dialog job. Note that there is a timeout setting (system configurable, typically 10, 30, 60 minutes) for dialog jobs. The DS job will fail if it takes longer to extract the data.

Where the conditions are sent to the underlying database. This benefits work performance. Also, make sure you only extract the columns you really need. The duration of the extraction process is linearly related to the amount of data, which is equal to the number of records * average record size. The less data is transferred, the faster the DS job will run.

Figure 3: Query transformation: KNA1 extract, without stale data sets

Figure 4: Generated SQL code - where the clause moves down

This approach is particularly beneficial when implementing incremental loads. If your source table contains a column with a last-modified timestamp, you can easily implement source-based Change Data Capture (CDC). Keep track of the timestamps you used in the previous incremental extraction (use a control table for this), initialize the global variables with these values, and use them in the where clause of your query transformation.

Figure 5: Query transformation: pull newly created or modified records from MARA

Figure 6: Generated SQL Code - Source Based CDC

two/. ABAP data flow

While conditions are sent from a normal data flow to the underlying database, joins are not (nor are sort and group operations!), often resulting in abominable performance, especially when dealing with of large amounts of data.

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

Figure 7: Data flow - MARA and MAKT extract

Figure 8: Query Transformation: Join MARA and MAKT with MANDT and MATNR Columns

Figure 9: Generated SQL code - no union

DS generates two SQL statements. First extract all current MARA records. And then, for each individual record, it retrieves the corresponding English description (MATNR = AIVariable_1 and MANDT = AIVariable_2). This approach results in as many round trips to the underlying database as there are records in the MARA table! It is only valid when dealing with smaller data sets.

You can improve performance by modifying the properties of the source table.

The default settings are:

Figure 10: Default source table properties

Make MARA a mobile table (give it morejoin the rankthan MAKT) and caching MAKT in memory results in a completely different generation of SQL code, with no round trips to the database. The MARA table flows through the data flow, the MAKT table is cached, and the join is resolved in DS storage.

Figure 11: Changed source table properties

Figure 12: Generated SQL code: MAKT cache

The feasibility of this approach is influenced 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 also not a good solution if MAKT and MARA are too big.

The recommended solution to extract a join from SAP tables is to use an ABAP dataflow.

Figure 13: ABAP data flow - MARA and MAKT extract

DS generates ABAP code that conforms to the source table properties and data flow logic. The table with the largest.Enter the leaderboardit becomes a driving table. Also in this case, the duration of the extraction process depends linearly on the amount of data: the less data is transferred, the faster the DS job will run.

Figure 14: Generated ABAP code

The ABAP code is sent to the SAP system and is executed there. Only the results of the program are returned to DS for further processing. This approach only works if the SAP system is open for development! Also make sure of the following:

• oABAP execution optionis set tobuild and run.
• oRun in the background (batch)The property is defined asSimto avoid timeouts in SAP dialog tasks.
• odata transfer methodis set toRFC. oRFC destinationmust be defined in the SAP system. The other data transmission methods only exist for compatibility reasons and should no longer be used. All result in lower performance.

Figure 15: SAP Data Warehouse Definition

To do the same DS work on non-evolutionary layers of your landscape, first transfer the ABAP program from DEV to TST, PRD... Put thatABAP execution optionProrun preinstalledand run the DS job. It will not regenerate the ABAP code but will execute the transported code.

ABAP dataflows are also a convenient solution for implementing incremental loads for ECC tables that do not contain a last-modified timestamp column. Insertions and modifications in KNA1 are recorded in the CDHDR and CDPOS tables. Use an ABAP data flow to link KNA1 to these tables. Make sure CDHDR gets the highest valueEnter the leaderboard, lower KNA1 to get the most efficient generated code. And include where clauses for:

• Filter current customers
• Receive only recently modified records
• Of the correct entries in the log tables

Figure 16: ABAP data flow - KNA1 extract

Figure 17: Query Transformation: Joining KNA1 with CDHDR and CDPOS

Figure 18: Query Transformation - Extract newly created or modified records from KNA1

3/. SAP extractor

SAP extractors adapt to BW Business Content. They contain all the logic for general business transformations, possible aggregations, and also how changes can be detected, making them well-suited for implementing incremental loads.

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

In a normal data flow, DS uses RFC to call the ODP Data Replication API. Conditions, if not, are sent to the extractor. This means that all data is pulled from the extractor and additional filtering is done in DS. Import the ODP object definition from the metadata store into the data store browser.

Figure 19: 0CUSTOMER_ATTR import extractor

Make sure you have theextraction modeProPetitions. Then use it as a source in a dataflow. An excerpt from the ODP 0CUSTOMER_ATTR object looks like this:

Figure 20: Data flow - extract from 0CUSTOMER_ATTR

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

Figure 21: ABAP data flow - extract from 0CUSTOMER_ATTR

Add the where clause to the query transformation.

Figure 22: Query transformation: extract current US customers from 0CUSTOMER_ATTR

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

Figure 23: Generated ABAP code

The implementation of CDC is quite easy for extractors who are "delta-aware". Make sure you have theextraction modeProChange Data Collection (CDC). When importing the ODP object.

Figure 24: Import extractor 0PROJECT_ATTR

Then use it as a source in a dataflow. There is no need to add a time-based condition in the where clause. The extractor logic ensures that only new and changed records are forwarded to DS. Just make sure thatinitial feeproperty of the ODP object is set tono. just set it upSimif you want the destination table to be restarted.

Figure 25: ODP object properties

Add a Map_CDC_Operation transformation to automatically synchronize the destination table with the source object. The transformation translates the value of the row operation to the corresponding DS row type:

• me > paste
• B&U: Image before and after an update
• D > Exclude

Figure 26: Data flow: delta extraction of 0PROJECT_ATTR

4/. ECC function

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

You can only import metadata for a role by name. Call it a query transformation by selecting New Function Call... from the popup menu in its output schema. Select the ECC data storage function. Define the input parameters and select the output parameter. The function call is added to the output schema.

Figure 27: Query transformation: Calling an ECC function

Then, in a next query transformation, deinterleave the returned results before writing them to a destination table.

Figure 28: Query transformation: output schema of the deinterleave function

5/. SAP LT (SLT) Replication Server

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

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

Figure 29: Definition of SLT data storage

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

Figure 30: SLT table metadata

Use the ODP object as a source in a data flow. When the data flow runs for the first time, a full extraction of the underlying table is performed. All subsequent executions automatically run an incremental program and only forward the delta.

Figure 31: Data flow: delta MARA extraction by SLT

6/. IDOC

DS realtime tasks can read IDOC messages and IDOC files. DS batch jobs can only be read from IDoc files.

Import the IDoc metadata definition by name from the SAP data warehouse. Use the IDoc as the file source in a data flow in a batch job.

Figure 32: Data Flow - IDoc File Source Delta Extraction

Double-click 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 stream.

Figure 33: IDoc Properties

Generate the SAP IDoc files and run your DS task.