Freigeben über

SQL Server Column-Store: Updated SAP BW code

  • Artikel

SQL Server column-store is generally supported by SAP BW since end of October 2012. One of the prerequisites is importing the correction instructions of SAP note 1771177. Last week we published a new version of this note with additional improvements. We recommend to update your SAP BW code by applying the new version of this SAP note using SAP transaction SNOTE.

BW Remodeling

BW Remodeling is now supported. When using the old SAP BW code, you first had to convert the cube from using the column-store to a conventional cube (using b-tree indexes) before running BW Remodeling. This restriction has gone. With the updated SAP BW code of SAP note 1771177 we now fully support BW Remodeling also for the column-store.

Key figures with increased precision

SQL Server 2012 column-store does not support the data type DECIMAL, if the precision is greater than 18 digits. The data type DECIMAL is used in SAP BW for key figures. In SAP BW you cannot create a key figure with a greater precision than 17 digits on your own (using SAP transaction RSD1). Therefore the restriction of the column-store index should not be an issue. However, SAP can deliver such key figures using BW Content. There are a few SAP applications based on SAP BW (for example SAP BPC), which use such key figures with increased precision.

The updated SAP BW code takes care of cubes containing key figures with increased precision. It always creates b-tree indexes on the e-fact table of these cubes, independent from the column-store configuration defined in report MSSCSTORE.

The column-store definition is stored in table MSSSTORAGE in field TEXT. There used to be two valid states: Using a column-store index (for TEXT="NONCLUSTERED") or using b-tree indexes (for TEXT=" "). The updated SAP BW code introduces a third state (TEXT="NOT_SUPPORTED"). It is used for cubes, that were explicitly configured to use the column-store, but are using b-tree indexes instead (due to key figures with increased precision).
clip_image001

When configuring to use the column-store for all cubes while using the old BW code, you can run into the following issue: An e-fact table which high precision key figures, has no b-tree indexes, because you configured to use the column-store. It has no column-store index either, because you cannot create it for this data type.

You can easily repair these e-fact tables after applying the updated BW code by running SAP report MSSCSTORE. Select "Repair indexes of all cubes" in the tab "Set default for all cubes". Choose your current system default (either "Use column-store index" or "Use b-tree indexes"). By choosing your current system default, you won't change the column-store definition of any cube. However, high precision cubes, which are configured to use the column-store, are repaired by creating b-tree indexes. After that, you should activate aggregates of the repaired cubes, if needed.
clip_image002

SAP system upgrade to 7.3

Any SAP system upgrade of BW systems using the column-store works fine. However, when upgrading from 7.0x to 7.3x, the column-store indexes may be dropped during the SAP upgrade. They have to be re-created after the SAP system upgrade using report MSSCSTORE. This issue will be fixed in SAP Software Upgrade Manager (SUM) in the next support package (SL Toolset 1.0 support package 6). We will describe details in SAP note 1796298 within the next weeks.

Comments

  • Anonymous
    August 05, 2013
    The comment has been removed
  • Anonymous
    August 25, 2013
    SQL 2014 is planned to be supported by SAP NetWeaver in mid of 2014.