Tuesday, July 31, 2018

Zero elimination for cubes

Zero elimination (sometimes called NULL elimination) is process within BW which does deletion of records in fact table where all Key Figure values are zero. This can be normally seen as BW administration task. It can be performed on Manage screen of cubes in RSA1. On tab called COLLAPSE a check box “With Zero Elimination” is available. 

This task can also be done outside from the RSA1 by executing or scheduling a ABAP report RSCDS_NULLELIM. The report calls FM RSCDS_EXEC_NULLELIM for the task execution. In case the BW system is HANA based there is a report RSHDB_EXECUTE_CLEANUP_NULLELIM for HANA DB.

Parameters of program are very well described in this wiki.

From technical point of view notice there are few limitations of this functionality. Main one is that there is no 100% zero elimination for the Key Figures of type FLTP. It is not possible to precisely evaluate whether the KF type of FLTP is really equal to zero. Value of the KF may be close to zero but not precisely zero. Therefore, as word of advice in scenarios that rely on exact summing never use the KF type of FLTP. Report is using value 1.0E-50 which is pretty close to zero but there might be a values stored in the BW that are even smaller.

Also the NULL elimination is only possible if the KF aggregation is SUM for all of the KF used in the cube. If the cube contains the KF with aggregation types other than SUM (e.g. MIN or MAX) the database cannot eliminate the zero lines during the compression.

More information:
1224631 - Program RSCDS_NULLELIM and Float-Keyfigures (Type FLTP)
1587759 - Duplicate Records in InfoCube Dimensions
1820706 - Report RSCDS_NULLELIM fails on SAP HANA
2206835 - Report RSCDS_NULLELIM now also on SAP HANA
2521338 - Compression with Zero Elimination does not work

No comments: