Basics of Cube Aggregates and Data Rollup

What are Cube Aggregates?

  Definition

An aggregate is a materialized, summarized and condensed view of the data in an Info Cube. An aggregate maintain the dataset of an Info Cube redundantly and persistently.

• Summarized and Condensed view refers to the condensing of the fact table of an Info cube to an aggregate table.
• An aggregate table no longer contains certain characteristics of the Info cube and has been condensed across attributes.

When We Create Aggregate on Cube?

Basic purpose of using aggregates is to make data extraction faster.

When we access the data frequently for reporting and we have huge amount of data it takes more time retrieve. If a query is frequently used for reporting and we want performance enhancement then we use aggregates on data source (at Info Cube).

• Aggregation makes data condensed and summarized so you can access the data of an Info Cube quickly when reporting.
• New data is loaded at regular interval (a defined time) using logical data packages (requests) in an aggregate. After this transaction, the new data is available for rolling up in reporting.
• Aggregates are used when we often use navigational attributes in queries or we want aggregation up to specific hierarchy levels for characteristic hierarchies. Both time-dependent attributes and time-dependent hierarchies can be used in aggregates.
• Note:

1. To find our queries frequently used and taking more time we can check RSDDSTAT_OLAP table (TCODE SE11).
2. You can use Table RSDCUBE to determine the Info Cube assigned to the aggregate using aggregate id (6digit).

Prerequisites

• The Info Cube for which we are creating aggregate must be in active state and there should not be any aggregate with same set of attributes for that Info Cube. Every aggregate must be unique.
• If you have created aggregates for an Info Cube and entered data for them, the OLAP processor automatically accesses these aggregates. When navigating, the different results are consistent. The aggregate is transparent for the end user.
• If you want to use system propose aggregates, and then you must create at least one query for the selected Info Cube. The necessary aggregates can be proposed when you start the queries and navigate in them.

Steps to create Aggregate

• Go to TCODE RSA1 and select the InfoCube in which you want to create aggregate and select Maintain Aggregates option.

• When first time we create an aggregate for an Info Cube it asks for type of aggregate.

a. Generate proposals

• The system proposes suitable aggregates. The Specify Statistics Data Evaluation dialog box appears.
• Enter Run time, from date and to date details & Choose Next.
• This will bring you to the Maintain Aggregates screen .The system displays the proposed aggregates in the right area of the Aggregates screen. 

b. Create yourself

• This will bring you to the Maintain Aggregate screen.

• Note : Aggregates are always build on Characteristics not for key figures .

• Drag all characteristic you want to include in Aggregate to write side window pane. You can add characteristics one by one as well. Following screen will appear.

• If you will select later for activation of aggregate following screen will appear where you can give date and time according to your requirement.

• After activation aggregate will show you record available for aggregation ,summarized record count ,last roll up ,last used (in query) etc. details .

This screen will give you following information also:

• Hierarchy and hierarchy level fields are used for aggregates on hierarchies.
• Valuation column will show ‘–‘and ‘+’ sign:

1. The larger the number of minus signs, the worse is the evaluation of the aggregate, “—–” means: The aggregate can possibly be deleted.
2. The larger the number of plus signs, the better is the evaluation of the aggregate, “+++++” means: The aggregate could make a lot of sense.

• Records will tell about number of records in the filled aggregate (size of the aggregate).
• Records Summarized (mean value) will tell about number of records read from source in order to create a record in the aggregate. This shows quality of the aggregate. Large value show better compression (better quality) .If it is 1 the aggregate is a copy of the Info Cube and should be deleted.
• Usage shows how often has the aggregate been used for Reporting in queries?
• Last Used shows when was the aggregate last used for Reporting? If an aggregate has not been used for a long time, it should be deactivated or deleted.

In order to increase the load performance you can follow the below guidelines:

1. Delete indexes before loading. This will accelerate the loading process.
2. Consider increasing the data packet size
3. Un check or remove the Bex reporting check box if the DSO is not used for reporting
4. If you are using abap code in the routines then optimize the code. this will increase the load performance
5. Un check the SID generation check box
6. Write optimized DSO are recommended for large set of data records since there is no SID generation in case of write optimized DSO.This improves the performance during data load.

Steps before using an Aggregate

• To use an aggregate for an Info Cube when executing a query, we must first activate it and then fill it with data.
• To use an existing aggregate select the aggregate that you want to activate and choose Activate and Fill. The system will create an active version of the aggregate.
• Once the aggregate is active you must trigger the action to fill the aggregate with data.
• The active aggregate that is filled with data can be used for reporting. If the aggregate contains data that is to be evaluated by a query then the query data will automatically come from the aggregate.
• When we create a new aggregate and activate it initial filling of aggregate table is automatically done.

Rolling Up Data into an Aggregate

a. ROLL UP

• If new data packages or requests are loaded into the Info Cube, they are not immediately available for Reporting via an aggregate. To provide the aggregate with the new data from the Info Cube, we need to load the data into the aggregate tables at a time which we can set. This process is known as ROLL UP.

b. Steps of rolling up new requests

• In the Info Cube maintenance you can set how the data packages should be rolled up into the aggregate for each Info Cube.
• In the context menu of the required Info Cube select Manage. The Info Provider Administration window appears. In the Manage Data Targets screen select tab Rollup.

• Here from selection button you can set date and time of rollup.

• You can set an event after aggregation and also create a process to run the job periodically. Selective request can be aggregate by providing request id.
• Request can be rolled up based on no of days.

• After rollup you can see the check sign in manage tab. Selecting a request for rollup will also rollup all previous requests loaded before that but not the new one .

Levels of Aggregation

• Aggregation level indicates the degree of detail to which the data of the underlying Info cube is compressed and must be assigned to each component of an aggregate (characteristics, navigation attributes, hierarchies).

Aggregation level can be

          By default, the system aggregates according to the values of the selected objects:

• ‘*’ All characteristic values
• ‘H’ Hierarchy level
• ‘F’ Fixed value

Thank you for reading this blog .Please add your comments .

For similar content follow My Blog

QwertyCuddle | Better data ,better design..

Reference(s)

1. http://help.sap.com/saphelp_nw04/helpdata/en/7d/eb683cc5e8ca68e10000000a114084/frameset.htm
2. http://help.sap.com/saphelp_nw04/helpdata/en/80/1a6791e07211d2acb80000e829fbfe/content.htm
3. http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/906de68a-0a28-2c10-398b-ef0e11ef2022?QuickLink=index&overridelayout=true