Partition of InfoCubes is done to divide the huge data in the fact table into several smaller, independent units. This improves the query performance.
InfoCube can only be partitioned using one of the two below criteria-
- Based on month (0CALMONTH info object must be present in the cube)
- Based on Fiscal period (0FISCPER info object must be present in the cube)
Partitioning and re-partitioning can be done even though the cube contains data. This concept was first introduced in SAP Netweaver BW 7.0 as a replacement of partitioning in 3.x
Please note that you do not partition the cube when you create it. Usually it is done at a later point in time when the cube has more than a million records.
Also, in a partitioned cube, it may happen that some of the partitions have little or no data due to cube archiving and data deletion over a period of time.
Features of the Partitioning:
- Complete partitioning or Repartitioning
- Merging partitions
- Adding partitions
Note: When you merge or add partitions, InfoCube partitions are either merged at the bottom end of the partitioning schema or added at the top.
During merging of partitions, the lower limit is pushed to the given range and during adding partitions, the last partition is extended to the given range.
You can merge and add partitions for aggregates as well as for InfoCube.
If an InfoCube or aggregate is partitioned, you need to reactivate them.
Complete partitioning fully converts the fact tables of an InfoCube. The system creates shadow tables with the new partitioning schema and copies all of the data from the original tables to the shadow tables.
As soon as the data is copied, the system creates indexes and the original table is replaced with the shadow tables.
After the successful completion of partitioning, both the fact tables – the F table and the shadow table are available in the original state.
You can manually delete the shadow tables after repartitioning has successfully completed to free the memory space.
If the fact table name is /BIC/F<cube name>, then the shadow table name will be /BIC/4<cube name>, where prefix 4 denotes a shadow table.
Example: Steps to perform complete partitioning
Step 1: Make sure that the InfoCube exists with a minimum of 1 million records.
Precaution: Backup the data of the InfoCube
Note: By default, the F fact table of InfoCube, change log table of DSO and PSA tables are partitioned by the system during data loading itself. (Automatic Partitioning)
In these cases, the partition criteria is ‘0REQUID’ (Request ID)
Generally, the large InfoCubes should be compressed first. After the compression, the F table data is moved to E table and the partitions by 0REQUID are gone.
The query now accesses the E fact table. To improve the query performance, you need to partition the E fact table using 0CALMONTH or 0FISCPER.
Step 2: Check the data in the cube, make sure that the InfoCube contains 0FISCPER or 0CALMONTH as time characteristic.
Double click on the cube, go to the Extras menu, and select ‘DB Performance’ -> ‘DB Partitioning’. Observe the time characteristic. Press F3.
Step 3: In the context menu of the InfoCube, select Additional Functions – ‘Repartitioning’
Select ‘Complete Partitioning’ and then select ‘Initialize’
Message pop up appears: ‘Did you back up the data before Repartitioning?’
Select ‘0CALMONTH’ and continue.
Give the start and end calendar months for partitioning e.g. 01.2015 to 12.2017
Maximum number of partitions will be N+2 where:
N is the actual number of partitions
and 2 are the additional partitions maintained by the BW system. These are-
- Partition before ‘From Date’
- Partition after ‘To date’
Message pop up appears: Select Yes
Select ‘Immediate’ and Click ‘Save’.
Continue with the message.
Go to SM37 and monitor your job-
Observe the job name: ‘RSDU_IC_COMP_REPART/<cube name>‘
Step 4: Check the InfoCube partitions.
Go to RSA11, double click on your cube. In the extras menu, select, ‘DB Performance’->’DB Partitioning’ and press Enter.
Observe the maximum number of partitions.
Similarly, you can add or merge partitions by giving a new month in the To date field after selecting appropriate option in Repartitioning.