SAP HANA Memory Utilization-Corporate Memory
1 Corporate Memory
While working with big clients having businesses throughout the globe, it becomes really important to manage the data and wherever possible, reduce the data base size. When dealing with millions of records, we often come up with a situation where the data base size reaches almost the maximum capacity and data loading is no longer possible. We always cannot ask client increase the DB size as lot of money is involved. Especially if you are using HANA data base. Several Archiving techniques have been introduced in HANA like NLS and Dynamic Tiering for Using Extended Tables, but these are not only expensive but have some restriction. For example if a document is already achieved using NLS, then if you want to load delta of that record into DSO, you will come up DSO activation error.
We have a simple technique that can be used easily to reduce main memory size without incurring too much of implement cost. Here we are storing the not so important data in separate memory called Corporate Memory.
1.1 Data in Corporate memory
First we need to analyze which are the large tables in DBACOCKPIT. Then we need to take decision based on business requirement which data is not so important. Data are classified into 3 types: Hot, Warm and Cold
Hot Data: Data which is regularly used and often changed e.g. Last 3 years of data
Warm Data: Data which is rarely used and may need once or twice in a quarter e.g. Data between year 2012 and 2006.
Cold Data: Data which is most likely never going to be used but still business wants to keep e.g. data beyond 2006.
The production data can be classified into these groups based on business requirement. The selection filed can be time or company code or anything. It should be purely a business decision.
With corporate memory we intend to keep both Warm data and Cold data out of main memory and thus reduce drastically the size of main memory
Create Inflow DSO which will be copy of the large BW tables.
1.2 Hana Data Base
To understand the advantages of corporate memory, we first need to understand how the data is stored in the HANA data base.
When a data is loaded into an Info Provider in HANA, it actually gets loaded into two storage area: Memory and Disk. Thus a data will be accessed very quickly since it resides in memory like RAM.
The screen shot suggests that it has got two Memories:
Current total Memory and Size on disk
With Corporate memory concept, we can offload some of the data from Main memory and keep it only in disk, so main memory size gets reduced
2.1 Functional Details
Let’s take an example of FI DSO. We assume that it has data from year 2000 to 2015.
Business approved that Data form 2012 onwards will only be useful for reporting and rest are historical data and may not require often. Hence Data from year 2000 to 2011 can be considered as Cold and Warm data. Hence these may not be required to be kept in Maun memory. These data we will be stored in Inflow DSO and then will be removed from the Main DSO.
2.2 Technical Details
- Create an Inflow DSO (Corporate DSO) which will be a copy of the Main DSO.
- Create transformation and DTP To and Fro from between the Corporate DSO and Mai DSO
- Load period wise Data to the Corporate DSO.(Year 2000 to 2011)
- Once Data is loaded in the Corporate DSO, do selective deletion of year 2000 to 2011 from the Main DSO.
- In DBACOCKPIT run SQL statement:
Unload “SAPMIW”.”/BIC/AFIXXXXX” (Here /BIC/AFIXXXXX is the active table of Corporate DSO)
- The above statement will offload whole data of the main memory of corporate DSO
3 DBACOCKPIT Test
3.1 Before Corporate memory Load
- Run t-code DBACOCKPIT
- Expand Current Status
- Double click on Overview
- We will get the overall data base size
3.2 Corporate memory Load
After taking the statistics from the cockpit, we need to do these below simple steps
- Load Period wise data to Corporate memory DSO from the Main DSO
- Do selective deletion of Data from the Main DSO of the periods that are loaded.
- Check the size of the Main memory table in DBA COCKPIT
- In DBA Cockpit, Expand Diagnostics, double click on table View
- Enter the Active Data table name in Table/View Section and Display find
- The next screen will give you the current size of the Corporate table
- Here we can see the Main Memory is having 1.12GB of data allocated for this DSO
3.3 Offload of Main Memory
- Run SQL command in DBACOCKPIT SQL Editor:
- Expand Diagnostics
- Double click on SQL editor and enter the command
- Click on execute
- All the data from the main memory of this Corporate DSO will be deleted
- Check again the size of this table in DBACOCKPIT- Tables View
- Here we can see the Size of Memory is Zero
- The size on Disk Remains same as we have only deleted data from the main memory and not from the disks
4 Important Notes and Conclusion
- We can repeat this activity for as many no of DSOs or cube that we want.
- Once we finish this activity, sometimes we can see the data gain comes back to main memory automatically. This is being done by the system if someone even tries to check the data of corporate memory in SE11 or LISTCUBE. This happens because of HANA functionality called DELAT MERGE.
- DELAT Merge is a HANA standard functionality where delta data is initially stored in Delta memory. Latter it is moved to Main Memory.
- To avoid this we can run a job through Program that will perform the offload of Main Memory using the above SQL command on a periodic basis so that even if someone does open Corporate memory DSOs, and data again loaded in the Main memory, these will periodically delete Main Memory and keeps check the size
- After the Corporate Memory activity is performed we can again check the Overall DBACOCKPIT size, you will see that there is a massive reduction
- This will help to really control the Main Memory Size in the data base and can save good amount of money for the customers buy not asking the increase the size.
- This is a coast affective solution and also have less maintenance cost
- Selective deletion from the Original DSO helps reduce the size.