If a HCPR uses a join condition with cardinality (1:n;n:1;n:m), the warning 'Ambiguities cannot be resolved without the analytic engine' (Message no. RSQBW143) is issued. It means that in general, the Analytic Engine is necessary to guarantee correct results in all cases. As explained in As explained in Ambiguous Join, in oder to avoid that key figures get duplicated, the query needs to request additional Infoobjects (e.g. all join fields). This may lead to a longer runtime(or higher memory consumption) but ensures correct query results. If you check the data of such a HCPR with the help of LISTCUBE, it is not guaranteed that the results are always as expected!
HCPR contains one DSO and two characteritics connected with inner joins:
The tables just contain a view records. After some mental arithmetic we expect the following overall results (no filter at all) for the 4 key figures:
- ZQUANT2 = 41
- ZQUANT1 = 17
- ZPRICE = 1450
- ZPOPULAT = 37
The cardinality is in both cases n:m:
When checking the provider the following warning message is issued:
In Listcube the following result is displayed:
You can see that the two key figures ZQUANT2 and ZPOPULAT are wrong(see above)! The values are the double of the correct values since the values 1,2 and DE,US of the join fiels exits twice in the central DSO table (in this case the system didn't do the grouping before the join).
We check the adhoc query STPE_ZZSALES/!STPE_ZZSALES of the HCPR. The 'Technical Information' in RSRT contains the following info:
When we have the Hana statement displayed
we see that the query requests the two join fields XCOUNTRY and X_MATL2 although they are not in the drilldown!
The query result is correct: