Improve performance - by compressing the fact table #SAP #BW

[MGrob]

Introduction

Even with "high performance hardware" the datavolume in a datwarehouse can get big enough that everyday loaded InfoCubes cause perfromance issues. For that reason bw provides a couple features that help to increase performance. Compressing the fact table is one option that optimized the access to basis infocubes.

What is a compression exactly?

Every InfoCube has a datapackage dimension that holds the request id. This allows bw to store the data in a granularity which is not necessarily required from a business perspective.

Depending on the datamodel of the InfoCube and the frequency of loads as well as the content of the loaded data this can have a significant impact on the datavolume. Further the data is split in multiple packages with each dataload. Those packages do not allow an aggregation and therefore each datapackage is limited within those boundaries.

An aggregation of the data across datapackages by removing the request id can shrink the datavolume of the factable dramatically without any downfall from a business perspective.

To achieve this each InfoCube consist out of two two fact tables one with a request id and one without it. The blue one is the standard fact table with a name either /BI0/F<CUBE> or /BIC/F<CUBE>. The green compressed fact table is named /BI0/E<CUBE> ot /BIC/E<CUBE>.

If you don't do anything specific the E-Table is not generated.

During the compression BW moves data from the normal F-table to the compressed E-Table. By choice the compression can be all or only part of the requests that have been loaded.

The OLAP processor is automatically combining the E and F table during the query.

Collapse data by request id

Compressing a fact table is done in the InfoCube administration and is based on the request id number. Doing a compression can have an impact on the daily business and also affects reporting during this time. Therefore compression is done best during non-reporting hours or weekends.

Conclusion

There are a couple of advantages and disadvantages compressing an InfoCube or not.

Compressed E-Fact table

- Compression cannot be undone and individual requests cannot be differentiated anymore.

- Moving data from F to E table is timeconsuming and during this process a reporting is not possible.

+ Access to compressed data is a lot faster and saves physically space on the database

- Deleting data would have to be done through selective deletion

Uncompressed F-Fact table

+ Allows a lot faster load of transactional data

+ Huge datavolumes due to the fact that the data is stored on request id and datapackage

- BW functionalities allow to delete individual requests and allow more control and no selective deletion required

Improve performance further with partitioning the fact table