Designing Transformations and Data Flows Between BW Layers

Explanation

Once the foundational objects—InfoObjects, ADSOs, and CompositeProviders—are modeled, the real engineering work in BW happens in transformations and Data Transfer Processes (DTPs), which govern how data moves and is enriched as it progresses through the layered architecture. A transformation defines field-to-field mapping and business rule logic between a source (DataSource, ADSO, InfoObject) and a target InfoProvider. A DTP then executes that transformation, controlling extraction mode (full or delta), filtering, and error handling at runtime. Transformation rule types range from simple to complex. A one-to-one direct assignment maps a source field straight to a target InfoObject with no logic. A constant rule hardcodes a value. Formula rules use BW's formula builder for calculations like unit conversions or concatenations. Routine rules (start routines, field-level routines, and end routines) allow ABAP code for complex logic—start routines operate on the entire source package before field mapping (useful for bulk lookups or filtering), field routines apply logic to a single field per record, and end routines operate on the complete result package after mapping, often used for aggregation, deduplication, or complex derivations requiring cross-record logic. A critical intermediate-level skill is understanding delta handling. Extraction from many source systems supports delta queues, meaning only new or changed records are transferred since the last successful load. The DTP is configured for delta extraction after an initial full load, and the request-based delta management in BW (using request IDs and status tracking) ensures exactly-once processing semantics—critical because a failed or partially processed request must be safely reprocessable without duplicating data. Understanding how a red (failed) request is handled—typically requiring deletion of the erroneous request from the target before re-triggering the DTP—is essential production knowledge, not just design theory. Data flow design decisions at this level include: where to place business logic (as far upstream as reasonable, to keep it reusable across multiple reporting targets, but not so far upstream that raw source fidelity is lost), how to handle master data timing dependencies (loading master data before transactional data that references it, to avoid unresolved SIDs or incorrect attribute lookups), and how to structure multi-step data flows for auditability (each ADSO layer should represent a meaningful, inspectable state of the data, not just a technical pass-through). In S/4HANA environments with embedded BW or SAP Datasphere-connected scenarios, some transformation logic may shift toward native HANA views or CDS-based extraction, changing where business logic physically resides—but the BW-side transformation and DTP concepts remain relevant wherever ADSOs and CompositeProviders are still used for harmonization or historical reporting. Consultants must be able to trace a data quality issue back through the transformation and DTP chain: checking the DTP monitor for error records, reviewing routine code for a faulty condition, and validating that master data was loaded and activated before the dependent transactional delta ran.

Code example

*Example: End routine in a BW transformation (ABAP)*Purpose: deduplicate incoming billing records by keeping only the*record with the latest change timestamp per document number METHOD end_routine.  DATA: lt_result TYPE TABLE OF ty_result,        ls_max    TYPE ty_result.   SORT RESULT_PACKAGE BY doc_number changed_ts DESCENDING.   LOOP AT RESULT_PACKAGE INTO ls_max.    READ TABLE lt_result WITH KEY doc_number = ls_max-doc_number         TRANSPORTING NO FIELDS.    IF sy-subrc <> 0.      APPEND ls_max TO lt_result.    ENDIF.  ENDLOOP.   RESULT_PACKAGE = lt_result.ENDMETHOD.

Real project scenario

During a finance reporting rollout, duplicate billing line items appeared in the harmonization-layer ADSO because the source system occasionally sent corrected duplicate records with the same document number but a later change timestamp. The consultant added an end routine to the transformation to retain only the latest record per document number, then re-ran the affected DTP requests after deleting the erroneous red requests from the target, restoring correct record counts.

Common mistakes

• Placing complex business logic in a start routine when it should be a reusable formula, making it harder to maintain and test • Not accounting for delta initialization correctly, causing either missing historical data or duplicated initial loads • Loading transactional deltas before master data is activated, causing incorrect or blank navigation attribute values in reports • Ignoring DTP error handling settings, so a single bad record silently drops instead of surfacing for review • Writing end routines that assume single-package processing when semantic grouping across packages requires different handling

Best practices

• Keep transformation logic as reusable and upstream as feasible without sacrificing raw data fidelity in the acquisition layer • Always sequence master data loads and activation before dependent transactional delta loads • Use end routines for cross-record logic like deduplication or aggregation, not for simple field mapping • Monitor DTP requests for error records rather than assuming silent success • Document any custom routine logic clearly so future support consultants can trace business rule origin during incident resolution

Interview angle

Expect scenario-based questions such as how you would troubleshoot a red request in a DTP, the difference between a start routine and an end routine, and how request-based delta management prevents duplicate processing. Interviewers also probe whether you understand the sequencing dependency between master data and transaction data loads.