Columnstore became default in SAP BW

  • Article

Overview

The following features have been optionally available in SAP BW on Microsoft SQL Server for several years:

The impact of these features is described in https://blogs.msdn.microsoft.com/saponsqlserver/2017/05/08/performance-evolution-of-sap-bw-on-sql-server. Customer experience showed, that using these features almost always resulted in BW query performance improvements. Therefore, these features are turned on by default after applying SAP Note 2582158 - Make Columnstore and Flat Cube default.

Columnstore became default only on SQL Server 2016 and newer:

Creating a New Cube

A new cube will be automatically created as a Columnstore Cube, except in the following cases:

  • The new behavior has been explicitly turned off by setting the following RSADMIN parameter

    • MSS_DEFAULT_CS = FALSE

  • APO Cubes
    Cubes of the SAP application Advanced Planning and Optimization (APO) never use the Columnstore

  • Realtime Cubes
    SAP Realtime Cubes only use the Columnstore on the e-fact table, unless you have set the RSADMIN parameter

    • MSS_REALTIME_FFACT_CS = X

    For details, see SAP Note 2371454 – Columnstore and Realtime Cubes

Transporting a Cube

A cube is automatically converted to Columnstore (on the target system), if it is empty (on the target system).

Converting all Cubes to Columnstore

There are 3 different ways for converting all cubes to Columnstore. In the second tab of report MSSCSTORE, you can define a global setting for all SAP BW cubes. We aware, that this is a global setting for all (existing and future) cubes, not just a default for new cubes.
When choosing "Always Column Store (CS)" and pressing F8, all cubes are defined in SAP BW as Columnstore Cubes. However, the Columnstore Indexes are not created on the database yet. Report MSSCSTORE should finish within a few minutes, since only the cube definition is changed.

  • Converting via process chain
    All indexes of a BW cube are created when running the BW process chain Type "Create Index" or executing "Repair DB indexes" in SAP transaction RSA1. This allows a gradual conversion of the cube indexes, once you have changed the Columnstore definition in report MSSCSTORE as described above.
  • Immediate conversion
    You might additionally select "Repair indexes of all cubes" in report MSSCSTORE. Hereby, the Columnstore Indexes are created on the database immediately. In this case, the runtime of report MSSCSTORE can easily take a few hours. Therefore, you should run report MSSCSTORE as a batch job (by pressing F9). Make sure that the SQL Server transaction log is large enough and that log backups are performed regularly.
  • Conversion during R3load System Copy
    You can automatically convert all cubes to Columnstore during an R3load-based system copy. Therefore, you have to choose "SQL Server 2016 (all column-store)" as the database version in report SMIGR_CREATE_DDL.

Conclusion

For SAP BW releases below 7.40 SP8 you should convert all BW cubes to Columnstore. For newer SAP BW releases you should consider applying the Flat Cube. A Flat Cube always uses the Columnstore.