Last year we already released an SAP note regarding BW cube compression performance, containing a correction instruction. The text of this note has been updated last week.
We released additional, new correction instructions for the BW cube compression in a new SAP note together with other improvements for the SQL Server column-store. The two notes are:
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.
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.
This issue can only occur when running one of the following support packages:
|SAP_BW 7.0||SP29 - SP30|
|SAP_BW 7.0 EHP1||SP12 - SP13|
|SAP_BW 7.0 EHP2||SP12 - SP13|
|SAP_BW 7.1 EHP1||SP10 - SP11|
|SAP_BW 7.3||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.
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:
Use SAP report SAP_RSADMIN_MAINTAIN for setting the RSADMIN parameter. If the RSADMIN parameter does not help, then remove it again.
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.