Child pages
  • OT-ISET-P3
Skip to end of metadata
Go to start of metadata

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:

CUSTOMER

MATERIAL

COUNTRY

QUANTITY

PRICE

MILLER

10001

US

5

500 USD

MILLER

10001

DE

7

700 USD

MILLER

10002

US

3

150 USD

MILLER

10002

DE

2

100 USD

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

MATERIAL

STOREHOUSE

QUANTITY

10001

11

5

10001

12

4

10001

13

2

10002

11

20

10002

12

10

10002

13

0

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

CUSTOMER

MATERIAL

QUANTITY

PRICE

QUANTITY

(ZZSALES)

(ZZSALES)

(ZZSALES)

(ZZSALES)

(ZZMATERIAL)

MILLER

10001

5

500 USD

5

MILLER

10001

7

700 USD

5

MILLER

10001

5

500 USD

4

MILLER

10001

7

700 USD

4

MILLER

10001

5

500 USD

2

MILLER

10001

7

700 USD

2

MILLER

10002

3

150 USD

20

MILLER

10002

2

100 USD

20

MILLER

10002

3

150 USD

10

MILLER

10002

2

100 USD

10

MILLER

10002

3

150 USD

0

MILLER

10002

2

100 USD

0

  • 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)

CUSTOMER

MATERIAL

QUANTITY

PRICE

QUANTITY

(ZZSALES)

(ZZSALES)

(ZZSALES)

(ZZSALES)

(ZZMATERIAL)

MILLER

10001

36

3600 USD

22

MILLER

10002

15

750 USD

60

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:

CUSTOMER

MATERIAL

QUANTITY

PRICE

MILLER

10001

12

1200 USD

MILLER

10002

5

250 USD

The result for ZZMATERIAL is:

MATERIAL

QUANTITY

10001

11

10002

30

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

CUSTOMER

MATERIAL

QUANTITY

PRICE

QUANTITY

(ZZSALES)

(ZZSALES)

(ZZSALES)

(ZZSALES)

(ZZMATERIAL)

MILLER

10001

12

1200 USD

11

MILLER

10002

5

250 USD

30

  • No labels