The root cause of the performance issues with BW cube compression are described in detail in the first SAP note. This BLOG is intended as a step-by-step guide for increasing BW cube compression.
Step 0: Upgrade SQL Server
The BW cube compression uses the SQL Server MERGE command in SQL Server 2008 and all newer releases. SQL Server 2005 and older releases do not support the MERGE command, resulting in increased transaction log usage and slower runtime of the BW cube compression.
Step 1: Solve issue with HASH JOIN HINT
This issue can only occur when running one of the following support packages:
SP29 - SP30
SAP_BW 7.0 EHP1
SP12 - SP13
SAP_BW 7.0 EHP2
SP12 - SP13
SAP_BW 7.1 EHP1
SP10 - SP11
SP8 - SP9
SAP_BW 7.3 EHP1
SP5 - SP8
Applying the correction instructions of SAP note 1860832 will solve this issue. If you are still not satisfied with the BW cube compression performance, then continue with step 2.
Step 2: Solve issue with execution plan
The optimal SQL Server execution plan of the MERGE command uses the P-index of the e-fact table. In almost all cases an optimal execution plan is used. However, you can force the usage of the P-index by activating on optimizer hint for the MERGE command. Therefore you have to apply the correction instructions of SAP note 1951490 and set the following RSADMIN parameter:
MSS_COND_HINT = X
Use SAP report SAP_RSADMIN_MAINTAIN for setting the RSADMIN parameter. If the RSADMIN parameter does not help, then remove it again.
Step 3: Optimize fields of P-index
The P-index of the e-fact table is not needed for SAP BW queries in most cases. Then main intention of this index is to speed-up the BW cube compression. In the past, this index was not optimal in all cases. After applying SAP note 1951490 the P-index of new cubes will be optimized for BW cube compression, in particular when using BW partitioning. Since the P-index is not needed for BW queries, the index check in SAP transactions RSA1, RSRV and report MSSCSTORE does not report a red flag for a suboptimal P-index. As a result, the suboptimal P-index will not be automatically converted during normal SAP BW process chains. It is implemented this way to prevent an unexpected, one-time increase of the process chain runtime after installing the next SAP BW support package. However, you can configure the automatic, one-time conversion of the P-index by setting the following RSADMIN parameter:
MSS_P_INDEX_REPAIR = X
The P-index will then be repaired the next time a “Create Index” process is running in a BW process chain. Alternatively, you can convert the P-index manually using SAP transactions RSA1 or RSRV. You can also display the status of the P-index in report MSSCSTORE. An index with suboptimal P-index looks like this:
In transaction RSA1 or RSRV you can also see the yellow status of the cube, but not the explanation in DB-Status.