Composite Providers - Nuts and Bolts.pptx

nice comp bpro

1. Composite providers Nuts & bolts

2. Composite provider Positioning

3. Composite provider : overview CompositeProvider now provides the central means for joining data and presenting it to the reporting layer. It is an InfoProvider that combines data from BW/4HANA Infoproviders and objects of external SAP HANA schemas by Join or Union. It makes this data available for reporting and analysis and fully replaces MultiProviders and BW InfoSets of the past

4. Composite provider : key features & advantages

5. Supports both UNION And joins (inner, left and right outer)

6. Composite provider – features Supported Providers Depending on the SQL root operation, you can integrate following InfoProvider types into a CompositeProvider: UNION : ADSO, InfoObject, Aggregation level, Open ODS view, CompositeProvider, hana Calculation View JOIN : ADSO, InfoObject, CompositeProvider, SAP HANA Calculation View JOIN and UNION : ADSO, InfoObject, CompositeProvider, SAP HANA Calculation View Things to consider : ADSOs should have properties that are relevant for reporting. In other words, they should have one of the following combinations of properties: 1. Standard DataStore object with change log 2. Staging DataStore object, reporting enabled 3. Data mart DataStore object 4. Direct update DataStore object External SAP HANA views generated for SAP BW Infoproviders are generally not available as input objects for CompositeProvider Open ODS Views that you want to use in CompositeProvider 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. 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 Composite Provider) for all details.

7. Composite provider – nesting Supported Providers You can use a CompositeProvider as PartProvider in another CompositeProvider However, only two levels of consumption are supported by SAP. That is, a CompositeProvider that consumes another one cannot be part of another CompositeProvider on top. 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 . In addition, there are limitations on the supported scenarios in terms of SQL root operation and type of part providers., SAP HANA Calculation View

8. Composite provider – temporal joins Composite Providers 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. ADSOs 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 advanced DSO 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.

9. Composite provider – temporal joins The following restrictions apply for Composite Providers based on temporal joins: Supported SAP BW Infoproviders: InfoObjects, ADSOs No union nodes are allowed in temporal joins. The resulting CompositeProvider cannot contain any fields that are not assigned. Composite provider – source of transformations 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 an ADSO, for instance. Extraction of type FULL is always available. A DELTA type extraction is available if following conditions are met: 1. The Composite Provider's root operation is Union . 2. The CompositeProvider consists of ADSOs only. 3. All ADSOs are of type data mart DataStore object (and as such behave like an InfoCube).

10. Composite provider – Ways to activate navigational attributes Modeling Navigation Attributes in the Composite Provider

11. Composite provider – mapping for navigational attributes

12. Composite provider – mapping for navigational attributes 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.

13. Consistent Navigation Attribute Mapping in a HCPR In contrast to a Multiprovider, the BW system does not allow all kinds of navigation attribute mappings any longer in a HCPR: In a HCPR, it is always assured that navigation attributes are mapped consistently. That is, the navigation attribute relationship to the main characteristic(as stored in the master data tables) is always kept in the output. Inconsistent navigation attribute mapping is not allowed. Simple example In a Multiprovider it was possible to map a characteristic C of a PartProvider to a navigation attribute A__C on the Multiprovider level. In this case, the relationship between a characteristic value of A and its navigation attribute C (stored in the P or Q master data tables of the characteristic A) is not kept any longer. Hence, a query might display different attributes in comparison to queries where the result is based on the corresponding master data tables. In addition, in this case it might happen that the assignment from A to C is not unique any longer! That is why this kind of mapping is not possible when creating an HCPR. When a navigation attribute (A__C in our example) is activated on the HCPR level, no mapping is possible/offered since this means (automatically) that the master data tables are joined with the basis characteristic in order to get the attribute values.

14. Consistent Navigation Attribute Mapping in a HCPR Examples of consistent and inconsistent navigation attribute mappings Characteristic A has B as reference characteristic. That is, A and B have the same master data tables. E.g.: 0DEBITOR has 0CUSTOMER as reference characteristic. Characteristic C has D as reference characteristic. HCPR is union of PartProviders. The following mappings are consistent Example 1 Partprov HCPR A ----------- A B ----------- B A__C ----------- A__C B__C ----------- B__C Example 2 PartProv HCPR A ----------- B B ----------- A A__C ----------- B__C B__C ----------- A__C The relationship between A and A__C is kept. The relationship between B and B__C is kept.

15. Consistent Navigation Attribute Mapping in a HCPR Examples of consistent and inconsistent navigation attribute mappings The following mappings are consistent Example 3 Partprov HCPR A ----------- A A__C ----------- C or D C or D is a 'standalone' characteristic, there is no relationship between A and C/D in the output. The following mappings are inconsistent, thus not possible in a HCPR Example 4 A ----------- A B ----------- B A__C ----------- B__C B__C ----------- A__C The relationship between A and A__C is broken. The relationship between B and B__C is broken.

16. Field mapping in HCPR (HANA Composite provider) 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 view node. The source structure contains the fields of either a physical InfoProvider or of another view node in the same CompositeProvider model. A target node 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 two types of view nodes: the UNION type and the JOIN (inner, left and right outer) type. 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. Data type compatibility is ensured by evaluating the compatibility rules. For details regarding these rules, see SAP Note 2228967 (Understanding Automatic Field Assignment in the BW

17. Composite provider – support functions Transaction RSOHCPR provides the following basic tools in the Data Warehousing Workbench to work with CompositeProvider: Where-used list Object catalog entry transport connection Display data Display metadata Display data model Recreate ColumnView In older bw versions, MultiProviders provided the interface to reports. Now they are replaced with composite providers. InfoSets were rarely used due to very slow performance. But composite provider also covers the join functionality and thus both joins and unions can be done on the fly with composite providers. In SAP BW/4HANA, the CompositeProvider adds value to your architecture. A CompositeProvider combines data from SAP BW/4HANA InfoProviders with SAP HANA Information Views by Join and Union. This makes this data available for reporting and analysis

18. Composite provider – support functions Composite Providers: Virtualization or Persistent Join

19. Design Considerations for Composite Provider Number of PartProviders From Query runtime perspective, it is not recommended to build a CompositeProvider with many PartProviders. Each involved PartProvider increases the Query runtime, even if no data from the PartProvider is requested, as more processing for the Metadata handling is necessary. Therefor it is recommended from Query runtime perspective to create a CompositeProvider, which contains only the data, which is necessary for the Query. NULL values The ABAP Application Server does not distinguish between an initial value and a NULL value. NULL values can occur in Composite Providers with left outer join or in Composite Providers that consume HANA models. The BW runtime is not aware of the semantics in a HANA model. Therefore it must handle a CompositeProvider based on a HANA model in that way that all columns might return NULL values. Several restrictions apply if a CompositeProvider might return NULL values: 1. When NULL values can occur for one involved InfoObject, the pushdown of exception aggregation is not possible anymore. 2. Since BW handles NULL values as initial values. Therefore a filter on the initial value in BW implicitly includes a filter on NULL when reading data from a source which may return NULL values. Due to this fact any filter must be analyzed to determine if it contains the initial value or not. 3. Furthermore it is not possible to push down any hierarchy filter if NULL values can occur. This is also the case if no InfoObject with NULL values is involved in the Query. The restrictions mentioned above may have an impact on performance. The degree of the performance penalty depends on the scenario and ranges from not noticeable to significant.

20. Design Considerations for Composite Provider Number of PartProviders To avoid null value special handling in case of Hana models is to guarantee that master data values exist in BW for any transactional record returned by the CompositeProvider. In that case the flag 'User Confirmed Referential Integrity' can – and should – be set in the characteristic-specific properties for the output fields of a CompositeProvider. Example: PartProvider 1 PartProvider 2 Characteristic A Characteristic B Key Figure K A1 B1 2 A2 B2 3 A3 B3 1 A4 B1 5 Characteristic A Characteristic B A1 C1 A2 A3 C2

21. Design Considerations for Composite Provider Result of Left Outer Join between PartProvider 1 and PartProvider 2 on Characteristic A Characteristic A Characteristic B Characteristic C Key Figure K A1 B1 C1 2 A2 B2 3 A3 B3 C2 1 A4 B1 Null 5 In the Result for Characteristic C, there are four different values after the Left Outer Join. The values c1, c2, the initial value and the NULL value. In BW the initial value and the NULL value are the same and only 3 different values are visible there .

22. Design Considerations for Composite Provider User confirmed Referential integrity The flag 'User Confirmed Referential Integrity' allows the CompositeProvider to do a join to the master data SID table – even for PartProviders that do not store SID values but keys. In that case the key column of the master data SID table is used as join column. By joining the master data SID table it is possible to benefit from SID-based processing like HANA pushdown of exception aggregation. The flag 'User Confirmed Referential Integrity' must only be set if master data values exist in BW for any transactional record returned by the CompositeProvider. Otherwise the result set of a query might be filtered by the existing values in the BW master data tables. The flag 'User Confirmed Referential Integrity' is mainly intended for HANA models as PartProviders. For BW InfoProviders used in CompositeProviders it can be derived from the properties of the InfoProvider whether referential integrity is given or not – for example, classic DataStores with SID generation during activation ensure referential integrity. In that case it is not necessary to set the flag in the CompositeProvider. Confirming the referential integrity in a CompositeProvider with HANA model may be beneficial to avoid the special handling of NULL values as described above. However the referential integrity is only considered if the related PartProvider is not connected with a left outer join in the CompositeProvider. In other words the special handling of NULL values is always required for PartProvider connected with a left outer join in the CompositeProvider. Confirming the referential integrity may also be useful if several source InfoObjects are mapped to the same target field and those source InfoObjects have different base characteristics. In this case it is not clear if any of the master data SID tables can be joined and therefore, no master data SID table is joined by default. By associating an InfoObject to the target and confirming the referential integrity, it is again possible to use a join to the master data SID table of the associated InfoObject. The referential integrity must only be confirmed if the associated InfoObject contains master data records for any value that might be returned by the different source InfoObjects. If the referential integrity is confirmed for the InfoObjects based on HANA Model it is automatically confirmed for the Navigation Attributes.

23. Conversion Option from MultiProviders to CompositeProviders on SAP BW 7.4/.7.5 on HANA In an SAP BW on HANA environment, you can create a CompositeProvider based on a Multiprovider or BW InfoSet. Since BW 7.40, SP10, report RSO_CONVERT_IPRO_TO_HCPR enables this. For instance, you can leverage this tool to prepare a conversion to SAP BW/4HANA. This tool is delivered and fully documented in SAP Note 2080851.

24. Conversion Option from MultiProviders to CompositeProviders on SAP BW 7.4/.7.5 on HANA The following actions are possible: Conversion of a MultiProvider to a CompositeProvider. Conversion of a MultiProvider to a CompositeProvider with the same name Copying of queries to the new CompositeProvider, considering the InfoObject mapping. Transfer of queries to the new CompositeProvider, keeping their names (requires SAP BW 7.5). Conversion of an old CompositeProvider to a new CompositeProvider Creation of a backup for a MultiProvider. Recovery of a Multiprovider from the backup. Deletion of the backup (requires SAP BW 7.5) Summary The role of the CompositeProvider is to provide a metadata object that forms the data mart layer within SAP BW/4HANA. It provides the data for reporting and analysis in the form of an outbound structure that is semantically rich. It makes the underlying SAP BW/4HANA objects abstract and provides an outbound interface that can be consumed by any kind of query by offering the option to generate an SAP HANA view. Therefore, SAP BW queries should always be defined on the new CompositeProvider only, because this option offers you greatest flexibility to react to changes in your reporting requirements.