If joins between tables(part providers) are used in the definition of an Infoprovider, 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:
Join Result Set
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.
When you check the definition of an affected HCPR in the BW Modeling Tools or in transaction RSOHCPR, the warning 'Ambiguities cannot be resolved without the analytic engine' (Message no. RSQBW143) is issued(see also See HCPR with Non-Unique Joins: Analytic Engine needed).
Unfortunately, the handling of ambiguous joins in the Analytic Engine can lead to performance problems(and/or high memory consumption). This is due to the fact that a higher data volume 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 are 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 Infoprovider types 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
- 2103032 Long runtimes in query on CompositeProvider
- Multiplication of key figures: e.g. pages 9-18
- KBA 2118286 'COPR/HCPR/ISET: High Query Memory Consumption'
Important Coding Corrections
Please assure that following notes are implemented:
- 2769505 Performance Improvement for Queries with Ambiguous Join
- 2719253 HCPR query returns wrong when ambiguious joins are involved
- 2623524 A Query with ambiguous Join delivers wrong data
- 2618599 Multiplied data in a Query on top of a CompositeProvider with multiple joins and ambiguties
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 a technical property of an HCPR which depends on the details of the join conditions.
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 record 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.
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 joined with B via Material
- Join 2: B joined 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 aggregation 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:
Basically, the rules discussed on this page also apply to Classic BW Infosets(modeled with the help of transaction RSISET). In the following example we use a sample query based on an Infoset and then convert the InfoSet to an analog HCPR:
InfoProvider does not support 'Local Grouping' for all key figures
- Always all join characteristics are requested by the Analytic Engine when the query contains affected key figures. So, the behavior also depends on the key figures used in the query.
- In case there is a query restriction regarding a bad characteristic, this InoObject is added to the Requested Attributes (of the statement)
- Example E1: We assume that the provider does not support Local Grouping at all: in such a case, even when the query only has the key figure Quantity and no characteristic in the drilldown, the database has to transfer the data on the level of the join field Material.
- 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.
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