Child pages
  • HCPR: Referential Integrity
Skip to end of metadata
Go to start of metadata

Overview

As explained in SAP note 2271658 'Design Considerations for Composite Provider', the flag 'User Confirmed Referential Integrity' allows the CompositeProvider to do a join with the master data SID table – even for PartProviders (like Hana Models) that do not store SID values but keys. 

  • 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 not be complete! See example below.
  • Confirming the 'Referential Integrity' in a CompositeProvider with HANA model may be beneficial to avoid the special handling of NULL values (see HCPR: Handling of Null Values). However the referential integrity is only considered if the related PartProvider is not connected with a left outer join in the CompositeProvider.
  • If the 'Referential Integrity' is confirmed for the InfoObjects based on HANA Model, it is automatically confirmed for the NavigationAttributes.

Example

A simple Table contains only three records and a Null value for the field STPE_COSTCENTER.

This table is used in a Calculation View. The Data Preview displays the same data.

 Now we use this simple Calculation View in a HCPR

 The fields of the Hana Model are assigned to Infoobjects.The setting 'User Confirmed Referential Integrity' is active for the Objects STPE_MAT and STPE_CUST, but nor for STPE_COST:

 A simple sample query defined on this HCPR uses the followng restrictions:

Whe we run the query in transaction RSRT, the expected result is displayed:

 If we use the function 'Execute&Debug' - 'Display SAP Hana Query', the following statement (definition of calculation scenario) is displayed:

This calculation view basically defines just the join between the HCPR and the SID table of the characteristic STPE_CUST. This is done since STPE_CUST is in the drilldown (requested) and since the setting 'User Confirmed Referential Integrity' is active for this object.

The following Analytic API statement then accesses the calculation scenario:

Since Costcenter does not have the setting 'User Confirmed Referential Integrity' active, the OLAP Engine gets the key values (corresponding SIDs are then generated on the fly) in contrast to the field Customer. The filter "Costcenter NE *" is only needed in order to read Null values as well (see HCPR: Handling of Null Values).

If  the setting 'User Confirmed Referential Integrity' is active for the Infoobject STPE_COST

 then the query result changes to

Please note that the row with Costcenter='Not assigned' is missing !! 

This is caused by the additional join with the SID table of the infoobject STPE_COST. The value 'Null' does not exist in the SID table and hence the corresponding row is not returned. In other words, all values of the field STPE_COSTCENTER in the table used in the Hana Model must exist in the SID table - otherwise data is missing in the query!

In our case the SID table conatins the value 100 and hence the query displays results for the costcenter 100.

 

When we run the query again and take a look at the statements ( 'Execute&Debug' - 'Display SAP Hana Query'), we notice that now a new join appears in the calculation scenario (as expected since we activated the setting 'User Confirmed Referential Integrity' for STPE_COST):

 The following Analytic API statement then accesses the calculation scenario:

 The special handling of Null values for Costcenter isn't done any longer.

  • No labels