This blog post is related to SAP BW4HANA or BWonHANA or SAP S/4 HANA Embeded Analytics ADSO Active table partitioning.
Now please understand that the active table of the ADSO holds data after activation and it is the area where all data resides.
Sometimes we may come up with a situation where the ADSO reached the maximum limit of HANA DB Table which can only store 2 billion records as of now and if the production data is reaching the maximum threshold limit then the production ADSO data refresh will fail.
The below method is less time consuming and quickly can be done in max 6 hours and system can be handed over to BAU as usual.
in SAP HANA table we can do partitions at 1st level, 2nd level & also heterogeneous and not heterogeneous Partition (can be called as Merge Hetero partitions).
I do not want to elaborate again what is heterogeneous and not heterogeneous partitions in SAP HANA. You can read this from SAP’s official blog as in below Blogs.:-
The below blog also give good idea on
HANA Partitioning – 2 billion rows limitation – Part I: BWoH / BW/4HANA
https://blogs.sap.com/2017/12/30/hana-partitioning-2-billion-rows-limitation-part-i-bwoh-bw4hana/
Example NON Heterogeneous Partitions in HANA
ALTER TABLE “Schema.”Table
PARTITION BY
RANGE (“Key”)
(PARTITION ‘1’ <= values < ‘99999999’ , …..
PARTITION OTHERS),
RANGE(“SystemID”)
(partition value = ‘XX’,
partition OTHERS);
Example of Heterogeneous Partitions in HANA
ALTER TABLE “schema”.”table”
PARTITION BY
RANGE (“ID”)
( (PARTITION VALUE = ‘XX’ ) SUBPARTITION BY RANGE (YEAR(“YearColumn”))
(PARTITION ‘2009’ <= values < ‘2010’ , PARTITION ‘2010’ <= values < ‘2011’ , PARTITION OTHERS ),
……
(PARTITION OTHERS)
);
Also based on your HANA DB release we should be able to do dynamic partitioning on date.
Also we can consider looking into the SAP note : 2019973 – Handling Very Large Data Volumes in SAP BW on SAP HANA
Well above are theories, references to check for partitions and how to handle large volume of data.
Now we will go through an actual partitioning we can perform in production BW4HANA system where you have huge amount of data and less downtime.
Below steps mentioned can help you to perform for ADSO the active table partitioning.
- Find out the ADSO which you want to do the partitioning.
when you are in SAP S/4 HANA Embeded analytics system or in BW4HANA system we have 2 layers.
- Application Layer which is nothing but SAP GUI
- DB layer to which you can connect from SAP HANA Studio or eclipse GUI.
in the DB layer we will find the SAPABAP schema if you are in S/4 HANA Embeded analytics or BW4SCHEMA if you are in BW4HANA/BWonHANA system.
you can do the partitioning in 2 ways
- either from SAP HANA Studio or Eclpise or from HANA WEB IDE
- or from SAP GUI screen with DB02 T code. The query execution time of SQL console of your DB02 must be increased to carry out this task. Take help from your Basis person in this case.
in both the case we need an DB user and application use with required privileges to CREATE, ALTER and INSERT operations.
As we are going to modify a table which is created by the application (SAP S/4 Layer) it is an ideal practice to create an Fire fighter ID and provide necessary privileges ( probably ADMIN role) to perform the task.
- Create a back up table which is same as our ADSO Active table.
create TABLE “XYZSchema”.”/BIC/AADSONAME_COPY” LIKE “XYZSchema”.”/BIC/AADSONAME”;
This will ensure that a copy table is created with same DDL structure as the main active ADSO table.
- Once the back up table is created you can check the index of both the tables.
You can find the index of a table in 2 ways
Option 1 : Indexes View in the HANA DB
SELECT * FROM
“SYS”.”INDEXES” WHERE TABLE_NAME = ‘/BZI/AADSONAME”
Option 2 :
Go to SE 11 and you can always see the index there.
the index of both the table cannot be the same. Hence rename the index name of the new copy table created to a new index name.
- RENAME INDEX idx TO new_idx; (Schema name is not needed when you are executing in DB02)
For More Information Please see SAP document here
Once the Index rename is done then we are almost done.
- we can delete the data from the main table and inform business users about system down time etc. This should ideally be done over a weekend where the system users are less and no active reporting is happening.
Once table is clean we can perform the partitioning. We can also do a table partition with data in it but the partition may take ages, sometimes it fails. So better to delete the data and perform the partition.
- Partition can be done based on the data pattern. This should be discussed with your BW architect to perform 1 level or 2 level (Hetero or Non heterogeneous partition).
Once partition is done the maximum steps are done.
- we can always insert the data from the back up table to the main table.
- While doing the insert always do INSERT INTO Select from way with all the columns from back up table and all the columns of main table.
- This is ensure the ordering is correct and no issues on table structure switch.
- The partition can be done in chunks such as Legal entity or fiscal period wise etc.
- Always perform Delta merge operation in between so that data from the delta memory can be pushed to main memory for maximum read/write operations and delta memory will be free always.
- MERGE DELTA OF “SchemaName”.”/BIZ/AADSONAME”
Once all insert are over compare the data between your copy table and main table. They must match.
Finally you can apply force compression of the Active table of the ADSO to free some space the table may occupy. This is to ensure table is compressed and memory is best utilized.
UPDATE “SchemaName”.”/BIZ/AADSOName” WITH PARAMETERS (‘OPTIMIZE_COMPRESSION’ = ‘FORCE’)
- Once everything is verified you can delete the copy table.
You can perform the data load to the ADSO now and you will see improvement in read write and merge operations and we will also over come the limitation of 2 billion records.
Now each partition will have the capacity to store 2 billion records. Similarly we can also do sub partition and 2nd level partition by studying the data in the system.
This is the best and optimum way of doing partition on SAP BW4HANA Active table ADSOs.
