Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Introduction

If joins between tables(partproviders) are used in the definition of an Infoporvider, the Analytic Engine(Manager) has to assure that key figures don't get duplicated when the join is not unique. This is a general problem of join conditions with cardinalities 1:n, n:1 or n:m. Such joins are also called ambiguous joins.

The following very simple example E1 shows the basic problem with such joins: Two tables are joined(inner join) via the field Material:

Table A

MaterialQuantity
M15

Table B

MaterialCustomerCalmonth
M1C110.2018
M1C111.2018

Join Result Set

MaterialCustomerCalmonthQuantity
M1C110.20185
M1C111.20185

When a query is supposed to display the two fields Customer and Quantity, we expect to get 5(and not 10) for Quantity. So, the system shouldn't calculate the sum regarding this key figure in this example. In general, only the Analytic Engine can guarantee correct results in all such cases. E.g. when using the transaction LISTCUBE, it is not guaranteed that the results are always as expected! See HCPR with Non-Unique Joins: Analytic Engine needed.
Unfortunately, the handling of ambiguous joins in the Analytic Engine can lead to performace problems(and/or high memory consumption). This is due to the fact that a higher data volumn has to be transferred to the Analytic Engine(see below).

In order to avoid performance and memory issue for queries it is recommended

  • if possible to avoid ambiguous joins
  • if ambiguous joins needed
    • avoid defining more than one join
    • in case of more joins, try to use always the same join fields

A detailed discussion of this topic is given below.

Even when the Analytic Engine is used to run queries, it can happen that results are incorrect when the join cardinality isn't set correctly. Hence, this has to be checked carefully when providers with joins are defined. There are basically three Infoproviders with which you can define Join conditions between providers:

  • COPR: A CompositeProvider modeled with the help of transactions RSLIMO/RSLIMOBW
  • HCPR: HANA Composite Provider modeled using the BW Modeling Tools(Eclipse based)
  • ISET: Classic BW Infoset modeled with the help of transaction RSISET

See Defining Join Cardinality (Unique/Non-Unique Join).

See also

Detailed Discussion

In the following we refer to HCPRs(basically, the same applies also for the other providers listed above) which use join conditions with cardinalities 1:n, n:1 or n:m. First we introduce a new term called 'Local Grouping'. It is an technical property of an HCPR which depends on the details of the join conditions.

'Local Grouping'

If 'Local Grouping' is possible, then, to some extend, the unwanted multiplication of key figures can already be avoided on the level of the database(which is positive in terms of query performance).

In our simple example E1 from above, it means that the records of table B are aggregated before the join with the table A is carried out. Then the Analytic Engine only gets 1 recdord from the database with the correct value 5 for the key figure Quantity. If 'Local Grouping' is not possible, the Analytic Engine requires in addition to the field Customer the join field Material which leads to a higher data volume transferred from the database. In our example E1, it means that the database transfers two data records to the Analytic Engine. The correct calculation of the key figure value is done in the Analytic Engine.

'Bad Characteristics'

For a certain key figure, all non-join characteristics from the 'opposite' join table are called bad characteristics.

In our example E1 this means the following: For the key figure Quantity the Infoobjects Customer and 0Calmonth are bad characteristics, Material is the join characteristic

See also Example AJ2.

When does a HCPR support 'Local Grouping'for all Key Figures?
  • If there is only one non-unique join
  • In case of more joins, 'Local Grouping' is possible if the join fields are always the same. More precisely, if any join characteristics is a bad characteristics for any key figure, 'Local Grouping' is not possible.

Example where 'Local Grouping' is not supported(for a certain key figure):

  • Table A as above
  • Table B as above
  • Table C has the fields Customer, Country and Amount
  • Join 1: A joind with B via Material
  • Join 2: B joind with C via Customer

Country is a join field and a bad characteristics for the key figure Quantity. Material is a join field and a bad characteristics for the key figure Amount. Hence, 'Local Grouping' is not possible in this cases.

InfoProvider supports 'Local Grouping'(for all key figures)

If a bad characteristic is requested by a query(e.g. it is in the drilldown), then also all join characteristics are needed.

  • Example E1
    • If a query has Customer and Quantity in the drilldown, the Analytic Engine requests, in addition to Customer, the join characteristic Material.
    • If only the key figure Quantity is in the query, the correct aggreagtion and calculation of Quantity is done on the database.
  • Example AJ2

If there is a filter regarding a bad characteristic, this infoobject(plus the join fields) is requested by the Analytic Engine from the database.

  • Example E1
    • Provided there is a filter on 0calmonth and only the key figure Quantity in the drilldown.
  • Example AJ3

Local Grouping is possible for more than one ambiguous join when all the join fields are always identical:

InfoProvider does not support 'Local Grouping'(for all key figures)

Always all bad characteristcs and join characteristics are requested by the Analytic Engine when the query contains affected key figures. So, the behaviour also depends on the key figures used in the query, see Example AJ4.

  • Example E1: We assume that the provider does not suppoprt Local Grouping at all: in such a case, even when the query only has the key figure Quantity, the databare has to transfer the data on the level of Material, Customer and 0Calmonth.
  • Example AJ4

Infoprovider displays warning BRAIN_DEV 300

Unfortunately, it can happen that a HCPR is designed in such a way that it is not possible at all to guarantee correct query results. In such cases the warning BRAIN_DEV 300 is displayed when the HCPR is checked/activated. See Example AJ6.

Documentation

SAP Consulting Notes

  • 2103032  Long runtimes in query on CompositeProvider
  • 2556591  HCPR as source of DTP: retrieved data incorrect
  • 2118286  'COPR/HCPR/ISET: High Query Memory Consumption'
  • 2372430  Ambiguous Joins: LISTCUBE may display incorrect values

 

 

  • No labels