Use Modeling Options with CompositeProviders

In SAP BW/4HANA, the virtualization layer is built from CompositeProviders. Although CompositeProviders might appear to be complex in their design, during a Query runtime they are trimmed of all unnecessary elements so that reporting performance is maintained.

A CompositeProvider is a type of InfoProvider that combines data from several InfoProviders and makes it available for analysis purposes. The CompositeProvider itself does not contain any data. Its data comes entirely from the InfoProviders on which it is based. These InfoProviders are connected to one another by a union and/or join operation.

CompositeProviders are optimized for SAP HANA. They run their SQL operations on SAP HANA rather than on the ABAP application side–the SQL union or join operation has been pushed down to SAP HANA.

CompositeProviders are used for the following reasons:

• As an interface for Queries.

• As a virtual data mart layer. If the CompositeProviders remain unchanged, changes to DataStore Objects (advanced) don't change your reporting.

• Semantic partitioning of data marts can be divided into several smaller units, which are integrated by the UNION-capabilities of CompositeProviders. This process brings more flexibility to your data model and enables parallel processing for loading and reporting processes, resulting in performance gains.

A CompositeProvider consist of one ore more PartProviders (a PartProvider can also be used more than once in a CompositeProvider). The following objects can be used as PartProviders:

• InfoObjects

• DataStore Objects (advanced)

  Note

  The following DataStore Object (advanced) can be used:

  • Standard DataStore object with and without the change log.

  • Staging DataStore object, reporting enabled.

  • Data Mart DataStore object.

  • Direct update DataStore object.

  Data from DataStore Objects (advanced) can be read from the near-line storage for unions and joins.

• CompositeProviders

  Note

  You can use a CompositeProvider as a PartProvider in another CompositeProvider. To enable this consumption, release the CompositeProvider for this function first by setting the option This CompositeProvider can be added to another CompositeProvider.

  Only two levels of consumption are supported. That is, the CompositeProvider that consumes another one cannot be part of another CompositeProvider on top.

  For an overview of all supported scenarios, see this help page: https://help.sap.com/docs/SAP_BW4HANA/107a6e8a38b74ede94c833ca3b7b6f51/a803e5acb5b042548f744fcd3af0858f.html?q=compositeprovider&version=2023.01&locale=en-US

• Open ODS Views

  Note

  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 long characteristic-type field values.

• SAP HANA Calculation Views

  Note

  Both generations of SAP HANA Calculation Views are supported (XS classic, XS advanced/HDI). External SAP HANA views generated from SAP BW/4HANA InfoProviders are not supported.

In the preceding example, a union is made between two PartProviders (in this case two DataStore Objects (advanced)).

You can configure the assignments for the fields on the right of the screen. On the left, you see the participating InfoProviders. To the right of these, the CompositeProvider's fields are displayed. In the graphical display, you can add the fields to the CompositeProvider. This is achieved by either using drag and drop, or by choosing Create Assignments in the Context menu and creating the assignments. If you add an InfoProvider by dragging and dropping it, the fields and dimensions (groups) of the InfoProvider are applied. Any required dimensions that do not already exist are created.

During the assignment, two essential things happen. First, the editor tries to identify a potential target field in the target view node structure. Then, 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. With method two, a field with the same name as the source field is favored.

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 solid line connecting the source field on the left side to the target field on the right side denotes a completed assignment.

A best practice in modeling is the use of wrap CompositeProviders. In this scenario, a union is defined on only one PartProvider. For example, in this way, you wrap a DataStore Object (advanced) into a CompositeProvider. This offers the use of navigation attributes and flexibility regarding changing reporting requirements.

You switch on navigation attributes in a CompositeProvider independently of being switched on or off in the PartProviders. Sometimes, for example, an Open ODS View on a BW DataSource, navigation attributes must also be switched on in the PartProviders.

Mapping of navigation attributes of the PartProviders with navigation attributes of the CompositeProvider is not necessary and not available.

However, mapping of the navigation attributes of the PartProviders with characteristics of the CompositeProvider is possible.

Navigation attributes can be used as source fields, but not by default. This feature must be switched on manually. In the Scenario tab, select Show Unassigned Navigation Attributes from each PartProvider to make these fields available as sources for characteristics in the target structure of the CompositeProvider. This is independent of the activation of navigation attributes in the CompositeProvider. They are managed in the Output tab and can be switched on for every InfoObject containing attributes ready for navigation.

Different join types can be used when defining a CompositeProvider:

• Inner Join

• Left Outer Join

• Right Outer Join

• Full Outer Join

• Referential Join

• Temporal Join

Let's look at how to use a temporal join in a CompositeProvider.

If your source does not provide the category information, you can derive the historical truth in SAP BW/4HANA by adding persistent data during a load process. Alternatively, you can virtually combine the master data and transactional data in a CompositeProvider using a temporal join.

Let's see how this is set up in the SAP BW/4HANA system.

The following steps are needed for this set up:

1. Make sure the relevant attribute is time dependent.
2. In the CompositeProvider, add the base characteristic as Condition field.
3. From the fact source of the CompositeProvider, choose Set Key Date and refer to a time characteristic.
   Note

   In the transactional data, you have a calendar month, rather than a calendar day, you need a Key Date Derivation Type. You can define a Key Date Derivation Type in SAP HANA Studio, SAP BW/4HANA Cockpit of transaction code RSTHJTMAINT.
4. Verify the temporal operands.

When you use a time-dependent characteristic in the join, the join becomes time-dependent. The two extra fields, 0DATEFROM and 0DATETO, are compared with a time characteristic of the join partner. This time characteristic must be specified as key date in the context menu of the join partner. If the time characteristic is an interval, such as 0CALYEAR, the first or the last day of the interval can be derived, or a specific key date derivation type defines how a key date is derived.

You can use a Projection or Aggregation node:

• To select a field.

• To create / edit SQL filter.

• To create a calculated field.

• As a top-level node.

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

• 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 the 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 a different aggregation behavior specified for the key figure. This is then the first aggregation behavior, which is used for the key figure.

You can create a calculated field in the target structure using SAP HANA SQL Syntax, Expert function. When creating it, you receive a list of features supported by SAP HANA. Currently, no highlighting or validation for the SQL expression is offered: it is a simple text field. The check is done in the back end only.

You can use the option Create Field... to create a field in the target structure. These fields are not available in reporting; they are only visible in staging. Using calculated elements with an SQL expression however, you can convert the data type, so that it is suitable for reporting.

The option Duplicate Field can be used to create a duplicate field in the target structure. By single select, the field is copied and the Edit Dialog is opened, to make corrections in the name of field or other properties. By using multiple select, all selected fields will be duplicated.

If an appropriate source field or characteristic doesn‘t exist in PartProviders, a field/characteristic value in the target structure of the CompositeProvider can also be provided by a Constant. This must be set for each PartProvider in question.

You can use the "Create/Edit SQL Filter" functionality on view node (Union, Join, Aggregation, Projection) using SAP HANA SQL Syntax, Expert function. When creating it, you receive a list of features supported by SAP HANA. Currently no highlighting or validation for a filter expression is available: it's a simple text field. The check is done in the back end only.

A CompositeProvider can also serve as a source for a Transformation. This can be useful for special requirements, when the SQL Join/Union must be persisted in a DataStore Object (advanced), for example. 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 Data Mart DataStore Objects.

A CompositeProvider makes the underlying SAP BW/4HANA objects abstract. It provides an outbound interface that can be consumed by any kind of Query. The order of the fields, the descriptions and, to some extend, association of InfoObjects can be changed.

Let's look at the following modeling approaches:

• Simple Dynamic Star Schema.

• Snowflaked Dimensions.

• Split Dimensions.

In this scenario, central facts are mainly field-based by using a database table/view or DataStore Object (advanced). InfoObjects are only used to use its global format definition or to read from master data during transformation.

The idea is to keep the underlying physical layers with DataStore objects (advanced) or database tables simple. The model can be mainly field-based. In the virtual layer above the physical data, use mainly CompositeProviders, or in some cases, Open ODS views (fact type) to add rich semantic information. Associate InfoObjects or Open ODS views of type Master Data, and turn on navigational attributes.

This scenario offers the following advantages:

• Consistent data.

• Facts and dimensions can be reused in many models.

• Association is only used if needed.

• Real time capable.

• Flexible: no, virtual, or persistent master data.

There are some additional tricks to increase the flexibility and to reduce the data volume. The core dimension is the InfoObject that is directly associated in the CompositeProvider. Consider whether you really need a direct physical field for all required attributes of the core dimension. (This model approach is called denormalized.) For example, if the main category only depends on the product category, it is sufficient to store product category values physically in the core dimension. You can integrate the main category as a foreign key attribute (transitive attribute in the core InfoObject). This has the following advantages:

• The data volume is reduced because the relationship between category and main category is stored only once.
• Core InfoObject and Foreign key Attribute InfoObjects can have different loading cycles (if they have different owners or volatility).
• You still decide which attributes of the attributes are visible and which do not provide a business value.

However, if the core dimension and foreign key attribute tables are large, a join can take a long time. Then, it is better to denormalize the model.

This must be checked situational. Prefer snow-flaked InfoObject dimensions if:

• Foreign key attributes have different owners/different volatility compared to the direct attribute of the core dimension.

• You want to keep the core-dimension table slim, transparent, and maintainable.

• The value of denormalizing foreign key attributes isn‘t obvious in the business context of a dimension (an employee that created a product category of a product).

Carefully examine the value of snow-flaked InfoObject dimensions if:

• The values of the foreign key attributes never change.

• The cardinality of the foreign key table is high.

Another concept for increased flexibility is the concept of Split Dimensions. Imagine that there are some product attributes that everyone can see, such as length and height, and others that are critical, such as product type and price. If all these attributes are stored in the same InfoObject, by associating the InfoObject, all such information appears as display attributes in all queries, including the critical ones. Moreover, to load a new price, you must load the entire attribute list again, including the unchanged size information.

To avoid these issues, we recommend creating different InfoObjects, one with uncritical technical attributes and another one for critical attributes. You can then decide for each CompositeProvider whether to associate both InfoObjects (by mapping the same source field twice to the target area) or only one or the other. Moreover, one of the InfoObjects could be replaced by an Open ODS View. For instance, if you need real time price information, or if you want to reduce storage space, without changing the reference to physically stored data of the other InfoObject.

Finally, consider the options of transitive attributes (snowflaked dimensions), reference characteristics and split dimensions and leaving some fields unassociated by using a field-based approach. This way, you can generate a flexible model with minimal storage space consumption.