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:

Extract data from SAP ECC (1)

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:

Extract data from SAP ECC (2)

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.

Extract data from SAP ECC (3)

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

Extract data from SAP ECC (4)

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.

Extract data from SAP ECC (5)

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

Extract data from SAP ECC (6)

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:

(Video) Extraction from SAP ECC to SAP Data Services | Extract from SAP ECC to Database | SAP BODS Tutorial

Extract data from SAP ECC (7)

Figure 7: Data flow - MARA and MAKT extract

Extract data from SAP ECC (8)

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

Extract data from SAP ECC (9)

Extract data from SAP ECC (10)

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:

Extract data from SAP ECC (11)

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.

Extract data from SAP ECC (12)

Extract data from SAP ECC (13)

Figure 11: Changed source table properties

Extract data from SAP ECC (14)

Extract data from SAP ECC (15)

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.

Extract data from SAP ECC (16)

(Video) SAP BW Data Extraction in SAP BWBI |Data Extraction SAP BW from Source ECC|Data Extraction Options

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.

Extract data from SAP ECC (17)

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.

Extract data from SAP ECC (18)

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

Extract data from SAP ECC (19)

Figure 16: ABAP data flow - KNA1 extract

Extract data from SAP ECC (20)

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

Extract data from SAP ECC (21)

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.

Extract data from SAP ECC (22)

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:

Extract data from SAP ECC (23)

Figure 20: Data flow - extract from 0CUSTOMER_ATTR

(Video) ECC Extraction for Master Data

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

Extract data from SAP ECC (24)

Figure 21: ABAP data flow - extract from 0CUSTOMER_ATTR

Add the where clause to the query transformation.

Extract data from SAP ECC (25)

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.

Extract data from SAP ECC (26)

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.

Extract data from SAP ECC (27)

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.

Extract data from SAP ECC (28)

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

Extract data from SAP ECC (29)

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.

Extract data from SAP ECC (30)

Extract data from SAP ECC (31)

Extract data from SAP ECC (32)

(Video) Data Extraction using ODP from ECC to BW Part 1

Extract data from SAP ECC (33)

Extract data from SAP ECC (34)

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.

Extract data from SAP ECC (35)

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.

Extract data from SAP ECC (36)

Figure 29: Definition of SLT data storage

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

Extract data from SAP ECC (37)

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.

Extract data from SAP ECC (38)

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.

Extract data from SAP ECC (39)

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.

Extract data from SAP ECC (40)

(Video) ECC Extraction Introduction

Figure 33: IDoc Properties

Generate the SAP IDoc files and run your DS task.


What is the best way to extract data from SAP? ›

The recommended solution for extracting from a join of SAP tables is through the use of an ABAP data flow. DS generates ABAP code corresponding to the properties of the source tables and the logic of the dataflow.

How to extract data from SAP ECC? ›

Extracting Data in SAP ECC
  1. Open SAP Logon and connect to the SAP ECC system containing the data you want to import.
  2. To list the available activated extractors, you can use the Postprocess DataSources and Hierarchy (RSA6) transaction and choose Enter as indicated below:

How to extract data from SAP ECC to SQL Server? ›

Steps involved
  1. Create a New Data Store and give the credentials to connect to the ECC system.
  2. Select the Tables under the Data store and import the sap tables that are required to the Data services.
  3. Copy the SAP table data to staging tables in the SQL database and use the staged tables for further steps.
May 3, 2017

What are the three data extraction methods? ›

There are three main types of data extraction in ETL: full extraction, incremental stream extraction, and incremental batch extraction. Full extraction involves extracting all the data from the source system and loading it into the target system.

Can Excel pull data FROM SAP? ›

The CData ODBC driver for SAP uses the standard ODBC interface to link SAP data with applications like Microsoft Access and Excel. Follow the steps below to use Microsoft Query to import SAP data into a spreadsheet and provide values to a parameterized query from cells in a spreadsheet.

How do I extract data from SAP ECC to Excel? ›

You can use the icon for exporting as a shortcut. All you have to do is to click on the exporting icon, which is an arrow pointing to the right, then select the spreadsheet button. Once that happens, your SAP data will be exported to Excel without a problem.

Is SAP ECC obsolete? ›

It is also important to know that SAP ECC has reached the end of the line. SAP is planning to end support for SAP ECC by 2027.

Is SAP ECC outdated? ›

In early 2019, SAP announced it was planning to end maintenance for ERP Central Component and other legacy ERP products by 2025. After pushback from customers, SAP has extended that deadline and will continue maintenance through 2027, with an optional extension through 2030.

Does SAP have an ETL tool? ›

The SAP ETL tool automates data extraction, using OData services to extract the data, both initial and incremental or deltas. The tool can connect to Data extractors or CDS views to get the data.

How do I download all data from SAP? ›

The first thing is to click on the list menu. Once you do that, you should select the Export option and then the Spreadsheet option. This option works when you are in the full-screen mode where the SAP screen is displaying returned data lists.

How do I Export large data from SAP to Excel? ›

From the List drop down box click on Export then Local file. In the pop-up box, Select format Text with Tabs. Click on the match box. When the 'Save As' pop-up appears, save your document to your Desktop.

Can Tableau pull data from SAP? ›

Tableau supports live and extract connections to SAP BW data. With a live connection you will always see the latest data in your workbooks and dashboards.


1. How to Extract SAP data in Excel and other Formats - SAP Tutorials
(Quick Learning)
2. 33.SAP ECC Extraction Introduction Part 1
(Software Skills)
3. Data Extraction in SAP BW from ECC using Standard DS
(AK Verma)
4. Automate SAP Data Extraction with Excel VBA & SAP GUI Scripting - Minimal Coding Required
(Joel Ting)
5. Extract SAP Accounting data from SAP ECC & SAP S/4HANA using Alteryx
(DVW Analytics)
6. Data Extraction using ODP from ECC to BW Part 2
(Srinivas M SAP Analytics Tutorial)
Top Articles
Latest Posts
Article information

Author: Pres. Lawanda Wiegand

Last Updated: 12/29/2022

Views: 5457

Rating: 4 / 5 (51 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Pres. Lawanda Wiegand

Birthday: 1993-01-10

Address: Suite 391 6963 Ullrich Shore, Bellefort, WI 01350-7893

Phone: +6806610432415

Job: Dynamic Manufacturing Assistant

Hobby: amateur radio, Taekwondo, Wood carving, Parkour, Skateboarding, Running, Rafting

Introduction: My name is Pres. Lawanda Wiegand, I am a inquisitive, helpful, glamorous, cheerful, open, clever, innocent person who loves writing and wants to share my knowledge and understanding with you.