How to eliminate zero values from ADSO

Direct update DSOs and InfoCube-like ADSOs can be used in a variety of ways in planning. However, over time, many zero data sets accumulate that cannot be deleted. In this article I will show you a trick on how to get rid of the zero records.

Direct Update DSO

Direct Update DSOs are often used as control tables in planning, for example to allow the user to switch planning cycles or to set flexible data slices. They are also used as a storage location for comments and especially when planning non-cumulative key figures (e.g. price planning).

However, a lot of data garbage accumulates over time - in other words, there are many data records with the key figure value 0. If you want to delete these and you think of the function 'Selective deletion' - this is only possible using characteristics. In our case, however, we want to delete all data records for which the key figure is 0, independent of characteristics.

The solution is quite simple. Since there is only one table for active data in a Direct Update DSO, the data records in it can be deleted with one line of ABAP.

In our example, the aDSO ZDRDFLAG is used as control table for the planning cycle. The key figure ZDRFLAG is used as an indicator of which year should be planned (1 = active, 0 = inactive).

The active table of the ADSO is /BIC/AZDRDFLAG2. You can list all tables of the ADSO by performing a wildcard search for the DSO name in the ABAP Dictionary (transaction SE11): *ZDRDFLAG*.

We can now use this table name in the coding. To do this, we create a program (transaction SE38) with the following line:

DELETE FROM /bic/azdrdflag2 WHERE /bic/zdrflag = 0.

After execution, the record with the flag = 0 is deleted. However, the data record with flag = 1 still exists.

Notice:

Please note that deletion at the database level is irreparable and the data can only be restored by a backup import. Therefore, test your program extensively on the development environment before you use it on the production system.

Planning Tools compared - SAP BW IP vs. BPC vs. SAC

InfoCube-like ADSOs

Unfortunately, with InfoCube-like ADSOs you cannot perform compression with zero elimination, as it was the case with normal InfoCubes. However, you can use the approach described above to delete data records from an InfoCube-like ADSO.

However, please note that an InfoCube-like ADSO uses an input table in addition to the active table. The delta records typical for InfoCubes are written there.

After the requests have been activated, the data records are compressed and moved to the active table. To do this, you have to switch the ADSO to load mode.

You can now use the following coding to remove the zero records from the ADSO:

DELETE FROM /bic/azdrtest62 WHERE amount = 0.

Notice:

Please note that deletion at the database level is irreparable and the data can only be restored by a backup import. Therefore, test your program extensively on the development environment before you use it on the production system.

Zero Values - Our Conclusion 

As you can see, there are always solutions to problems that have not yet been addressed by SAP. However, solutions like the one shown here should be used with great caution, since they operate directly on the database and such changes in the data are irreparable.