Child pages
  • BEX-OT-ISET-E3
Skip to end of metadata
Go to start of metadata

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.

 

 

  • No labels