Query based on InfoSet containing an Ambiguous Join
In the following we discuss a simple example to the topic Ambiguous Join where the query is based on an InfoSet. In a second step we convert(copy) the InfoSet to an HCPR(using report RSO_CONVERT_IPRO_TO_HCPR) and compare two analog queries. As explained in Ambiguous Join and note 2118286, the join cardinality has to be set correctly in order to assure correct query results. We first do not use the correct cardinality and show how this can lead to wrong values in a query. We also discuss under which circumstances the query can deliver the expected results even when the cardinality is not set to non-unique.
When the report RSO_CONVERT_IPRO_TO_HCPR is used to create the corresponding HCPR, the join cardinality is set to the value determined by the system(derived from the definition of the join(join fields) and the key fields of the affected providers). Hence, it might happen that be default the HCPR uses a different join cardinality in comparison to the InfoSet.
InfoSet based on an InfoCube and a DSO
The join field is STPE_MAT. In both providers there are more key fields apart form the join field and hence the join is non-unique. We start with the formally incorrect setting where the property 'Additional Grouping Before Join' is inactive(see 2118286):
Data of the PartProviders
Hence, e.g. for the material M001 we expect to get the value 6(3+1+2) displayed in a query(without filters) for the key figure ZPOPULAT.
Query based on InfoSet
We run the query in Transaction RSRT the following result is displayed:
The value 12 is incorrect! Since the material M001 exists twice in the DSO and since the property 'Additional Grouping Before Join' is not flagged, the value 6 gets doubled. Since the other two materials only occur once in the DSO, the numbers are correct. Therefore, the query result does not only depend on the definition of the join(and the key fields of the operands) but, of course, also on the data stored in the providers. In case it can be guaranteed that the data in the providers is such that the join is actually unique, it is possible to work with an inactive setting 'Additional Grouping Before Join'(see also note 2118286). This has the advantage that the query performance can be (much) better, however, it has the downside that the query would deliver wrong values(without warning) when the data in the providers do not fulfill the restriction of uniqueness any longer.
When we change the InfoSet property to:
the query displays the correct value 6!
In the Technical Information of the query(RSRT) the list of 'Non-Visible Drilldown Characteristics'(see e.g. Ambiguous Join Example 2) has changed:
'Non-Visible Drilldown Characteristics' inactive:
'Non-Visible Drilldown Characteristics' active:
Creating an HCPR out of the InfoSet Definition
We use the report RSO_CONVERT_IPRO_TO_HCPR to create the HCPR:
Query STPE_ISET_CONVERSION is copied to STPE_ISET_CONVERSION_HCPR:
Protocol of Simulation Mode
When we check the definition of the HCPR in the BW Modeling Tools, we recognize that the system chose the cardinality n:m which is correct from a logical point of view(since in both providers are additional key fields to the join field STPE_MAT):
This means that the cardinality can changed after the conversion and that it is recommended to check this setting anyway.
The query displays as expected the correct result(in Transaction RSRT):