CompositeProvider Runtime Behavior, Query Performance and Troubleshooting

Explanation

Once a CompositeProvider is designed and activated, its real value is tested at query runtime. Unlike a MultiProvider, which always performs a union and pushes aggregation down to each underlying InfoProvider before combining results, a CompositeProvider can mix union and join semantics within a single object, and it executes on the HANA calculation engine rather than the classic BW OLAP engine alone. This means performance characteristics differ meaningfully from classic BW objects, and consultants need a mental model of how the generated HANA calculation view behaves. When a query runs against a CompositeProvider, BW generates SQL that is pushed to HANA. For union parts, HANA typically executes each branch independently and merges results, similar to a MultiProvider, but with the advantage of in-memory columnar processing and reduced data movement. For join parts, the join is evaluated at the granularity of the CompositeProvider definition, which means if the join cardinality is not correctly set (for example, an unintended many-to-many relationship), the result set can silently multiply rows, causing inflated key figures. This is one of the most common production defects: a report showing revenue three times higher than expected because a join between a sales ADSO and a returns ADSO was modeled without a proper cardinality constraint or without pre-aggregating one side. A second runtime consideration is filter pushdown. CompositeProviders are most performant when filters on characteristics used in the join or union key can be pushed down to each underlying source before combination. If a query filters on a field that only exists in the composite provider's calculated or derived layer, filter pushdown may not occur, forcing the engine to read and combine larger volumes of data before filtering, which increases both memory consumption and elapsed time. Reviewing the generated query execution plan, where available in your monitoring tools, helps confirm whether pushdown is happening as expected. Troubleshooting a CompositeProvider issue typically starts with reproducing the query in a query monitor and reviewing the aggregation and join step trace, if your tools expose it. Consultants should check: whether the CompositeProvider is using join or union for the affected fact tables, whether cardinality settings match the actual data relationships, whether identical characteristics across sources are correctly mapped to the same field to avoid duplicate rows, and whether currency or unit conversion is happening at the right layer (composite provider level versus underlying provider level), since inconsistent conversion timing is a frequent source of numeric discrepancies. Activation and transport issues also surface at this stage. A CompositeProvider that references objects not yet active in a target system will fail activation with dependency errors; the standard remediation is to transport prerequisite ADSOs, InfoObjects and any referenced queries or HANA views before or together with the CompositeProvider request, and to reactivate in the correct sequence. In BW/4HANA, semantic checks are stricter than in classic BW on HANA, so composite providers that were tolerated in older on-premise BW may need remodeling (for example, removing InfoCube dependencies) before they pass BW/4HANA content checks. From a governance perspective, teams should document each CompositeProvider's join/union structure, expected row-level grain, and known limitations, because these objects tend to be reused across many queries and any hidden fan-out defect propagates broadly. Regular regression testing after underlying ADSO structural changes (added fields, changed keys) is essential, since a CompositeProvider does not automatically validate that its join conditions remain semantically correct after source changes.

Code example

-- Conceptual illustration of what a CompositeProvider JOIN can generate at the HANA level-- (simplified; actual generated SQL is managed internally by BW/4HANA) SELECT  sales."0MATERIAL"      AS MATERIAL,  sales."0CALDAY"        AS CALDAY,  sales."REVENUE"        AS REVENUE,  returns."RETURN_QTY"  AS RETURN_QTYFROM  "ADSO_SALES_FACT" AS salesLEFT OUTER JOIN  "ADSO_RETURNS_FACT" AS returns  ON sales."0MATERIAL" = returns."0MATERIAL" AND sales."0CALDAY"   = returns."0CALDAY"-- WARNING: if MATERIAL + CALDAY is not unique per row in either source,-- this join fans out and inflates REVENUE when aggregated in the query.-- Correct approach: pre-aggregate returns to MATERIAL+CALDAY grain-- before joining, or model as a UNION with a type discriminator field instead.

Real project scenario

A retail customer built a CompositeProvider joining a sales ADSO to a promotions ADSO to enrich sales queries with promotion flags. After go-live, finance reported that total sales figures in the new report were roughly double the values in the legacy report. Investigation showed the promotions ADSO had multiple promotion records per material/day combination (one row per promotion channel), so the join fanned out sales rows before aggregation. The fix involved restructuring the CompositeProvider to use a union with a promotion flag derived via a separate calculated field, and pre-aggregating the promotions data to the sales grain in a staging ADSO, after which the numbers reconciled correctly.

Common mistakes

• Using JOIN when the actual business requirement is a UNION of comparable fact data, causing row fan-out and inflated key figures. • Not verifying cardinality assumptions against real data before activating a join-based CompositeProvider in production. • Assuming filter pushdown always occurs; not testing performance with realistic filter combinations before go-live. • Applying currency or unit conversion inconsistently between the underlying providers and the CompositeProvider layer, leading to reconciliation gaps. • Skipping regression testing after underlying ADSO structural changes, allowing join logic to silently break or double-count. • Ignoring BW/4HANA semantic check differences when migrating composite providers built under classic BW on HANA rules.

Best practices

• Prefer UNION over JOIN whenever combining fact data at the same grain from different sources; reserve JOIN for genuine attribute enrichment scenarios. • Validate join cardinality against actual data profiles in a non-production system before promoting to production. • Test query performance with representative filters and data volumes, not just small development datasets. • Standardize currency and unit conversion at a single, well-documented layer to avoid duplicate or missing conversions. • Maintain a lightweight design document per CompositeProvider describing grain, join/union logic and known constraints for future maintainers. • Re-test CompositeProviders after any structural change to an underlying ADSO, InfoObject, or source query. • When migrating to BW/4HANA, proactively review composite providers for classic-BW-only dependencies before relying on automated conversion tools.

Interview angle

Interviewers often probe whether a candidate understands the practical risk of joins in CompositeProviders versus unions, and how to diagnose an inflated key figure defect. Strong answers describe checking cardinality, verifying join keys against real data granularity, and explaining when a union with a source-system or type discriminator field is the safer design choice. Candidates should also be able to explain filter pushdown conceptually and why it matters for performance, without overstating internal HANA optimizer behavior they cannot verify.