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 transactionRSDDV or context menu of the basis cube.
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
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
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
|
|
1000
|
A
|
17
|
|
2000
|
B
|
15
|
|
2000
|
C
|
44
|
02.2002
|
2000
|
D
|
30
|
E Fact table (aggregate)
Month
|
Customer
|
Sale
|
|
1000
|
17
|
|
2000
|
59
|
|
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.
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
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.
In this way aggregates can be used in performance tuning.
Perfromance tuning can also be achieved using Indexing, Partition and clustering.
This post is extremely radiant. I extremely like this post. It is outstanding amongst other posts that I’ve read in quite a while. Much obliged for this better than average post. I truly value it! top sap institutes in hyderabad
ReplyDelete