Child pages
  • Defining Join Cardinality (Unique/Non-Unique Join)
Skip to end of metadata
Go to start of metadata

As explained in Ambiguous Join, if a Join condition is used in the definition of an Infoporvider, it might happen that a query does not display correct values. Other symptoms could be that the query performance is bad or the memory consumption is higher than expected. There are basically three Infoproviders with which you can define join conditions between providers, all offer an option where you can specify whether the join is unique or not:

  • COPR: If you are modeling the CompositeProvider via transactions RSLIMO/RSLIMOBW, the option is set on level of the joined provider. You can select the provider, right mouse click, check the option: Unique Join Columns.
  • HCPR: If you are modeling an Eclipse based HANA Composite Provider created with the BW modeling tools, the relevant option is called 'Join Cardinality' (tab 'Scenario' - context menu on the 'JOIN' - 'Edit Cardinality'). If you choose 'Join Cardinality=1:1', the system assumes that the join is unique.
  • ISET: If you are modeling an Infoset with the transaction RSISET, the relevant is setting called 'Additional Grouping before Join'. If this option is switchen on, the system assumes that the join is not unique.

In the following we discuss this topic with the help of a simple example. The providers below(COPR/HCPR/IEST) always contain the following 2 DSOs where the characteristic STPE_MAT is used as the join field:

  • STPEDSO7
    • key field: STPE_MAT 'Material'
    • key figure: 0D_FC_NETSA 'Net sales'
  • STPEDSO8
    • key field: STPE_CUST 'Customer'
    • data field: 0CALMONTH
    • key figure: STPE_KF1 'Counter'

These DSOs only contain a view data records:

 

Please note that in the DSO STPEDSO8 the characteristic STPE_MAT is not unique, e.g. there are three records where STPE_MAT = M001.

COPR: 'Unique Join Columns'

Definition of the CompositeProvider:

 

The join field is STPE_MAT, which is the key field of the DSO STPEDSO7 !

This is a non-unique join (1:N), since the field STPE_MAT is not a key field in the DSO STPEDSO8! Hence the Analytical Engine has to assure that key figures of the DSO STPEDSO7 are not multiplied. When the CompositeProvider is activated, the following warning is issued:

RSQBW 143: Ambiguities cannot be resolved without the analytic engine, see long text and HCPR with Non-Unique Joins: Analytic Engine needed

Query Result:

You can see that the key figure 'Net Sales' is not aggregated regarding characteristcs (0calmonth) of the DSO STPEDSO7 (N part) ! In order to be able to do so, the OLAP Engine needs to have also all 'Join Fields' (here STPE_MAT) in the drilldown (internally). Hence, when we remove STPE_MAT from the drilldown, the query still requests this characteristic from the data base(when 0calmonth is still in the drilldown, see Ambiguous Join for details)!

Therefore the 'Technical Information' in RSRT contains the following info:

Such 'invisible' drilldown characteristics can lead to a high data volume and hence to performance and memory issues!

If the setting 'Unique Join' is used for this COPR (although the data in the DSO is NOT unique regarding STPE_MAT), the query displays incorrect data:

E.g. 150 EUR is wrong by a factor of 3 for the material M001.

In general, if

  • the Join is by definition (meta data) non-unique, but the data records in the tables (N part) are such that the join is actually unique, then it is possible to set the corresponding option to 'unique' in order to avoid performance issues.
  • the Join is non-unique (also the data is non-unique), then the unique join condition must not be set! Otherwise the query displays wrong results!

 

HCPR: 'Join Cardinality'

The discussion from above also applies for the HANA Composite Provider. The only difference is the description of the relevant seitting:

The system uses by default the 'calulated join cardinality' which is 1:N in our case. Changing this setting would lead to a wrong query result as above:

 

ISET: 'Additional Grouping before Join'

The discussion from above also applies for the BW Infoset. Here the relevant seitting is called 'Additional Grouping before Join':

 

 

  • No labels