Layered Scalable Architecture and CompositeProvider Design for Reporting

Explanation

Once the basic building blocks of BW modeling are understood, the next skill is arranging them into a coherent, maintainable architecture. The Layered Scalable Architecture (LSA++) is the de facto modeling pattern for SAP BW and BW/4HANA implementations, organizing data flow into distinct layers, each with a specific purpose: the Inbound/Acquisition layer captures raw, unchanged source data (typically a write-optimized-style ADSO with minimal transformation, preserving auditability); the Harmonization/Corporate Memory layer applies business rules, master data lookups, currency/unit conversions, and historical retention; and the reporting/consumption layer exposes cleansed, enriched, query-ready structures, often through a reporting ADSO and one or more CompositeProviders. The CompositeProvider is the modern replacement for MultiProviders and InfoSets in BW/4HANA (MultiProviders still exist in BW-on-HANA for compatibility but are largely superseded). A CompositeProvider can perform a union (stacking similar structures, such as combining current-year and historical ADSOs) or a join (combining structures on common keys, such as sales facts joined with a currency conversion table or a master-data-like ADSO). Because CompositeProviders operate at the semantic layer and generally push processing down to the HANA database, they allow architects to avoid physically merging data during ETL, which reduces load times and storage duplication while still delivering a single reporting object to BEx queries. Designing a CompositeProvider requires understanding join cardinality and field mapping carefully. A join that unintentionally creates a many-to-many relationship at the database level can silently duplicate key figures, inflating reported totals—a classic and costly modeling defect. Architects mitigate this by ensuring join keys reflect the actual grain of each underlying provider and by testing aggregate totals against source system totals before go-live. Transformations are the mechanism that moves and reshapes data between LSA++ layers. Each transformation maps source fields to target InfoObjects/ADSO fields and can include rule types: 1:1 direct assignment, constant, formula (using BW's rule editor or ABAP routines), master data read (attribute lookup), currency/unit conversion, and time conversion. A well-modeled transformation avoids embedding complex business logic that changes frequently; instead, volatile logic is often isolated into a dedicated routine or a decision table so it can be adjusted without touching the entire transformation. DTPs (Data Transfer Processes) execute the transformation between two persistent objects and control filtering, error handling, and delta versus full extraction semantics. Modeling decisions directly affect DTP behavior: for example, if an ADSO is defined with a delta-capable key structure, DTPs downstream can request deltas efficiently; if the key design is wrong (e.g., missing a time characteristic in the key when history must be preserved), records will overwrite each other instead of accumulating history. On S/4HANA embedded analytics, some reporting needs may bypass BW modeling entirely via CDS views, but hybrid architectures often still rely on BW/4HANA for harmonized, cross-system reporting, especially where multiple source systems or long history retention is required. In such hybrid scenarios, modelers must decide which reporting requirements belong in BW versus embedded analytics, based on data latency needs, cross-system scope, and governance requirements. Performance tuning at the modeling stage includes choosing appropriate key fields to limit ADSO activation set size, avoiding unnecessary navigational attributes in CompositeProviders (each navigational attribute join can add processing cost), and using aggregation-aware design so that reporting-layer ADSOs pre-aggregate where feasible rather than relying solely on database-level aggregation at query time.

Code example

-- Conceptual illustration of a CompositeProvider JOIN semantics (not executable ABAP/BW syntax)-- ADSO_SALES (key: SalesOrder, Item; fields: Customer, Material, Amount, Currency)-- ADSO_CURR_RATE (key: Currency, Date; fields: ExchangeRate)---- CompositeProvider CP_SALES_REPORT (JOIN):--   ADSO_SALES.Currency = ADSO_CURR_RATE.Currency--   AND ADSO_SALES.PostingDate = ADSO_CURR_RATE.Date---- Risk check before activation:--   Verify ADSO_CURR_RATE has exactly one rate per Currency+Date (unique key)--   Otherwise the join produces a many-to-many match and inflates Amount totals

Real project scenario

A manufacturing company's BW/4HANA team is asked to report global sales in a single reporting currency without duplicating ETL logic per region. They build an inbound ADSO capturing raw sales per region, a harmonization ADSO applying currency conversion and master data enrichment, and a reporting ADSO exposed through a CompositeProvider that joins with a rate table. During UAT, total revenue appears higher than the source ERP report; investigation reveals the exchange rate table had multiple rates per day due to intraday updates, causing a many-to-many join and duplicated fact rows, which the team fixes by adding a rate-selection rule in the transformation before the join.

Common mistakes

• Building a CompositeProvider join without verifying uniqueness of the join key on the secondary provider, causing duplicated key figures. • Embedding frequently changing business rules directly in transformation ABAP routines instead of externalizing them for easier maintenance. • Skipping delta-key design on ADSOs, resulting in history being overwritten instead of preserved. • Mixing union and join logic in a single CompositeProvider without documenting intent, making future maintenance error-prone. • Adding excessive navigational attributes to a CompositeProvider, degrading query performance without a clear business justification.

Best practices

• Model each LSA++ layer with a single clear purpose and avoid skipping layers under schedule pressure. • Validate join cardinality on every CompositeProvider before moving to test/production. • Isolate volatile business rules into maintainable lookup tables or routines rather than hardcoding them in transformations. • Design ADSO keys to match the required historical grain, especially where delta tracking or auditability is needed. • Reconcile aggregate totals against source systems as a standard step in every new model's testing cycle.

Interview angle

Expect questions on LSA++ layer purposes, the difference between union and join CompositeProviders, and how to prevent key-figure duplication in joins. A strong answer walks through a concrete duplication scenario (like the currency rate example) and explains the verification step (comparing totals against source) used to catch it before production.