SAP BW improvements in new Support Package

  • Article

The latest SAP support packages contain a bunch of improvements for SAP BW running on Microsoft SQL Server. The improvements can be assigned to two areas:

The necessary Support Packages for NetWeaver 7.0 and 7.0 EHP 1 have already been released. The Support Packages for the other SAP BW releases will follow soon.

Release New SP Stack Release to Customer (RTC)
7.00 SPS 27 July 27, 2012 (available)
7.01 SPS 12 August 13, 2012 (available)
7.02 SPS 12 Calendar Week 38, 2012 (planned)
7.11 SPS 10 Calendar Week 35, 2012 (planned)
7.30 SPS 8 Calendar Week 40, 2012 (planned)
7.31 SPS 5 Calendar Week 43, 2012 (planned)

 

 

Index improvements

The index layout for SAP BW cubes has slightly changed over the last 7 years. As a result, it was different on SAP BW 7.0, 7.1 and 7.3. The different index layout was not taken into account during a SAP BW version upgrade. As a result, the indexes looked different for cubes newly created on SAP BW 7.3 compared with cubes already created on SAP BW 7.0 (before upgrading the system to 7.3).

With the newest SAP BW SPs, the index layout has been optimized and standardized for all SAP BW releases as of SAP BW 7.00. The indexes of existing SAP BW cubes are automatically adjusted to the new layout when running BW process chains for data load (process type "Generate Index").

clip_image001

Reduced disk space usage

The P-index on the f-fact tables has been removed without replacement. This index was a relic from long ago when database query optimizers could not use more than one index for a single query. We have not seen any BW query for years which could benefit from the P-index. The index was just a waste of space. However, there is one exception: For SAP APO we need a unique P-index on the f-fact table. As a matter of course, this index still exists for APO cubes.

The packet dimension on the e-fact table for cumulative cubes always contains the same value (typically 0). There used to be an index on the packet dimension: the 010-index. This index has been removed, too.

Finally we also removed the index on the unit dimension, for e-fact and f-fact tables: the 030-index. The unit dimension is not supposed to be a selective field in SAP BW queries.

Improved BW query performance

The field order of the P-index was historically suboptimal. The first field used to be the packet dimension, which is not selective for e-fact tables. F-fact tables have an additional clustered index on the packet dimension anyway. Therefore we changed the field order of the P-index. It now starts with the time dimension.

For Microsoft SQL Server, the dimension tables used to have only the primary key and a compound 010-index. There were additional single-column indexes on other database platforms (for example on ORACLE). We now also have these additional single-column indexes on the dimension tables in order to improve certain BW queries.

Cube load improvements

DIMID (Dimension ID) generation for dimension tables

Having single-column indexes on the dimension tables can improve BW query performance. On the other hand, this may result in an increased DIMID generation time during data transfer processes into cubes. We simply have the additional overhead to maintain the indexes for each row which is inserted into a dimension table. After applying SAP note 1808872 "single-column indexes of dimension tables", you can configure the single-column indexes using an RSADMIN parameter.

When loading a huge amount of data into a cube, you typically use the process types "Delete Index" and "Generate Index" within a BW process chain. These processes drop and re-create the indexes on the f-fact table in order to speed data load. For Microsoft SQL Server they also drop and re-create the single-column indexes of the dimension tables.

The DIMID generation for the dimension tables works like this: For each row to be loaded into the cube we first select the DIMID from the dimension table by specifying all characteristics. When the select returns a row, then there is nothing to do. If not, then we have to generate a new DIMID. Selecting the DIMID is always fast, because we only have one fitting index (the compound index 010 on all characteristics of the dimension). However, when not using a process chain, there are additional single-column indexes. They may result in a suboptimal execution plan on SQL Server. In order to avoid this, we have added an optimizer hint (index 010) for the select on the dimension tables when specifying the DIMID. This was already fixed in SAP note 1689011 "Slow Selects on Dimension Tables".

Dimensions with more than 16 characteristics

There is another potential performance issue regarding this select on the dimension tables. This issue is database independent and caused by the fact, that the SAP Data Dictionary only supports 16 columns per index. Although there is no (and cannot be made any) improvement for this in the SQL Server specific code of SAP BW, I want to describe the general solution for such an issue here.

When having more than 16 characteristics per dimension, you should add the most selective characteristics first in RSA1 when defining a cube dimension. The 010-index of a cube only contains the first 16 characteristics as they show up in RSA1. However, you can change the order of the characteristics (and therefore the index fields of the 010-index) in RSA1 without changing the definition of the fact- or dimension-tables:

  1. Edit the InfoCube
  2. For each (of the 16) characteristics, you want to be included in the 010-index:
    1. Right-click on the characteristics (e.g. Plant), select "Cut(to Move Only)"
      clip_image002
    2. Right-click on the characteristics, which is currently on the top of the list (e.g. Company Code), select "Insert"
      clip_image003
  3. Perform one additional (dummy) change, e.g. change the description of the cube
    clip_image004
  4. Activate the cube
    As a result, only the 010-index of the modified dimension is re-created using the new field order. No other fact- or dimension-table is touched.
  5. Optional: Undo your (dummy) change and activate the cube again

Cube compression improvements

One of the major changes in the BW cube compression was the usage of the SQL Server MERGE command (for cumulative cubes) as of BW 7.11. This resulted in a faster cube compression while reducing the needed transaction log space at the same time. This code was down-ported to SAP BW 7.0x and delivered as a correction instruction in SAP note 1635177 "BW Compression with MERGE for 7.x".

In the newest BW support packages we added further improvements to the BW cube compression. Dependent on the size of the request to be compressed and the index statistics, the MERGE command could be executed with a suboptimal execution plan. We tried to fix this by adding an optimizer hint to the MERGE statement. Unfortunately, this was counterproductive in many cases, dependent on the cube definition, database statistics and fill rate of the SQL Server buffer cache. Therefore we removed the hint again in one of the next support packages. You can remove the hint manually by applying SAP note 1860832 "Performance improvement for BW cube compression".

When compressing a cube which leverages the column-store, we have to drop and re-create the CS-index on the e-fact table. The index creation is using 8 CPU threads in parallel per default. Details about parallelism and how to change the default values is described in SAP note 1654613
"SQL Server Parallelism for SAP BW".

At the end of the cube compression process we run an update statistics. The runtime of the update statistics is relatively low compared to the MERGE runtime. However, you may not want to have this additional step. In this case you can turn off the update statistics during cube compression by setting the following RSADMIN parameter:
MSS_COND_UPDSTATS = FALSE

Repartitioning improvements

Although the repartitioning is a relatively seldom activity in SAP BW, we implemented some performance improvements here. In the old implementation indexes were un-necessarily recreated. This has been fixed in the newest support packages. As a result, the runtime and the transaction log space requirements decreased considerably.

For the indexes, which have to be created, we now use 8 CPU threads in parallel per default. This can be configured the same way as described above for the cube compression.

Important improvements in older SPs

We already delivered several improvements in the past. As a matter of course, they are all included in the newest BW SPs, too. However, many of them can be implemented as a correction instruction and do not require the newest SPs. The most important are the following:

Query and index creation parallelism

Correction instruction in SAP note 1654613 "SQL Server Parallelism for SAP BW": Increases BW query performance and speeds up index creation. Details are described at https://blogs.msdn.com/b/saponsqlserver/archive/2011/12/12/performance-of-index-creation.aspx

PAGE compression as default

Correction instruction in SAP note 1581700 "PAGE compression support for DDIC & Hom./Het. System": Dramatically reduces the space usage for SAP NetWeaver systems running on Microsoft SQL Server. It does not only apply to SAP BW systems. Details are described at https://blogs.msdn.com/b/saponsqlserver/archive/2011/08/22/sql-server-page-compression-became-default-for-sap.aspx

1000+ Partition Support

Correction instruction in SAP note 1494789 "Enabling 1000+ partitions support on SQL Server": Increases speed for cube compression and request deletion. Details are described at https://blogs.msdn.com/b/saponsqlserver/archive/2011/03/18/increased-partition-support-of-sql-server-in-sap-bw.aspx