Infocube Compression in SAP BW
Frequency: Daily
InfoCubes should be compressed regularly. Uncompressed cubes increase data volume and have a negative effect on the query and aggregate build performance. If too many uncompressed requests are allowed to build up in an Info Cube, this can eventually cause unpredictable and severe performance problems. Basically the F-fact table is optimized for writing (upload) and the E-fact table is optimized for reading (queries).
A well run and high performing BW system is not possible unless there is regular compression of
InfoCubes and aggregates. A regular compression strategy should be defined with the business data owners. In line with the requirements of the business process, data in the F fact table should be compressed regularly. For more information refer to the documentation in the SAP Help Portal:
http://help.sap.com àSAP NetWeaverà Business Information Warehouse à Administrator
Workbench à Managing Data Targets à Managing InfoCubes à InfoCube Compression
Technical description
During the upload of data, a full request will always be inserted into the F-fact table. Each request gets its own request ID and partition (DB dependent), which is contained in the ‘package’ dimension. This feature enables you, for example, to delete a request from the F-fact table after the upload. However, this may result in several entries in the fact table with the same values for all characteristics except the unnecessarily and consequently decrease the performance of your queries. During compression, these records are summarized to one entry with the request ID ‘0’. Once the data has been compressed, some functions are no longer available for this data (for example, it is not possible to delete the data for a specific request ID).
Transactional InfoCubes in a BPS environment
You should compress your InfoCubes regularly, especially the transactional InfoCubes. In a BPS planning process, a request is closed when the open request contains 50.000 records or when it is switched manually between loading and planning mode.
For transactional InfoCubes that are used for BPS there is another advantage through compression (on ORACLE databases): The F-fact table has a B-tree index and is partitioned according to request ID, whereas the E-fact table has a bitmap index and partitioning according to your settings (time characteristics). Read accesses to the E-fact table are faster than those to the F-fact table because Btree indices are favorable for data write processes but not for read processes.
The BPS delete function is used to remove data from the selected planning package. The records are not directly deleted from the InfoCube. Instead, the system creates additional records with offsetting values. The original and offsetting record are deleted when the InfoCube is compressed.