Aggregates - SAP BW

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

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

Step 2. Click create-urselfas we know the aggregate we need to create.
Proposals for aggregates

Step 3. ClickClick to create an aggregate.
Maintaining aggregates

Step 4. Enter short and long descriptions,and then clickclick to continue.
description of the aggregate

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

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

Step 7. Select EAST,and then clickclick to continue.
Sales region

Step 8. Select the aggregate,and then clickclick to create the aggregate and fill the aggregate with data.
Maintaining aggregates

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

Step 10. To start the background job immediately,clicknow.
Execution time of the aggregation

Step 11. ClickClick to see the status of the background job. A indicates that the background job failed.
Aggregates of an Infocube

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

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.
Maintaining aggregates

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.
Administrator work bench modelling

Step 15. ClickYes to confirm the deletion.
Delete contents

Step 16. Clickdelete entries to delete the data.
Delete Info cubeODS contents

Step 17. Reload the Table sales data again,but this time without the units of measure. Repeat Steps 8–11. The result should be like the one shown in Screen,a blank table.Close the window.
Aggregates of an Infocube

Result:
The aggregate has been created and filled with data.
Maintaining aggregates

As shown in the right panel of Screen,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 Clickto display the aggregate contents. Screen shows the result.
Info cube Browser

The result in Screen is an aggregate of the East region sales data at the sales representative level,as specified by the aggregate definition in Screen. Tables show how this result is calculated.The number in the 0FACTCOUNT column of Screen indicates how many fact table records are aggregated to generate a record of the aggregate.
Info cube browser:

In Screen,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 with the V_SREG variable. After running the query,selecting East when asked to enter a value for V_SREG,and then refreshing Screen,we see that the aggregate's Usage value increased by 1,and a time stamp was entered under Last used (Screen). This result means that the query used this aggregate,which is what we want.

AN EXCERPT OF THE EAST REGION SALES DATA FROM TABLE
AN EXCERPT OF THE EAST REGION SALES DATA FROM TABLE
AN AGGREGATE OF THE EAST REGION SALES DATA AT THE SALES REPRESENTATIVE LEVEL
AN AGGREGATE OF THE EAST REGION SALES DATA AT THE SALES REPRESENTATIVE LEVEL

Maintaining aggregates

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

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


dictionary display table

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.
Maintaining aggregates

In Screen,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.
Info cube browser

The contents of this aggregate appear.The data in Screen represent the query result summarized at the region level. This result matches the result.
Contents of this aggregate appear

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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SAP BW Topics