Aggregates

In this section, we first show how to create an aggregate for the InfoCube design described in Section 7.3, "InfoCube Design Alternative II-Dimension Characteristics."

Work Instructions

Step 1. Right-click the InfoCube IC_NEWBC2, and then select Maintain aggregates….

 

 

SCREEN 8.1

Step 2. Click as we know the aggregate we need to create.

 

 

SCREEN 8.2

Step 3. Click to create an aggregate.

 

 

SCREEN 8.3

Step 4. Enter short and long descriptions, and then click to continue.

 

 

SCREEN 8.4

Step 5. Select IO_SOFF, IO_SREG, and IO_SREPN2 from the left panel, and then drag and drop each into the right panel.

 

 

SCREEN 8.5

Step 6. Right-click Sales region, and then select Fixed value.

 

 

SCREEN 8.6

Step 7. Select EAST, and then click to continue.

 

 

SCREEN 8.7

Step 8. Select the aggregate, and then click to create the aggregate and fill the aggregate with data.

 

 

SCREEN 8.8

Step 9. To load the aggregate with aggregated data, click to launch a background job.

 

 

SCREEN 8.9

Step 10. To start the background job immediately, click .

 

 

SCREEN 8.10

Step 11. Click to see the status of the background job. A indicates that the background job failed.

 

 

SCREEN 8.11

Step 12. Close the window, and return to the Maintaining Aggregates screen.

 

 

SCREEN 8.12

Step 13. BW opens a message text window in the lower-right corner, displaying the status of creating the aggregate and filling the aggregate with data.

Here we see the error message Dimension IC_NEWBC2U: Error when writing the initial record to the DB table.

 

 

SCREEN 8.13

IC_NEWBC2U is the dimension for the units of measure. It is reasonable to believe that the error was caused by an aggregation of different units of measure. Let's prove our hypothesis.

Step 14. Return to the InfoCube, and then select Delete data from the menu.
 

SCREEN 8.14

Step 15. Click to confirm the deletion.

 

 

SCREEN 8.15

Step 16. Click to delete the data.

 

 

SCREEN 8.16

Step 17. Reload the Table 7.1 sales data again, but this time without the units of measure. Repeat Steps 811. The result should be like the one shown in Screen 8.17, a blank table.

Close the window.

 

 

SCREEN 8.17

Result

The aggregate has been created and filled with data.

SCREEN 8.18

As shown in the right panel of Screen 8.18, BW generated a number 100008 (next to the aggregate long description Aggregate new: IC_NEWBC2 01) as the technical name of the aggregate. To display the aggregate contents, select the long description and click .

Click to display the aggregate contents. Screen 8.20 shows the result.

SCREEN 8.19

The result in Screen 8.20 is an aggregate of the East region sales data at the sales representative level, as specified by the aggregate definition in Screen 8.18. Tables 8.2 and 8.3 show how this result is calculated.

The number in the 0FACTCOUNT column of Screen 8.20 indicates how many fact table records are aggregated to generate a record of the aggregate.

SCREEN 8.20

In Screen 8.18, the F in the Aggregation level column for the Sales region characteristic indicates that the aggregate should be filled with the data of a specified region – in this case, EAST, as shown in the Fixed value column.

Create a query on IC_NEWBC2. This query works much like the one generated in Section 5.3 with the V_SREG variable. After running the query, selecting East when asked to enter a value for V_SREG, and then refreshing Screen 8.18, we see that the aggregate's Usage value increased by 1, and a time stamp was entered under Last used (Screen 8.21). This result means that the query used this aggregate, which is what we want.

Table 8.2. AN EXCERPT OF THE EAST REGION SALES DATA FROM TABLE 7.1

IO_CUST	IO_SREG	IO_SOFF	IO_SREPN2	IO_MAT	IO_PRC	0UNIT	IO_QUAN	IO_REV	0CALDAY
CUST001	EAST	ATLANTA	SREP01	MAT001	2	CS	1	2	19980304
CUST002	EAST	NEW YORK	SREP02	MAT002	2	CS	2	4	19990526
CUST002	EAST	NEW YORK	SREP02	MAT003	5	CS	3	15	19990730
CUST003	EAST	NEW YORK	SREP03	MAT003	5	CS	4	20	20000101

Table 8.3. AN AGGREGATE OF THE EAST REGION SALES DATA AT THE SALES REPRESENTATIVE LEVEL

IO_CUST	IO_SREG	IO_SOFF	IO_SREPN2	IO_MAT	IO_PRC	0UNIT	IO_QUAN	IO_REV	0CALDAY
			SREP01		2		1	2
			SREP02		7		5	19
			SREP03		5		4	20

SCREEN 8.21

To find appropriate aggregates, you use the menu items shown under the Propose pull-down menu (Screen 8.22).

Note

After we run transaction SE11 to display the /BIC/F100008 table definition, we see that the aggregate is a subset of the InfoCube (Screen 8.23). It has the same structure as an InfoCube, and it references the same dimension table.

SCREEN 8.22

SCREEN 8.23

This screen was captured after the aggregate was deleted and re-created several times. For each new aggregate, BW increased the number given to the aggregate's technical name by 1. Therefore, /BIC/F100014, instead of /BIC/F100008, appears in the Transparent table field.

In the same way, we can create an aggregate for the InfoCube created in Section 7.4.

SCREEN 8.24

In Screen 8.24, the H in the Aggregation level column indicates that the aggregate is a summary at a hierarchy level. The value 02 in the Hierarchy level column indicates that the summary is at the second level – the region level – from the hierarchy root (see Screen 7.32 or 7.33).

SCREEN 8.25

The contents of this aggregate appear as shown in Screen 8.25.

The data in Screen 8.26 represent the query result summarized at the region level. This result matches the result in Screen 8.25.

SCREEN 8.26

Note

An aggregate can be created only for key figures for the SUM, MIN, and MAX operations, not the AVG operation.

Aggregates require additional disk space and system maintenance.