I’d like to once again delve into the issue of memory usage. Other blogs like “SAP HANA – no more memory? Implement early unload!” have shown the importance of properly configuring SAP BW objects for the intended usage. Briefly: anything not queried regularly and immediately should be unloaded early to storage. For this you set the “early unload priority” attribute in the HANA database table. Since this setting is not a part of the ABAP transport system (CTS), the developer or operation must always ensure that the configuration is correct. Otherwise, only data needed for staging from the data acquisition layer perform well in RAM, which burdens the system and is unnecessarily expensive.
A HANA database moves tables, based on their priority, from the main memory to physical storage (called UNLOAD). The data are thus not available for direct querying. But you can ignore this for staging, since the data are often needed only overnight when running a process chain. The sequence of moves is defined by the object priorities in the table, which you can set manually in the RSHDBMON transaction. Some SAP BW objects like PSA tables, change logs from normal ADSOs, or write-optimized ADSO objects, have a full or partial early upload flag set in the standard version too, depending on SAP’s intended use.
For SAP BW objects, you can set only priority 5 (active data for reporting) and priority 7 (early unload, inactive data like change logs …).
SAP also states that manually set RSHDBMON attributes remain when you change the ADSOs. However, this is not quite correct. If a transport creates a table via DROP & CREATE, the current support packages for SAP BW 7.5 include the standard setting for early unload, and thus no longer reflect the necessary settings.
How to solve this problem: since continuing integration gives us full access to the admin function of the database within the SAP HANA environment, we can use a native SQL command, analogous to the RSHDBMON transaction, to correctly set the early unload priority. This works best if we follow a clear convention to name LSA++ layers (e.g., P* for the propagation layer, D* for the data mart layer, etc.) Through these prefixes, a program can find the ADSOs in the P* layer and accordingly correct the early unload settings. The convention is completed with exception tables: for instance, if reporting is set on an object in the propagation layer. The LSA++ permits this explicitly.
The program logic sequence is as follows:
- Determine all ADSOs to be checked via name prefixes
- Determine the tables used via an API (ABAP OO class)
- Check the early unload priority in each table
- Update the early unload priority via a native SQL command
By executing this after a release, you ensure that all objects have the correct setting.
For implementation, an extra function encapsulating a change to the early unload priority is built into the ADMP class used here to clear the main memory. Unfortunately, SAP has not implemented an API for that, and each is programmed separately. Moreover, in addition to the class, an ABAP report was implemented to select the class and provide the developer or operation with a mask that enables the change to be simulated.
The class determines the tables belonging to the transferred ADSO and sets these up correctly. At the beginning, the program defines the necessary data types, checks the transfers, and determines the database information of the SAP BW schema on the HANA DB.
The actual program logic is then executed. This determines the affected ADSOs in the SAP BW table “rsoadso” that contains all ADSOs with a static filter on the layer. This is followed by determining the tables belonging to the ADSOs via an API. Finally, the private set_unload_priority method is called to set the tables accordingly.
The private method encapsulates SQL access for setting the early unload priority. This makes sure that you do not coincidentally set other tables and reuse the function for other class extensions. (Please be careful here, it’s not easy to reverse changes!)
Source code for preparing reports
The report has a very simple dialog to control the class and simulation. You can verify any changes to objects in advance. Furthermore, you can identify incorrectly named or recognized objects. There is no table of exceptions here, which means the functionality is still multifaceted and expandable as needed.
In looking up reports, you can use SQL wildcards to filter the tables. Otherwise, at least the static filter accesses the table prefix in line with the naming convention, which avoids errors.
This simple functionality assures no unnecessary loading of memory on the HANA DB. Moreover, it minimizes the effort for a release and simplifies a check of whether all settings meet your requirements. Manual adjustments are thus avoided, and usage of the HANA DB memory is optimized.