OT-ISET-P3

This flag is used to set how the key figures are grouped.This affects the query results.

(Note: This option was introduced with BW 7.3 - see KBA 2152282)

• If the flag is set, the key figures are grouped before the join for all tables involved.
• If the flag is not set, grouping does not take place until after the table contents are joined.

Caution: Using grouping before the join can have a negative impact on performance if several ambiguous joins are involved, as the join
sequence can no longer be selected by the database optimizer.

Simple Example

DataStore 'ZZSALES' contains the following data records:

Another DataStore object, 'ZZMATERIAL', contains the following data
records and provides an overview of the materials and quantities
available in the different storehouses:

In the InfoSet, the two InfoProviders are joined:

ZZSALES-MATERIAL with ZZMATERIAL-MATERIAL

In the report, fields ZZSALES-CUSTOMER, ZZSALES-MATERIAL,
ZZSALES-QUANTITY, ZZSALES-PRICE and ZZMATERIAL-QUANTITY are displayed
for example. The result is as follows:

Global Property: 'Additional Grouping before Join' INACTIVE

Intermediate Result Set

• Each row from table ZZSALES appears three times in the result because it has three rows as join partners in the table ZZMATERIAL. Similarly, each row from table ZZMATERIAL appears twice in the result because it has two rows as join partners in table ZZSALES. If the result is now grouped/aggregated using all characteristics, the outcome is the result specified under 'without local grouping/aggregation before the join'. This result does not necessarily match expectations from a business perspective.
• If the join condition of the tables involved does not fully cover the entire key of both tables, multiple data records from a table are found that satisfy the join condition. In this case, the join is not unique and the key figure values are duplicated.
• The problem can be solved by first sending a query that groups/aggregates the data from each table involved before the join is performed(see below). This makes it possible to ensure that the result does not contain any duplicate records (see 'with local grouping/aggregation before the join'); the result thus matches expectations.

• Without local grouping/aggregation before the join (the flag is not set)

Global Property: 'Additional Grouping before Join' ACTIVE

With local grouping/aggregation before the join (flag is set) The tables involved are first aggregated.

The result for ZZSALES is:

The result for ZZMATERIAL is:

• The join is then made and the end result is: