Buy

Buy

List of Posts

Get this widget
To get notifications, Please like our Facebook Page >>>>>>

Use Search box to find a topic in this Blog!!!

Aggregate Data in the Update Rules for Performance Improvement


How to…
Aggregate Data in the Update Rules for Performance Improvement


1 Business Scenario
You have an InfoCube that is designed for highly aggregated reporting. On its way to this InfoCube
the source data is aggregated in the update rules because the source data is on a lower level of
detail (granularity) than required in the InfoCube. For instance, the InfoCube requires data on a
monthly level whereas the source data is on daily level. When loading this data a significant part of
the loading time is spent in the update rules. You would like to improve the performance of the update
rules.

2 Introduction
Aggregation in the above-described scenario is usually automatically done in the update rules if some
of the characteristics like e.g. “calendar day” are not used in the InfoCube. However, this aggregation
is done after the update rules have been processes as defined for each record. The idea of the
performance improvement described in this paper is that the aggregation is done already in the start
routine. Thus, the update rules that are processed for each record have to be processed for a smaller
amount of records.

This approach should only be considered if the time spent in the update rules is significant and if the
aggregation ratio of the number of incoming source records and the number of records stored in the
InfoCube is high.

It is not possible to use the scenario if the order of aggregation and update rules can’t be changed.
For instance, the low level of detail is needed in the update rules themselves or calculations and
transformations of data take place that are not commutative with aggregation. That’s why this
approach must be carefully analyzed and tested for each InfoCube before it is used.

3 The Step By Step Solution

A start routine for the update rules is created or changed and the coding for the aggregation is added.

1. Create a Start Routine for the Update Rules

1. Create (or change an              
existing) start routine for
the update rules of the
InfoCube.


2. Do the following (or                
similar) declarations





3. Add the following code.                
Replace the field names
in the CLEAR statement
with the fields that should
be aggregated.











2. Testing and Monitoring

It is absolutely necessary to test whether query results are (still) correct after using these new update
rules. In the following some “high level” checks are described that should be used in addition to query
testing. For these tests it is assumed that the same data is loaded twice, with and without the
enhancement in the start routine.

1. The request overview in the InfoCube management shows the number of transferred and added records. In our example request 176296 was loaded without aggregation in the start routine
(in the default way), whereas request
177003 was loaded with the desribed
enhancement in the start routine. Both
requests have the same amount of
transferred and added records, which is
correct.

2. In the detail view of the monitor you can compare the individual data packages of the two requests. In request 176296 data package 3 was reduced from 10000 (transferred) to 5033 (added) records.




3. The same view for request 177003   
shows an additional line indicating that
the same reduction has already been
done in the start routine.




4. When you compare query results   you
can use the Request ID from the Data
Package dimension as free
characteristic and filter by a single
request.










5. You can also display the Request ID in
the query result and filter by both
requests in one query navigation.












No comments:

Post a Comment

Note

This blog is solely for the purpose of getting educated in SAP. This blog does not encourage distribution of SAP hold copyright and any other publication and/or materials bearing SAP mark. If you find any copyright materials, please mail me so I can remove them. This blog is not in association with SAP.

ALL Posts

Get this widget