Skip to Content

Performance Tuning

In data Warehouse, the data volume can grow into sizes that may cause performance problem even for high performance hardware. For this reason SAP BW offers, options to improve the data analysis performance.

Approaches to Optimization related to data model of basic cube:

·         Aggregation

·         Indexing

·         Partition and clustering

·         Use of dedicated OLAP memory (BIA)

In this document we are basically focusing on aggregates. What are aggregates, how to fill the aggregates and of how they work.

Aggregate: An aggregate is redundant basic cube data storage with only a subset of basis cube data.

Aggregates are memory intensive; however they are highly flexible and can be largely adjusted to the reporting requirements. With high volume of data they are the most important tuning measure for data analysis.

An aggregates can always be used in reports when no other information is required in the report, than which is available in the aggregates. The decision on whether or not an aggregate will be used for analysis will not be transparent for the user. It will be decided by the analytical engine.

For each basis cube a discretionary number of aggregates can be created with the transaction RSDDV or context menu of the basis cube.

/wp-content/uploads/2013/02/1_183380.png

Initial filling of aggregates

Aggregates are created when the respective basis cubes contain data. Right after the creation of an aggregate, it has to be filled initially to have the same dataset as the respective basis cube. This can be done in the aggregate maintenance under the menu item aggregate -> activate and fill.

Depending on the size of basis cube, reading the ‘F fact table’ can be very time consuming and may not be really required, because there might already be other aggregates which can be used as a database

/wp-content/uploads/2013/02/2_183381.png

There are several limitations while an aggregate is being built.

·         There can be no roll up for the aggregate.

·         No change run is possible if the aggregate disposes off master data attribute.

As the limitation described may exist for a period of several hours, it is advisable to use specific time slot to initially build aggregate.

With a new creation, the aggregates are filled from the respective basis cube. The newly added data of basis cube will be transferred to aggregates via process called ROLLUP

/wp-content/uploads/2013/02/3_183382.png

The data which is to be transferred to the aggregates, the corresponding ID can be entered in Request ID in Roll Up tab.

Working of Aggregates

The reduction of data volume in an aggregate may be achieved by reduction in granularity or the accumulation of subsets. Usually both the options are combined.

The reduction in granularity is achieved, if the amount of Info Object that defines the granularity of cube, only a subset is filled into the aggregates.

Aggregates for Characteristics

There are two fact table F fact table for the cube and E fact table for aggregates. All characteristics that are defined in cubes but not filled in into an aggregate are aggregated in such a way that the detailing level of aggregate is limited to characteristics that are filled into the aggregates.

F Fact table (Cube)

Month

Customer

Material

Sales

  1. 01.2002

1000

A

17

  1. 01.2002

2000

B

15

  1. 01.2002

2000

C

44

02.2002

2000

D

30

E Fact table (aggregate)

Month

Customer

Sale

  1. 01.2002

1000

17

  1. 01.2002

2000

59

  1. 02.2002

2000

30

Attribute Change Run

Whenever there is a change in master data, we have to execute a change run, because changes in master data cause changes in navigational attributes or hierarchies. To insure consistency in reporting results, data in aggregates have to be adjusted after the master data load.

By executed the change run, the data in aggregates is adjusted and the modified version of navigational attributes and hierarchies turns into active version.

                     

/wp-content/uploads/2013/02/4_183386.png

Aggreagtes(Before change run)                                 Aggregate (After change run)

Attribute

Sales

X

80

Y

40

Attribute

Sales

X

100

Y

20

                           

                 

It is carried out from the Tools Menu and selecting Apply Hierarchy/Attribute Changes

/wp-content/uploads/2013/02/5_183387.png

The changes in Master Data will be effective after executing the change run only, and during this process the reporting can be done on Old Master Data and hierarchies.

/wp-content/uploads/2013/02/6_183389.png

In this way aggregates can be used in performance tuning.

Perfromance tuning can also be achieved using Indexing, Partition and clustering.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Anu V

    Dear Pallavi,

    Its a very good document and with screenshots which you attached, its very easy to understand and learn.

    Thanks ๐Ÿ™‚

    (0) 

Leave a Reply