EPO Consulting Wiki - EPO Connector ETL: The Concept

This is an old version of this page. To the new page please click here

Das ist eine alte Version dieser Seite. Zur neuen Seite klicken sie hier

ETL Objects

• Any type of data which needs to be transferred is called an ETL object. An object can be a single table or view or CDS view, a collection of tables or a business object like master data or transaction data.
• All ETL Objects are defined in table /EPO1/ETLOBJ. An ETL Object is always defined by its Key. Optionally there can be a "Key Prefix" defined.
• All ETL Object entries must be inserted into the table /EPO1/ETLLOG by using static method /EPO1/CL_ETL_LOG->ADD_KEY.

ETL function principle

For any data which needs to be transferred, the relevant object keys must be added first into table /EPO1/ETLLOG by using static method /EPO1/CL_ETL_LOG->ADD_KEY.

The data transfer is based always on keys in table /EPO1/ETLLOG.

For SAP Inbound interfaces the data transfer is done with the 2 APIs getList (returning the keys only) and getData (returning the data). Additionally there is the getDefinition API for providing the structure of the interface and its fields.

For SAP Outbound interfaces the data transfer is done by the report /EPO1/ETL_SEND or report /EPO1/ETL_SEND_REALTIME, which are used as step in a SAP job.

Advantages of the concept of first adding object keys into table /EPO1/ETLLOG

• The same keys can be added multiple times. The data transfer will only be once per key.
• Object keys can be added at creation time, at change events or by a periodically scheduled job.
• APIs can be defined "SAP Inbound" or "SAP Outbound" using the same principle.
• ETL Objects can be defined for any data and any interface target.
• All interfaces do have same functionalities.
• Interfaces can be designed for one-time-delivery of data
• Interface data can easily be re-built by adding the relevant keys into table /EPO1/ETLLOG
• Data (keys) which is not in this table cannot be transferred. This way the full control of data leaving the SAP system lays within SAP.

Table /EPO1/ETLLOG

Table /EPO1/ETLLOG hold all object keys for all ETL objects.

Fill /EPO1/ETLLOG with a Report

ETL Object Keys can be added with simple reports.

EPO Consulting provides those reports for many data objects in the SAP system.

Customers can copy those reports and change the logic to their needs.

Note: Adding keys must be done with static method /EPO1/CL_ETL_LOG=>ADD_KEY.

Fill /EPO1/ETLLOG with a User-Exit

ETL Object Keys can be added by implementing a User-Exit.

EPO Consulting provides User-Exits for many data objects in the SAP system.

Customers can copy those User-Exits and implement it in their system.

By adding method /EPO1/CL_ETL_LOG=>RAISE_EVENT_EPO1_ETL those interfaces can transfer data in real-time.

Note: Adding keys must be done with static method /EPO1/CL_ETL_LOG=>ADD_KEY.

Fill /EPO1/ETLLOG with SAP Inbound getList API

ETL Object Keys can be added when the ETL SAP Inbound API getList is called. This is configured in customizing table /EPO1/ETLOBJ_IN, field FILL_LOG_AT_GETLIST ("Fill Log" Checkbox)

Note: Adding keys at runtime of API getList can slow down this API.

Option 1: ETL function with SAP Inbound Calls

Data can be transferred by using EPO Connector SAP Inbound calls. The APIs are getList, getData and getDefinition.

Testing can be done with a tool like Postman or SoapUI. EPO Consulting is providing Postman Collections.

Option 2: ETL function with SAP Outbound Calls

Data can be transferred by using EPO Connector SAP Outbound calls.

GET DATA service

GET DATA returns data for selected objects, if there is an entry in the ETL-log table. The API path (elements after EPO-service, EPO-operation) are analyzed and interpreted as

• ETL object kind (defined by table /EPO1/ETLOBJ)
• Prefix (optional, defined by table /EPO1/ETLOBJ)

Depending on the ETL object kind, a specific function module is invoked to return the object-specific data. The name of the function module is defined in table /EPO1/ETLOBJ_IN.

Main-entry function module: /EPO1/ETL_GETDATA

Import parameter:

• ETL_KEY_T: list of ETL-keys

Export parameters:

• EPO1OK: flag, if the processing was OK (without errors)
• EPO1MESSAGE: list of error messages
• EPO1EXP: list of object data, the structure depends on the object kind

SYNC service

In order to get out-of-sync data aligned again, it is possible to implement a sync service which compares the data in the ETL server against the data in SAP. If differences are found, the ETL key of changed/created/deleted objects will be stored into the ETL log table. With the next GetList / GetData cycle, the different data will be aligned properly.

Concept

The function module /EPO1/ETL_SYNC is configured to be accessible via webservice (see EPO customizing).

Import parameters:

• the ETL object name and the prefix are appended into the URL string
  e.g.: https://server.path.or.name/epo1soa/jsonhandler/epoetl/sync/ETLKostl/1000
• OBJECT_LIST: list of data pairs: ETL-key and timestamp of the last update in the ETL server, containing all available data for the current combination ETL-object/prefix
• FIRST_BLOCK: indicates, that the data transfer of the sync service is starting
• LAST_BLOCK: indicates, that the data transfer of the sync serice is finished - the data processing might start

For a small set of object, the sync-service may be invoked by one single call - containing the complete list of ETL-key/timestamp combinations. However, for a large data set, the data transfer might be split into several webservice calls, transferring a smaller block of data to SAP.

The data processing will be started after receiving the LAST_BLOCK - flag set to 'X' in the background.

Depending on the ETL object (see table /EPO1/ETLOBJ_IN), a special function module will be called, which collects all the available information from SAP (containing the ETL-key and the timestamp of the last change).

Afterwards, both collected data (from the ETL-server and from SAP) are compared. Differences will be written into the /EPO1/ETLLOG - table, such that the differences can be equalized during the next GetList/GetData processing.

Please note, that a 'first data preparation' is also possible, when the ETL-server has no data for a given ETL-object/prefix; just send an empty OBJECT_LIST, resulting in a completely filled /EPO1/ETLLOG - table.

Please note, that for performance reasons, the SYNC service should not be called too often.

The current SYNC-status (idle, data collection, data processing), timestamp and error message of the last SYNC-run and a small statistic of created/changed/deleted objects is stored in table /EPO1/ETLSYNC_ST and is returned as part of the GetList response.

object-specific SYNC - function module

There is a set of predefined EPO-SYNC function modules. However, it is also possible to write Your own function modules if You have special needs or newly created custom ETL-objects, which should be synchronized.

Please refer the existing function modules (e.g. /EPO1/ETL_ACCOUNT_SYNC, /EPO1/ETL_ASSET_SYNC, /EPO1/ETL_CUSTOMER, ..) how to select and process really fast all the available data from SAP.

Import parameters

• IV_ETL_OBJECT name of the ETL object
• IV_PREFIX value of the prefix (e.g. the BUKRS)

Export parameter

• EV_ERROR_MESSAGE only to be filled, when an error occurs, empty for successful run; this error message will be stored as sync-status in the table /EPO1/ETLSYNC_ST and will be returned at the next GetList for the same combination of ETL-object and prefix

Customizing table /EPO1/ETLOBJ_IN

Specify for each ETL object a SYNC - function module:

Customizing of inbound operation

In the EPO customizing

EPO Connector Configuration / Inbound Service Configuration / EPO Runtime / In: Maintain EPO Runtime service configuration

• specify an operation (e.g. 'sync') for the ETL service.

EPO Connector Configuration / Inbound Service Configuration / EPO Runtime / GFMC: Change processing FM for given operation

• map the service/operation to the function module /EPO1/ETL_SYNC

Examples of a webservice call

The example has been sent using Postman. Please use Your own SAP - servername. This example means, that the ETL server has only 1 cost center stored.. usually, the list would be much longer. Here, we would want to synchronize all cost centers for the company code (BUKRS) 1000, so the ETL-keys should contain only cost centers of the company code 1000.

Please note, that - depending on the field mapping - Your request might require other fieldnames.

URL

https://server.path.or.name/epo1soa/jsonhandler/epoetl/sync/ETLKostl/1000

request

{
    "objectList":
    [
        {
            "timestamp":"20221231120000",
            "etlKey":"100012345"
        }
    ],
    "firstBlock": "X",
    "lastBlock": "X"
}

response

{
    "epo1ok": "X"
}

EPO - ETL objects

There are several objects defined and implemented by EPO. However, a customer may define and implement any other objects. Simply study (and copy) one of the available function modules and create similar function modules for other objects.

Package /EPO1/EXC_ETL_CO

COPOSTING

KOSTL

Package /EPO1/EXC_ETL_FI

ACCOUNT

ASSET

CUSTOMER

FICTR

FIPOSTING

ORGSTRUCT

VENDOR

Package /EPO1/EXC_ETL_MM

Material

Export data

• table /EPO1/ETL_MATERIAL_T

• structure /EPO1/ETL_MATERIAL

• OBJECTOK FLAG
• OBJECTMESSAGE BAPIRET2_T
• OBJECT /EPO1/ETL_MATERIAL_OBJECT

• ETL_KEY /EPO1/ETL_MATERIAL_KEY
• MARA MARA
• MARC MARC
• MAKT MAKT

• IS_DELETED FLAG

Material class

Note: there is no change log, so we will sync all material classes

Export data

• table /EPO1/ETL_MATKL_T

• structure /EPO1/ETL_MATKL

• OBJECTOK FLAG
• OBJECTMESSAGE BAPIRET2_T
• OBJECT /EPO1/ETL_MATKL_OBJECT

• ETL_KEY /EPO1/ETL_MATKL_KEY
• T023T T023T

• IS_DELETED FLAG

PO - Purchase Order

Export data

• table /EPO1/ETL_PO_T

• structure /EPO1/ETL_PO

• OBJECTOK FLAG
• OBJECTMESSAGE BAPIRET2_T
• OBJECT /EPO1/ETL_PO_OBJECT

• ETL_KEY /EPO1/ETL_PO_KEY
• EKKO EKKO
• EKPO /EPO1/ETL_EKPO_T

• IS_DELETED FLAG

RAUMN

STAND

WERKS

Package /EPO1/EXC_ETL_PM

Equipment

Export data

• table /EPO1/ETL_EQUIPMENT_T

• structure /EPO1/ETL_EQUIPMENT

• OBJECTOK FLAG
• OBJECTMESSAGE BAPIRET2_T
• OBJECT /EPO1/ETL_EQUIPMENT_OBJECT

• ETL_KEY /EPO1/ETL_EQUIPMENT_KEY
• EQUI EQUI
• EQKT EQKT

• IS_DELETED FLAG

Functional Location

Export data

• table /EPO1/ETL_FUNCT_LOC_T

• structure /EPO1/ETL_FUNCT_LOC

• OBJECTOK FLAG
• OBJECTMESSAGE BAPIRET2_T
• OBJECT /EPO1/ETL_FUNCT_LOC_OBJECT

• ETL_KEY /EPO1/ETL_FUNCT_LOC_KEY
• IFLOT IFLOT
• IFLOTX IFLOTX

• IS_DELETED FLAG

Changelog

Version 3.5

Version 3.5.4

Transportauftrag WK1K904??? ??.??.2024

• Sync-Service: Kill-Switch-Timeout (23 Stunden) gegen endlose Blockade bei nicht rückgesetztem Sync-Status

Aktivitäten vor dem Import:

• -- keine--

  Version 3.5.3

Transportauftrag WK1K904273 12.03.2024

• Sync-Service: Objekt & Prefix ins EPO - Log schreiben
• Sync-Service: keine leeren ETL_KEYs in die temporäre Tabelle schreiben, nachher nicht mehr verarbeiten

Aktivitäten vor dem Import:

• -- keine--

  Version 3.5.2

Transportauftrag WK1K904220 26.02.2024

• Korrektur der Fehlermeldung im Sync-Service bei fehlendem (aber benötigtem) Prefix

Aktivitäten vor dem Import:

• -- keine--

Version 3.5.1

Transportauftrag WK1K904204 21.02.2024

• Korrektur: Parameter für SYNC - Service korrigiert/umbenannt für konsistente Namen

Aktivitäten vor dem Import:

• -- keine--

Version 3.2

Version 3.2.3

Transportauftrag WK1K903883 08.11.2023

• Korrektur: Asset, Feld ANL2 ab nun immer mit führenden Nullen ausliefern, weil nur 4-stellig

Aktivitäten vor dem Import:

• -- keine--