Designing CompositeProvider Union and Join Nodes for Reporting
Learn practical design decisions for building CompositeProvider union and join structures, mapping fields, and validating results before exposing the object to queries.
Explanation
Once the purpose of CompositeProvider is clear, the intermediate-level work is designing it correctly: choosing node types, mapping fields, handling key figure aggregation, and validating output before BEx or SAC queries are built on top. Design starts with identifying the reporting requirement's grain - the lowest level of detail the final report needs. If the requirement is sales analysis by day, product, and customer segment, the modeler identifies which InfoProviders supply which pieces: an InfoCube for daily sales facts (product, customer, quantity, revenue) and a DataStore Object (advanced) holding customer master attributes including segment. Because segment is an attribute, not a transactional fact, this becomes a JOIN scenario, not a UNION scenario. Two or more fact-level sources sharing the same structure (e.g., POS and e-commerce sales with identical characteristics and key figures) are UNION candidates. Field mapping in a UNION node requires aligning source fields to a common output field per target characteristic or key figure. When source providers have compatible but not identical objects, the modeler must confirm they're built on the same InfoObject; if not, a transformation step upstream (in the DSO or InfoCube transformation) should harmonize them before they ever reach the CompositeProvider, since CompositeProvider mapping does not perform data type conversion or business logic transformation the way a BW transformation does. For JOIN nodes, the join type decision is critical. An INNER JOIN only returns combinations where both sides have matching keys - useful when you want facts strictly limited to master data that exists, but dangerous if used for a fact-to-attribute join, because any transaction referencing a customer not yet loaded into the master data DSO silently disappears from the report. LEFT OUTER JOIN preserves all fact rows and returns nulls for unmatched attributes, which is almost always the safer default for fact-to-master-data joins. Referential joins (an optimized join type available in some scenarios assuming referential integrity) can improve performance further when the modeler is confident that all fact keys always exist in the reference side, but this assumption must be validated, not assumed. Key figure handling also needs review: after a UNION, key figures with the same technical meaning across sources should map to a single output key figure so aggregation works correctly in queries; if two sources use differently scaled or differently unit-typed key figures (e.g., one in USD, one in local currency), a harmonization step should occur upstream, not silently in the CompositeProvider. After building the node structure, validation is essential before exposing the CompositeProvider to reporting. This includes running the object's data preview feature in the modeling tool, comparing row counts and sums against the source providers independently queried, and testing filter/navigation scenarios that mirror expected report usage, particularly checking that outer joins behave as expected with missing master data. Performance-wise, since the join or union pushes down to HANA, the modeler should be aware of the underlying source objects' HANA views - DataStore Objects with heavy nesting or excessive characteristics in the join key can still create expensive execution plans. Where a CompositeProvider becomes a performance bottleneck, checking the generated HANA calculation view (via the appropriate BW tools) and considering whether a persisted variant or restructured join key is needed becomes the next step, typically undertaken by an architect-level review rather than initial design. Finally, in S/4HANA embedded analytics contexts, similar composite modeling exists via CDS view composition rather than BW CompositeProvider; BW CompositeProviders remain specific to BW-on-HANA and BW/4HANA landscapes and should not be confused with S/4HANA's virtual data model, even though the underlying push-down philosophy is conceptually similar.
Real project scenario
A manufacturing company needed a plant-level production reporting object combining a production order DSO (advanced) with a plant master InfoObject's navigational attributes for region and business unit. The team built a CompositeProvider with a LEFT OUTER JOIN node so that production orders for newly created plants not yet fully attributed in master data still appeared in reports with blank region values, rather than disappearing entirely, which had been a recurring complaint with the previous InfoSet-based INNER JOIN design.
Common mistakes
• Choosing INNER JOIN by default and losing valid transactional records tied to incomplete or delayed master data loads • Failing to harmonize currency or unit of measure across unioned sources before combining them in the CompositeProvider • Skipping data preview validation and discovering row duplication or missing records only after queries go live • Overloading a single CompositeProvider with too many join levels, making the generated HANA execution plan difficult to troubleshoot • Assuming referential join is always safe without verifying referential integrity between fact and reference providers
Best practices
• Default to LEFT OUTER JOIN for fact-to-master-data joins unless completeness of master data is guaranteed and documented • Harmonize currency, unit of measure, and characteristic compatibility upstream in transformations, not inside the CompositeProvider • Always run data preview and reconcile row counts and key figure totals against source providers before releasing to reporting • Keep join and union structures as simple as necessary; split overly complex logic into layered CompositeProviders if needed for maintainability • Review generated HANA execution behavior for join-heavy CompositeProviders feeding high-usage dashboards
Interview angle
A frequent scenario-based question asks how to design a CompositeProvider joining transactional data to master data attributes while avoiding data loss. The expected answer identifies LEFT OUTER JOIN as the safer default, explains why INNER JOIN can silently drop unmatched fact rows, and describes validating with data preview and row-count reconciliation before go-live.