Local Aggregation
Use
In the global properties for an InfoSet, you can specify whether a local aggregation it to be used.
This setting is used to control how the key figures are grouped.
● If the indicator is set, the key figures are grouped before the join, for all tables involved.
● If the indicator is not set, grouping does not take place until after the table contents are joined. This is the default setting.
This setting affects the query results.
Example
A DataStore object, '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 query, the fields ZZSALES-CUSTOMER, ZZSALES-MATERIAL, ZZSALES-QUANTITY, ZZSALES-PRICE and ZZMATERIAL-QUANTITY, for example, are displayed. The result is as follows:
1. without grouping/aggregation
Each row from the ZZSALES table appears three times in the result, because it has three rows as join partners in the ZZMATERIAL table. Similarly, each row from the ZZMATERIAL table appears twice in the result, because it has two rows as join partners in the ZZSALES table.
2. without local grouping/aggregation before the join (indicator is not set)
The result is grouped/aggregated for all characteristics:
This result does not necessarily match the expected result from a business perspective. If the join condition of the tables involved does not fully cover the entire key of both tables, it is possible that 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.
3. with local grouping/aggregation before the join (indicator is set)
To avoid duplication of key figure values, data from each table involved can be grouped/aggregated before the join is performed. This ensures that the results are as expected and do not include any duplicate records.
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:
