What should be considered before re-partitioning /BIC/A* BW active table

[rajarajeswari_kaliyaperum]

BW Active table Vs BW New table in scale out ; How to avoid inconsistent DSO in BW

 

Though re-partitioning BW table is like normal table re-partition in HANA, but there are few special consideration that have to be taken care during planning of BW table re-partition.

In general, when ever BW creates a DSO ,an active table and a corresponding new table gets created and ABAP ensures that both these tables are placed in the same node .This will facilitate a faster DSO activation along with maintaining the consistency of DSO layout between active and new table.

Naming convention of active and new table in BW environment!

Active table format: /BIC/AZXXXXX0000

New table format:/BIC/AZXXXXX0040

In almost all the DSOs , new table will have relatively lesser number of records. ie, If an active table holds around 6 billion records, its corresponding new table might even have less than 200 million records or even 0 records which is normal. However, when ever we are going to re-partition an active table, we must also re-partition the corresponding new table and must distribute it the same way like new table to ensure consistency irrespective of its record count.

Description:

Active table format: /BIC/AZXXXXX0000

New table format:/BIC/AZXXXXX0040

In BW on HANA system, a default partition will normally be present because of TABLE_PLACEMENT sql that we would have executed post installation .

We have observed that , despite the table placement that takes control for setting up partition for BW tables, it ends up creating a HASH on all primary key but with just 1 number of partitions  . In this case, even though it is partitioned, all the records tends to sit in single partition and hence there raises a need for re-partitioning to increase the number of partition, if it grows beyond 1billion records .

In below screen ,we can see an Active table and its corresponding new table. By default , ABAP has placed it in same host on purpose . It is very important for performance of DSO activation that this setting of similar distribution of partitions that belong to active and new table is retained at any case .

Active Table:

Its corresponding NEW Table:

 

If this active table grows beyond 1 billion records, we will have to increase the number of partitions present in this table from 1 to n. The same activity has to be performed in the corresponding new table as well despite the number of records could be as low as 0 .The entries gets written to new table during DSO activation and might get cleaned up post activation.

ALTER TABLE SAPSSS."/BIC/AZXXXXX0000" PARTITION BY HASH(BPARTNER,CDM_CSEG,CDM_MDCR) PARTITIONS 7;

ALTER TABLE SAPSSS."/BIC/AZXXXXX0040" PARTITION BY HASH(BPARTNER,CDM_CSEG,CDM_MDCR) PARTITIONS 7;

ie,

If I increase this table re-partition to 7 for active table and if i am going to place these 7 partitions on 7 different host, the same pattern should be followed for new table as well.

partition 1 of both new and active table must sit in same worker node

partition 2 of both new and active table must sit in same worker node

partition 3 of both new and active table must sit in same worker node

partition 4 of both new and active table must sit in same worker node

partition 5 of both new and active table must sit in same worker node

partition 6 of both new and active table must sit in same worker node

partition 7 of both new and active table must sit in same worker node

 

If we fail to maintain this type of distribution , it will not only cause slowness with DSO activation, it will also be populated in inconsistent DSO list and hence require manual adjustment. This can be checked by SQL from OSS note 1969700:

SQL:HANA_BW_InconsistentDSOTables_Fallback

======================

- Check for related DSO tables (new data, active data) with inconsistent configuration

[SOURCE]

- SAP Note 1969700

[DETAILS AND RESTRICTIONS]

- Tables are reported if partitioning layout differs between DSO new data and DSO active data table
- Tables (and partitions) are reported if partitioning layout is identical, but tables (and partitions)
reside on different hosts
- Fallback: No check of DSO change log tables included to avoid necessity to access SAP tables (RSDODSO, RSTSODS)
- Output is a rough guidance, but may not be 100 % correct in all cases, consider also using RSDU_TABLE_CONSISTENCY

[VALID FOR]

- Revisions: all
- Statistics server type: all

========================

If our table gets populated in inconsistent DSO, then we have to manually allign the layout for the affected active and new table using alter move command as below.

ALTER TABLE "SAPSSS"."/BIC/Axxxxx000" MOVE PARTITION 1 TO 'servername1:3nn03' PHYSICAL;

ALTER TABLE "SAPSSS"."/BIC/Axxxxx040" MOVE PARTITION 1 TO 'servername1:3nn03' PHYSICAL;

ALTER TABLE "SAPSSS"."/BIC/Axxxxx000" MOVE PARTITION 2 TO 'servername2:3nn03' PHYSICAL;

ALTER TABLE "SAPSSS"."/BIC/Axxxxx040" MOVE PARTITION 2 TO 'servername2:3nn03' PHYSICAL;

ALTER TABLE "SAPSSS"."/BIC/Axxxxx000" MOVE PARTITION 3 TO 'servername3:3nn03' PHYSICAL;

ALTER TABLE "SAPSSS"."/BIC/Axxxxx040" MOVE PARTITION 3 TO 'servername3:3nn03' PHYSICAL;

ALTER TABLE "SAPSSS"."/BIC/Axxxxx000" MOVE PARTITION 4 TO 'servername4:3nn03' PHYSICAL;

ALTER TABLE "SAPSSS"."/BIC/Axxxxx040" MOVE PARTITION 4 TO 'servername4:3nn03' PHYSICAL;

.

.

Do the same for all the remaining partitions .

Thanks for reading!

Like and Follow for more such posts by clicking here ! rajarajeswari_kaliyaperumal

Leave a comment and suggestion if any!