As explained in SAP note 2271658 'Design Considerations for Composite Provider', in case a provider may return Null values some special handling is necessary. In addition it leads to some restrictions:
- Null values on characteristics are always treated as initial values. Therefore, a filter on the initial value in BW needs to inculde (implicitly) a filter on NULL when reading data from a source which may return Null values (in oder to guartantee correct query results).
- BW recognizes NULL values for not-mapped keyfigures in UNIONS. It also treats restricted keyfigures as NULL, if the corresponding filter does not match. In case a key figure has Nulls as a result of a left out join, the inital value is displayed
- When Null values can occur for one involved InfoObject, the pushdown of exception aggregation is not possible anymore
- If you want to define queries on Hana calculation views which may return Null values, create a HCPR based on this view. It is not recommended to use a Analytic View (as provider) since the correct handling of Null values is not guaranteed in this case. See example below.
- Confirming the 'Referential Integrity' in a CompositeProvider(HCPR) with a HANA model may avoid NULL values. However,
- the referential integrity has only an effect if the Nulls are not generated by a left outer join.
- in case the setting 'Referential Integrity' is unjustified, a query may return wrong results (data records with Nulls are missing due to the joins with the corresponding SID tables)
In the following we discuss the this topic with the help of a simple example.
Hana Calculation View with left outer join. The fields of the left opreator have the naming convention XYZ_L. The join fiel is STPE_CUSTOMER.
When we chack the data in the Hana studio (Data Preview) the following result is displayed:
Since the 'right' table does not contain the customer C004, we cann see Null values for the corresponding fields.
This calculation view is now used in a HCPR. When creating a HCPR it is possible to search for Hana Views:
The defintion of the HCPR is straightforward:
In the query there is a filter on the initial value of STPE_CUSTOMER:
The query result is basically the same as in the Hana Studio, the only difference is that instead of Nulls the sign '#' (initial value) is displayed for charcteristic values and zero for key figures (transaction RSRT is used):
If you take a look at the Analytics API statement, you can see that the filter section contains s special retriction (Customer NE *) which handles the Null value:
As mentioned above, if you use the transaction RSDD_HM_PUBLISH to create an Analytic View based on the Hana calculation view, the handling of Null values is not guaranteed. It may happen that filters on initial values do not take into account Null values:
Hence, it is recommended to use instead of such providers a Hana Composite provider.
In our example the setting 'Referential Integrity' wouldn't have an impact on the Null handling (see Overview).