Simple example to the topic Ambiguous Join.
The following two ADSOs are used in an HCPR. Only the key figure STPEQUNT2 is used in the simmple test query below.
|ADSO STPE_AJ01 (Sales Data)||ADSO STPE_AJ02 (Stock Data)|
HCPR with Ambiguous Join
THe HCPR joins the two ADSOs via Material(STPE_MAT = STPEMAT2) with an inner join. The cardinality is n:m.
The cardinality is n:m since the join fields 'Material' are not the only key fields in both ADSOs. Since we have only one join, this HCPR support Local Grouping (see Ambiguous Join).
If you check such a Provider in the BW Modeling Tools or in transaction RSOHCPR, the warning 'Ambiguities cannot be resolved without the analytic engine' (Message no. RSQBW143) is displayed. See also HCPR with Non-Unique Joins: Analytic Engine needed.
The test query has only the key figure STPEQNT2 and the characteristic STPE_CUST in the drilldown, there are no filters.
The relevant part of the data contains the key figure and all join and mad characteristics(see Ambiguous Join).
The value 11 is displayed three times(since we have three records for material M001). Since the query is only 'interested' in the key figure per customer, the system has to aggregate over Country and Material. In this case the provider supports Local Grouping and hence the aggregation over 0Country is already done on the database. In order to be able to calculate correctly the result lines, the Analytic Engine needs the join field in the drilldown. Hence, when you take a look at the trex statement(see below), you can see that the field STPEMAT2 is selected.
Hence, the Analytic Engine basically gets the following data set:
The overall result should take 11 only once into account, so, the correct value should be 46(and not the sum which would be 57). Let's check whther the query is working correctly.
We run the query in Transaction RSRT and use the button Execute&Debug:
In the Trex statement(see SQL / HANA Analytics-API ) we see that, in addition to the characteristic STPE_CUST, the join field STPEMAT2 is selected:
The overall result is correct(46 and not the sum which would be 57).
When we take a look at the Technical Information of this query in RSRT, we see the following
For the key figure we have one join characteristic and four bad characteristics (called Infoset). Depending on the query definition, all of them may be used as non-visible drilldown characteristics. In case e.g. STPEATTR3 were not used in the query at all, it would not appear here in the list of mad characteristics.
We run the same query again and remove STPE_CUST from the drilldow. In this case, as explained in Ambiguous Join, the necessary aggregations are already carried out correctly on the database: