Transformations and DTPs: Rule Design, Error Handling and Delta Management

Explanation

Transformations and DTPs are the operational core of BW data modeling: once InfoProviders and DataSources exist, transformations define the semantic mapping between source and target fields, and DTPs control how and when data physically moves between persistence layers (PSA, DSOs, InfoCubes, aDSOs in BW/4HANA). Getting this layer right determines whether downstream reporting is trustworthy and whether loads complete within batch windows. A transformation is a set of rules per target field: direct assignment, constant, formula (using the rule editor or ABAP routines), read-master-data lookups, or time conversion rules for calendar alignment. Each rule type has a cost profile - routines executed row-by-row are far more expensive than direct mappings or formula rules that BW can process in bulk, especially on HANA where push-down processing matters. A common intermediate-level responsibility is reviewing existing transformations and identifying start routines, field routines or end routines that could be replaced with standard rule types or ABAP that is HANA-friendly (avoiding unnecessary loops, database reads inside loops, or dependency on obsolete function modules). The DTP governs extraction mode (full vs delta), the source PSA/DataSource filters, the update mode into the target, semantic keys for aDSOs, and error handling (request-level, or record-level with an error stack when 'update invalid records' handling is active). In classic scenarios, delta DTPs rely on the delta queue (RSA7 in source systems, or the extraction mechanism specific to the source) and BW maintains a pointer so each delta DTP run picks up only new/changed records since the last successful run. Filters on DTPs are a frequent source of confusion: they can be set at DTP level for repeatable subsets (for example, a specific fiscal year) but should not be mistaken for filtering that belongs in the transformation or in the query itself; misusing DTP filters can silently drop records that business users expect to see. Error handling design decisions include: whether to write invalid records to an error stack for later reprocessing, whether to abort the request entirely on error (safer for finance-critical loads), and how many parallel processes to allow, given system resources and record volume. For high-volume aDSO loads in BW/4HANA, understanding the semantic key and how it drives change log and active table updates is essential to avoid unintended overwrites or duplicate accumulation. Process chains typically orchestrate DTP execution together with attribute/hierarchy activation, index maintenance, and master data loads, sequenced to respect dependencies (master data before transaction data that looks it up). At an intermediate level, the practical skill is reading an existing transformation and DTP configuration, explaining what will happen to a specific incoming record, and being able to diagnose why a request failed, went yellow, or produced unexpected values - typically by checking the monitor, the PSA content, and the rule logic in that order rather than guessing.

Code example

* Example: Formula-based transformation rule pseudocode for a currency-neutral quantity field* Rule type: Formula (visual rule editor equivalent shown as pseudocode)IF SOURCE_FIELD-UNIT = 'EA' .  RESULT = SOURCE_FIELD-QUANTITY .ELSE.  RESULT = SOURCE_FIELD-QUANTITY * CONVERSION_FACTOR .ENDIF. * Example: End routine skeleton used to derive a fiscal period after all fields are mappedMETHOD end_routine.  LOOP AT result_package ASSIGNING FIELD-SYMBOL(<result>).    IF <result>-calday IS NOT INITIAL.      <result>-fiscper = /bic/derive_fiscper( <result>-calday ).    ENDIF.  ENDLOOP.* Avoid database SELECTs inside this loop; pre-read master data before the LOOPENDMETHOD.

Real project scenario

A retail client's daily sales load into an aDSO started missing its overnight batch window after volumes grew during a promotion period. Investigation showed a field routine in the transformation performing a database lookup for every incoming record instead of using a read-master-data rule with buffering. The team redesigned the rule as a standard master-data read rule, moved the remaining custom logic into an end routine operating on the full result package with a pre-fetched lookup table, and adjusted the DTP package size and parallelism. Load time dropped substantially and the process chain resumed finishing within the required window, avoiding delayed morning reports for store managers.

Common mistakes

• Using ABAP routines with row-by-row database reads instead of read-master-data rules or bulk lookups in the end routine • Setting restrictive DTP filters that unintentionally exclude valid records without documenting the business reason • Not distinguishing between full and delta DTPs, causing accidental reprocessing of the entire history or missed deltas after a delta initialization issue • Ignoring the error stack, leaving invalid records silently unresolved instead of triaging and reloading them • Sequencing process chains without respecting master-data-before-transaction-data dependencies, causing lookups to fail intermittently • Overusing start/end routines for logic that a standard rule type could handle more efficiently on HANA

Best practices

• Prefer standard rule types (direct, formula, read master data) over custom ABAP routines wherever functionally equivalent • Pre-fetch reference/master data once per package in end routines rather than reading inside row-level loops • Document the business justification for any DTP-level filter directly in the DTP description • Design process chains with explicit dependencies so master data activation always precedes dependent transaction loads • Use the error stack deliberately for financial or master-data-critical loads, with a defined reprocessing procedure • Monitor delta DTP status regularly and have a documented re-initialization procedure for delta queue issues

Interview angle

Interviewers assess whether a candidate can explain the performance difference between rule types (direct/formula versus ABAP routines), describe how delta DTPs track processed data, and walk through a troubleshooting sequence for a failed or yellow request (checking monitor status, PSA data, and rule logic in order). Being able to explain semantic keys in aDSOs and their effect on overwrite versus accumulate behavior is a strong differentiator for BW/4HANA-focused roles.