Working with CompositeProviders

The role of the CompositeProvider is to provide a virtual data mart layer within SAP BW/4HANA that can be consumed by queries.

A CompositeProvider is a highly flexible and versatile object that replaces multiple modeling objects from SAP BW including the MultiProvider, InfoSet, and the first generation CompositeProvider that was first delivered with SAP BW powered by SAP HANA.

You should always build your BW Queries on top of a CompositeProvider. This is a similar best-practice of SAP BW when a MultiProvider was the object on which you built a query. This approach ensures that changes to the underlying data sources of the query can be easily managed. For example, you can easily replace the underlying DataStore Object (advanced) with an updated one without needing to adjust the query.

You can combine different types of SAP BW/4HANA objects in a CompositeProvider using flexible join and / or union logic. This was a major limitation with SAP BW when only a union was available in a MultiProvider and joins has to be implemented in separate InfoSets.

You can even build CompositeProviders on top of CompositeProviders to support a high level of reuse. It is also possible to generate additional calculated fields at the CompositeProvider level which means that you no longer need to define such calculations in the query or persist calculations in the underlying DataStore Objects (advanced).

The CompositeProvider can also directly consume calculation views from the SAP HANA database. You can even mix the objects from SAP BW/4HANA and SAP HANA to build hybrid models that mix historical data stored in SAP BW/4HANA, with live data stored in the SAP HANA tables that support a business application.

As already mentioned, in an SAP BW environment, the virtual data mart was implemented with MultiProviders. They were widely used for the following reasons:

• Interface for all reporting-related objects (mainly BEx Queries): This way, it was possible to decouple reporting objects from the persistent InfoCubes of the physical data mart layer. This means that changes to InfoCubes did not harm your reporting, as long as the MultiProviders remained unchanged.

• Semantic partitioning of data marts: Physical data marts could be divided into several smaller units, and they were integrated by the UNION capabilities of MultiProviders. This brought much more flexibility to your data model and enabled parallel processing for loading and reporting processes. This resulted in significant performance gains.

CompositeProviders now takes on these key responsibilities and add even more functionality.

CompositeProviders enable you to reduce data persistence in your data warehouse. This is because in SAP BW, join operations were done mostly during data staging, with persistent results staged in DataStore Objects. With the new CompositeProvider, a join can be executed on a data set without persistence.

Launch the next demo to explore a CompositeProvider in BW Modeling Tools.

When defining a CompositeProvider, a union can be used, which combines data from multiple providers and builds the union sets for the relevant data. All the values are combined.

When defining a CompositeProvider, also a join is offered, which combines data from tables using specific criteria.

You can combine the following InfoProvider types into a CompositeProvider using a UNION or JOIN:

• Standard DataStore object with change log
• Staging DataStore object, reporting enabled
• Data Mart DataStore object
• InfoObject
• CompositeProvider
• Open ODS View (see note)
• BAdI Provider (only Union)
• SAP HANA Calculation View

Things to consider generally:

• It is not supported to leverage external SAP HANA views generated for SAP BW InfoProviders as input objects for CompositeProviders (see SAP note 2458572).

• Open ODS Views that you want to use in CompositeProviders can only contain fields with InfoObject-compatible data types. These are the following data types: CHAR, CUKY, CURR, DEC, DATS, FLTP, INT4, NUMC, TIMS, QUAN, UNIT. Data type STRG is also supported for particularly long characteristic-type field values.

• In the case of DataStore Objects (advanced), data can be read from the near-line storage for unions and joins.

• When a field is assigned, the system checks whether the target field is suitable for the assignment being performed. A target field is suitable if it has not already been assigned to another field in the source structure. In addition, the system verifies whether it has a compatible data type. Refer to SAP note 2228967 (Understanding Automatic Field Assignment in the CompositeProvider) for all details.

CompositeProviders also support modeling temporal joins to depict time flows. In SAP BW/4HANA, only master data InfoObjects can be defined as a time-dependent data source. Two additional fields or attributes, 0DATEFROM and 0DATETO, are then added to the characteristic. DataStore Objects (advanced) however, cannot be defined as time-dependent. Nonetheless, they often contain time characteristics (from which a time interval can be derived) or a minimum of two InfoObjects that refer to 0DATE (which you can use to define a time interval for). This allows the DataStore Object (advanced) in the CompositeProvider to be considered as time-dependent. As soon as an InfoProvider that is contained in the CompositeProvider is made pseudo time-dependent, it is handled as a time-dependent data source. An important difference between pseudo time-dependent InfoProviders and time-dependent InfoProviders like InfoObjects is that the system cannot prevent gaps or overlaps from occurring in the time stream. This always depends on the data set of the pseudo time-dependent InfoProvider.

The following restrictions apply for CompositeProviders based on temporal joins:

• No union nodes are allowed in temporal joins

• The resulting CompositeProvider cannot contain any fields that are not assigned

• Only Standard DataStore object with change log, Staging DataStore object, reporting enabled, Data Mart DataStore object and InfoObject can be used

Aggregation and Projection nodes are in general not necessary in the CompositeProvider. There are some specific use cases only, where it could be interesting to use them, like:

• Defining a filter or calculation directly on top of a PartProvider.

• Defining a different aggregation behavior for a key figure, for example, in combination with force group by.

• Needed data aggregation between join nodes.

• Block the PartProvider default aggregation with a projection node for DataStore Objects (advanced) and Open ODS Views.

  Per default the CompositeProvider reads the data of the PartProviders already aggregated and the Union directly on top of the PartProviders aggregates the data as well. That means also that data of PartProviders is aggregated before a join is executed. This aggregation is, for example, necessary for the ambiguous join handling. The direct aggregation of PartProvider data can be skipped now for DataStore Objects (advanced) and Open ODS Views by using a Projection node directly on top of the PartProvider. This introduces the option, for example, to use a different aggregation behavior directly for a PartProvider. For this, an aggregation node on top of this projection is necessary, which has for the key figure a different aggregation behavior specified. This is then the first aggregation behavior which is used for the key figure.

In the CompositeProvider scenario editor, the user can create and modify assignments between the fields (columns) of a source structure and the fields (columns) of a target structure. The source structure contains the fields of either a physical InfoProvider or of another view node in the same CompositeProvider model. A target structure in the mapping context is always a view node. This is either the output (default) node or any other intermediate view node.

A CompositeProvider supports several types of view nodes: Union, Join, Aggregation, or Projection. The output view node (default node) is a dedicated view node in the set of all view nodes in a CompositeProvider. This view node defines the semantics of the CompositeProvider when consumed as a reporting view. Only the fields in the output node are visible for the SAP BW/4HANA reporting tools.

During modeling of the CompositeProvider, the fields of the source structures are mapped to the final target structure. During the assignment, two essential things happen. First, the editor tries to identify a potential target field in the target view node structure. Second, it checks whether the chosen target field is suitable for assignment. An existing target field is suitable if it meets the following criteria:

• It is not already assigned to another field in the same source structure that the source field comes from

• It has a binary-compatible data type

If no suitable existing target field can be found, a new target field with appropriated InfoObject type and data type is created, and then assigned to the source field. A completed assignment is denoted by a solid line connecting the source field on the left side to the target field on the right side. Source fields for the assignment may be the fields of a view node, the fields of a native SAP HANA Calculation View, or the fields of an SAP BW InfoProvider. Data type compatibility is ensured by evaluating the compatibility rules.

It is possible to add new fields or create a calculated field in the target structure. The logic for these additional fields is directly applied in SAP HANA. The context menu provides function Create Field... / Create Calculated Field… in the Scenario tab on the target structure. In the UI dialog, you can fill all necessary information including the SQL Expression. The new field will have the calculated field icon and will be automatically mapped up. No incoming mappings are allowed into a calculated field.

There is a slight difference between the MultiProviders of the past and the CompositeProvider in SAP BW/4HANA. In the past, you could model different sources for navigation attributes, which could be confusing for other users. For example, CUSTOMER__COUNTRY could be assigned to any other country available in the data model and it was not guaranteed that this field really represented the country information of the customer object. SAP has changed this for the CompositeProvider and no longer allows these asynchronous mappings for navigation attributes. Navigation attributes are only switched on in the relevant InfoObject and they are no longer available for modeling different mappings.

Navigation attributes are still available as source fields, but not by default; this feature needs to be switched on manually. In the Scenario tab, select Show Unassigned Navigation Attributes from each PartProvider to make these fields available as sources for modeling a CompositeProvider. In the target structure of the CompositeProvider, however, the navigation attributes are not available. They are managed in the Output tab and can be switched on for every InfoObject containing attributes ready for navigation.

You can use a CompositeProvider as PartProvider in another CompositeProvider. To enable this consumption, release the CompositeProvider for this function first. In the General tab of the CompositeProvider modeler UI, choose This CompositeProvider can be added to another CompositeProvider. However, only two levels of consumption are supported by SAP. This means that a CompositeProvider that consumes another one cannot be part of another third CompositeProvider on top. In addition, there are limitations on the supported scenarios in terms of SQL root operation and types of PartProviders.

Just like MultiProviders in the past, the new CompositeProvider can serve as source for a Transformation as well. This might be useful for special requirements, when the SQL Join/Union should be persisted in a DataStore Object (advanced), for instance. Extraction of type FULL is always available. A DELTA type extraction is available if the following conditions are met:

• The CompositeProvider's root operation is Union.

• The CompositeProvider consists of DataStore Objects (advanced) only.

• All DataStore Objects (advanced) are of the Data Mart DataStore object type (and as such behave like an InfoCube).

In SAP BW/4HANA 2.0 SP04 (03/2020) the modeling possibilities for the CompositeProvider were extended with the intention allowing more options in the CompositeProvider and reduce the necessity for a mixed modeling between SAP HANA and SAP BW/4HANA. All the new options are SAP HANA-optimized, but consider, that best performance is guaranteed with a simple Union directly on the PartProviders. Leveraging these enhanced features might come at the cost of performance - just like it is when modeling SAP HANA Calculation Views.

The following design considerations and recommendations provide some helpful guidance to avoid modeling mistakes:

• Inner Join versus Left Outer Join: If possible, use an inner join, because this join is always faster.
• If Referential Integrity is guaranteed, you should set the corresponding flag on the Output tab. You then need to make sure that there is exactly one master data record for every value of the characteristic in the provider. If referential integrity exists, this can improve performance.
• Using OLAP Functions in Mixed Scenarios with SAP HANA Calculation Views: If you make significant use of BW OLAP functions, we recommend only using BW-persisted InfoProviders. This ensures the best possible performance when using BW OLAP functions. OLAP functions (such as exception aggregation) can only be executed in an optimized manner in SAP HANA if all fields used in the query have master data IDs (SIDs). However, SAP HANA Calculation Views do not have SIDs.

Transaction RSOHCPR provides the following basic tools in the Data Warehousing Workbench to work with CompositeProviders:

• Where-used list

• Object catalog entry and transport connection

• Display data

• Display metadata

• Display data model

• Recreate ColumnView