Published at March 5, 2024by Jörg Brandeis

Unfortunately, in BW 7.50 semantic grouping does not work with HANA execution to ensure that all records with the same partial key end up in the same package. But this is sometimes necessary when calculations are performed in our routines. In BW/4HANA this problem no longer exists, see SAP documentation. In this article I want to present a few solution approaches.

Example of semantic grouping

The goal is to calculate the percentage share of an item within a document. This can only be calculated if all data of a document is in the same package.

Everything in one package ➔ Correct result

Split across two packages ➔ Incorrect result

Solution approaches

There are several ways to replace semantic grouping with HANA execution in BW 7.50. As always, the best approach depends on the specific circumstances.

The solution in SQLScript

This approach solves the problem in the AMDP. For the data in the INTAB, we read all records from the active table of the source DSO according to the semantic grouping. With this we build a new table variable INTAB_NEW that is consistent in itself. If a semantic group is now split across two packages, then all records of the group are processed twice.

However, we assume that the target is a standard DSO. This means that if a record with the same key exists in two or more packages, it will be overwritten with exactly the same values during activation.

Here is the source code with which we build the INTAB_NEW:

METHOD GLOBAL_EXPERT BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
using /BIC/AZBR_E2_S2.

    intab_new = select * 
                  from "/BIC/AZBR_E2_S2"
                 where ( budat, account ) in ( select distinct budat, 
                                                               account
                                                 from :intab ); 

Disadvantage of semantic grouping in SQLScript

With the logic shown, we ensure that all records always stay together. But this also means that data is processed twice. This does not distort the result in terms of content, since the logic always produces the same result in all packages. However, more records are generated than are actually needed. This can slightly affect the runtime.

In my tests, for the example with about 3.1 million records and 4 packages, the overlap was below 5%. Compared to the other options, this is definitely acceptable. However, I don't know whether this value can be generalized. How high the redundancy actually is can be seen during activation from the difference in the number of records between the inbound table and the active table. Presumably a fine granularity of the groups helps to keep the redundancy low.

One large package

If you choose the package size so that two packages are never formed, then you don't have the problem. However, even a HANA database cannot handle an infinite amount of data at once. That's why this is only an option if you can foresee that the volume will remain manageable. I assume that a few million records are acceptable.

And what if the data volume increases unexpectedly? The parameter for the package size must be set so that it is truly never exceeded. Otherwise we get incorrect data. If, on the other hand, there are terminations, then that is annoying, but if necessary you can then get the data into the system with the next solution approach.

Multiple DTPs with disjoint data sets

If you have too much data for one package, especially if you have run into problems with the previous approach, then one option is to partition manually. By this I mean creating several DTPs that split the data set based on the filter criteria. This is not elegant, since it reduces the maintainability of the system. But it is always possible.

Upgrade to BW/4HANA

Just for the sake of completeness. For anyone who uses a lot of semantic grouping, this could be an argument for BW/4HANA.

Switch to ABAP execution

The last option is to switch to ABAP. This is a valid option especially for small data volumes or when execution time is not an issue. Not elegant, but guaranteed to work.

Conclusion

It is a shame that semantic grouping only works properly for HANA execution starting with BW/4HANA. But there are several practical workarounds. In SQLScript it is not very complicated.

I hope the article is useful. Of course, I would be very happy to hear comments and experiences with it.