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.