PERFORMANCE ISSUES
Here you can find the options that allow you to affect not only the load performance but also the query performance.
1. Database Indexes
With an increasing number of data records in the InfoCube, not only the load but also the query performance can be reduced. This is attributed to the increasing demands on the system for maintaining indexes. The indexes that are created in the fact table for each dimension allow you to easily find and select the data. You should not create indexes when initially loading data into the Info Cube. Create the indexes after you have created the Info Cube.
The indexes displayed are the secondary indexes of the F and E fact tables for the Info Cube. The primary indexes and those defined by the user are not displayed. The aggregates area deals with the corresponding indexes of the fact table for all aggregates of an Info Cube.
Checking Indexes
Using the pushbutton Check indexes, you can check whether indexes already exist, and whether these existing indexes are of the correct type (bitmap indexes).
Yellow status display: There are indexes of the wrong category
Red status display: No indexes exist, or one or more indexes are faulty
Deleting Indexes
For delta uploads with a large quantity of data (more than a million records), you should not align the database indexes of the Info Cube with every roll up, rather delete the indexes first, and then completely reconstruct them after rolling up.
Repairing Indexes
Using this function you can create missing indexes or regenerate deleted indexes. Faulty indexes are corrected
Aggregate Table Indexes
Just as for the indexes of the fact table, you can maintain the indexes of the aggregation tables. Maintaining the indexes of the aggregation tables affects all the aggregation tables of the Info Cube.
If the Info Cube contains more than 50 million records, you should, as a rule, refrain from deleting and recreating indexes.
Database Statistics
Database statistics are used by the system to optimize the query performance. You should keep the database statistics up-to-date for this reason.
It is recommended that you always update the statistics if you have loaded more than a million new records in the InfoCube since the last update.
Using the pushbutton Check statistics, you can check the InfoCube to see if statistics exist. If no statistics exist yet, the status display changes to red. Using Recalculate statistics, you can add the missing InfoCube statistics in the background.
You can determine the percentage of InfoCube data, that is used for creating the statistics. The percentage is set to 10% by default. The larger the InfoCube, the smaller you should choose the percentage, since the demand on the system for creating the statistics increases with the change in size.
For up to 10 million entries in the InfoCube, you should set the percentage of InfoCube data used for creating the statistics to 100%.
2.Compressing
Use
When you load data into the InfoCube, entire requests can be inserted at the same time. Each of these requests has its own request ID, which is included in the fact table in the packet dimension. This makes it possible to pay particular attention to individual requests. One advantage of the request ID concept is that you can subsequently delete complete requests from the InfoCube.
However, the request ID concept can also cause the same data record (all characteristics agree, with the exception of the request ID) to appear more than once in the fact table. This unnecessarily increases the volume of data, and reduces performance in Reporting, as the system has to aggregate using the request ID every time you execute a query.
Using compressing, you can eliminate these disadvantages, and bring data from different requests together into one single request (request ID 0).
This function is critical, as the compressed data can no longer be deleted from the InfoCube using its request IDs. You must be absolutely certain that the data loaded into the InfoCube is correct.
Functions
You can choose request IDs and release them to be compressed. You can schedule the function immediately or in the background, and link it to other events.
Compressing one request takes approx. 2.5 ms per data record.
With non-cumulative InfoCubes, compression has an additional effect on query performance. Also, the marker for non-cumulatives in non-cumulative InfoCubes is updated. This means that, on the whole, less data is read for a non-cumulative query, and the reply time is therefore reduced.
If you run the compression for a non-cumulative InfoCube, the summarization time (including the time to update the markers) will be about 5 ms per data record.
If you are using an Oracle database as your BW database, you can also carry out a report using the relevant InfoCube while the compression is running. With other manufacturers’ databases, you will see a warning if you try to carry out a report using an InfoCube while the compression is running. In this case you can only report on the relevant InfoCube when the compression has finished running.
If you want to avoid the InfoCube containing entries whose key figures are zero values (in reverse posting for example) you can run a zero-elimination at the same time as the compression. In this case, the entries, where all the key figures are equal to 0, are deleted from the fact table.
Zero-elimination is permitted only for InfoCubes, where exclusively key figures with the aggregation behavior ‘SUM’ appear. You are not permitted to run a zero-elimination with non-cumulative values in particular.
Activities
For performance reasons, and to save space on the memory, summarize a request as soon as you have established that it is correct, and is no longer to be removed from the InfoCube.
3.AGGREGATES
An aggregate stores the dataset of an InfoCube redundantly and persistently in a summarized form on the database.
When building an aggregate from the characteristics and navigation attributes from an InfoCube, you can group the data according to different aggregation levels. Remaining characteristics that are not used in the aggregate are summarized.
New data is loaded into an aggregate using logical data packages (requests). You differentiate between filling and rolling up with regard to loading data.
Use
Aggregates make it possible to access InfoCube data quickly in Reporting. Aggregates serve, in a similar way to database indexes, to improve performance.
We recommend that you create aggregates, especially in the following cases:
- The execution and navigation of query data leads to delays with a group of queries.
- You want to speed up the execution and navigation of a specific query.
- You often use attributes in queries.
- You want to speed up reporting with characteristic hierarchies by aggregating specific hierarchy levels.
Aggregation Levels
An aggregation level indicates the degree of detail to which the data of the underlying InfoCube is compressed
An aggregation level must be assigned to each component of an aggregate (characteristics and navigation attributes). You can choose one of the following aggregation levels:
- All characteristic values ("*"): The data is grouped by all the values of the characteristic or navigation attribute.
- Hierarchy level ("H"): The data is grouped by the hierarchy level node.
- Fixed value (‘F’): The data is filled according to a single value
Structure
|
Column |
Information |
|
Aggregates |
Aggregates with all transferred components in a tree display |
|
Technical name |
|
|
Save |
|
|
Proposed action |
The system proposes actions if necessary
Once the system has proposed a new aggregate, it recommends that you activate this aggregate. This is marked by a |
|
Status |
|
|
Filled/switched off |
|
|
Refers to individual components in the aggregate: |
By default the system aggregates by value of the selected objects:
You can change this setting from the context menu of the displayed aggregation level. |
|
If necessary, name of the selected hierarchy |
|
If necessary, selected level of the hierarchy |
|
If necessary, selected fixed value |
|
Evaluation |
Representation in a bar chart:
"-----" means: The aggregate can possibly be deleted.
"+++++" means: The aggregate could make a lot of sense. The evaluation is based on a number of criteria. At the moment the following is used in the evaluation:
|
|
Records |
Number of records in the filled aggregate. This information tells you something about the size of the aggregate. |
|
Records compressed (mean value) |
Number of records read on an average from the source in order to create a record in the aggregate.
Given an aggregate with three records. For example if 10 records were read for the first record in the aggregate, 15 records were read for the second record and 20 records for the third record, the aggregate has a mean of 15 "records compressed". This information tells you something about the quality of the aggregate.
Since an aggregate should be 10 times smaller than its source, the number should be larger than 10.
|
|
Use |
Number of uses (in queries). How often was the aggregate used for Reporting |
|
Last usage |
Date. When was the aggregate last used for Reporting? If an aggregate has not been used for a long time, deactivate or delete it.
Note that certain aggregates cannot be used at certain times (for example during vacation). Do not delete basic aggregates that you created to speed up the hierarchy/attribute change run. |
|
Last roll-up |
Date. When was the last time that data was entered for the aggregate? |
|
Last roll-up by |
User name of the person who scheduled the roll-up. |
|
Last changed on |
Date. When was the aggregate definition last changed? |
|
Last changed by |
User name of the person who made the last change |
Marks new or changed aggregates
in this column.
|
|
