Dirk Grote

Parallel DeltaQ Loads and Processing of Delta Records

 

Parallel Data Loads in SSIS with Xtract IS DeltaQ

When loading SAP datasources with DeltaQ full load into your Data Warehouse you will most likely end up with 100s of tables that you want to load daily in your ETL process. For reasons of better maintainability it is best practice to have one SSIS package for each SAP datasource to load.

Even if each of your load packages just takes 1-2 minutes, whole processing time quickly sum up to 2-3 hours. So, you’re clever and parallize the extractions by calling your load packages in one controller package and set property MaxConcurrentExecutables=2 (or more) to have more then one package running at a time.

Then suddenly your remark strange errors e.g. timeouts during extraction. What happens is, that more than one client process (= SSIS Package) connects to SAP via the same RFC destination and communication between the processes gets confused. You’ll have to avoid this! To work around this you need 2 or more RFC destinations in SAP and organize your extraction in 2 or more streams that almost take the same time in total, each stream using only one Destination. This way you are save and every destination is only used once at the same time. The number of extractions carried out in parallel should be validated with SAP admins of your source system, to avoid to much processing load at the same time.

SAP Delta Processing

SAP DeltaQ has several Record Modes that tell you, what kind of record it is: Is it the first time, that this record is delivered or has there been an update in the source system and you’re getting a new version of this record. The current state of the record is known as ‚New Image‘ (delivered first time) or ‚After Image‘ (after changing values). These are the records you’ll most likely will need for your ETL processing. But eventually you also get ‚Before Image‘ (before update with values signed reverse), ‚Additive Image‘ (difference to old values), ‚Reverse Image‘ (cancellation, all values are signed reverse) or ‚Delete Image‘ (delete record with original values). Find these values in ROCANCEL field of the extracted record and consult documentation of specific Datasource to see, how each case is marked. Depending on delta method for the datasource you’ll find ROCANCEL like this: <empty> = ‚New Image‘ or ‚After Image‘, ‚X‘ = ‚Before Image‘, ‚R‘ = ‚Reverse Image‘, ‚D‘ = ‚Delete Image‘.

If you want to check, which Delta Processing is activated for a specific Datasource in your system, take a look at SAP table ROOSOURCE in your SAP OLTP System (not in your SAP BW!). There you’ll find every information you need:

ROOSOURCE

In this example for Datasource 0PRODUCT_ATTR we see that Delta Processing Method ‚AIM‘ is used. But what does this mean? Take a look at SAP table RODELTAM and you’ll find explanation:

RODELTAM

Delta Method ‚AIM‘ (see first column) only uses After Images (UPDM_AIM=’X‘). But be aware to select only records with OBJVERS=’D‘ (means this version is delivered in production).

In your SAP OLTP System you find These information by calling Transaction RSA2.

So much for now. Hope this answers some questions.