Infoset: Results depend on Navigational State if Join is not Unique
DataStore 'ZZSALES'
CUSTOMER | MATERIAL | COUNTRY | QUANTITY | PRICE |
|---|---|---|---|---|
MILLER | 1001 | US | 1 | 500 USD |
MILLER | 1001 | DE | 2 | 700 USD |
MILLER | 1002 | US | 3 | 150 USD |
DataStore object 'ZZMATERIAL'
MATERIAL | STOREHOUSE | PIECES |
|---|---|---|
1001 | S1 | 10 |
1002 | S2 | 20 |
In the InfoSet, the two InfoProviders are joined as follows:
ZZSALES-MATERIAL with ZZMATERIAL-MATERIAL
In contrast to the DSO ZZSALES, in the DSO ZZMATERIAL the field Material is a key field and hence we have a 1:N Join Cardinality. The DSO ZZSALES we call N-part of the Join, the DSO ZZMATERIAL is called 1-part. If we assume that a query has CUSTOMER,MATERIAL, COUNTRY and STOREHOUSE is in the drilldown we get the following result for the key figures Quantity and Pieces:
CUSTOMER | MATERIAL | COUNTRY | STOREHOUSE | QUANTITY | PIECES |
|---|---|---|---|---|---|
MILLER | 1001 | US | S1 | 1 | 10 |
MILLER | 1001 | DE | S1 | 2 | 10 |
MILLER | 1002 | US | S2 | 3 | 20 |
Please note that the SUM of all values for the key figure PIECES is 40. When the infoobject COUNTRY is removed from the drilldown the query result changes to
CUSTOMER | MATERIAL | STOREHOUSE | QUANTITY | PIECES |
|---|---|---|---|---|
MILLER | 1001 | S1 | 3 | 10 |
MILLER | 1002 | S2 | 3 | 20 |
The sum of PIECES is now 30! The key figure QUANTITY hasn't changed since it belongs to the N-part of the Join.
Basically this is the same issue than discusssed in the example 'Key figures are multiplied'. Normally it is not required to sum up such a key figure as 'Pieces'. Hence it is recommended to use the setting 'Grouping before Join'. In this case, the OLAP Engine would not aggregate the key figure 'Pieces' over the characteristic Material for a certain material values. So, in a corresponding query the 'yellow' result line for material 1001 would be 10, and the overall result would be 30. Please review SAP note 2118286 "CPR/HCPR: High Query Memory Consumption" and the wiki page CPR:Unique Join where you can find a simple instructive example.