- First published on MSDN on May 23, 2012 by Martin Merdes.
- This blog post might be outdated by now. The latest documentation regarding SQL Server Columnstore on SAP is available in https://www.sap.com/documents/2019/04/023e5928-487d-0010-87a3-c30de2ffd8ff.html and in SAP Note 2116639
- The performance impact of SQL Server Columnstore is described in https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Performance-evolution-of-SAP-...
SQL Server 2012 introduced an additional storage format for data. Besides the normal row-oriented store, there is a column-oriented store as well (like in SAP HANA). In order to make it as transparent as possible, the new column store got exposed to the Database Administrators and Applications as a new index type: non-clustered column-store index.
A column-oriented store is optimized for aggregating mass data. A row-oriented store can be used optimal when accessing just a single row. In SQL Server 2012, the column-oriented store is build side by side with the row-oriented store. SQL Server Query Optimizer can decide at compilation time of a query which store to use best.
SQL Server 2012 Column-store support has been integrated into SAP BW for the releases 7.00, 7.01, 7.02, 7.11, 7.30 and newer. It will be available with the next SAP BW support packages, which are scheduled by SAP for August/September 2012. See details below.
The general support of SQL Server 2012 for SAP products based on SAP NetWeaver (7.0x and 7.3x) is planned for Q3/2012. See SAP note 1651862 for details.
Benefits of the column-store for SAP BW
A column-store index has many benefits: The administration of SAP BW is much easier, since you do not need to maintain any BW aggregates any more. The disk space usage is much smaller when using column-store indexes and the BW query performance is much better.
Query Performance
We have seen significantly increased BW query performance when using a column-store index in our test lab. There was a wide spread of performance increase, dependent on the data and type of query. Some queries were up to 50 times faster, while others did hardly benefit from the column-store index. The majority of the BW queries we tested ran about 3 to 5 times faster. However, we expect even better results with real customer data. The data in our test cubes was often artificially created and did not have any relation between the different characteristics, which prevented optimal compression and performance.
Space Savings
The fact tables on SAP BW cubes have one database index per dimension. All these indexes are dropped, once we move the data into the column-oriented store by creating a column-store index. Dependent on the number of dimensions you save a lot of disk space when using a column-store index. We have seen space savings of 50% and more, even when comparing with fully PAGE compressed fact tables.
No need for aggregates
When defining a column-store index in SAP BW for a cube, all aggregates are deactivated. The definition of the aggregates still exists in SAP BW. However, the database tables for the aggregates are dropped. This results in further space savings and less administration efforts when using a column-store index
Usage of column-store indexes for SAP BW
The column-oriented store in SQL Server 2012 is read-only. Therefore you cannot modify the data of a table as long as a column-store index exists for this table. Before loading data into the table, you first have to drop the column-store index. After the data load you should re-create the column-store index in order to optimize the query performance again.
This behavior perfectly fits to the e-fact tables of SAP BW cubes. In SAP BW each cube has 2 fact tables. The F-fact table is optimized for data load, while the E-fact table is optimized for query performance. Data can be loaded the whole day into the F-fact table. However, the E-fact table is only modified during a process called BW cube compression, typically running at night.
Improved BW cube compression for the column-store
A column-store is dropped during BW cube compression (aka condense or collapse). At the end of the BW compression process, the column-store is automatically re-created again. You may argue that the runtime of BW cube compression increases when using a column-store index. However, the opposite is the case. When using the column-store, there are only 2 indexes on the E-fact table (the clustered, unique P-index and the CS-index). Without the column-store there are many additional indexes on the E-fact table, which have to be maintained during cube compression.
In order to speed-up the cube compression, additional improvements have been implemented in the BW compression code. Index creation is performed using SQL Server parallelism (per default 8 threads for creating the column-store index). A MERGE statement is now been used also in SAP BW 7.00 (This feature was down-ported from SAP BW 7.11). In addition, a SQL Server optimizer hint (HASH JOIN) is used in the MERGE statement.
Required SAP Support Package
The next SAP BW support packages will contain several improvements. The code for the index maintenance (cube activation, index check, index repair, update stats), cube compression and repartitioning was standardized for all SAP BW releases (7.00 to 7.30). Further new improvements have been implemented. This code base is also needed for using column-store indexes of SQL Server 2012 with SAP BW. The new code will be delivered in the next SAP support packages in August/September 2012:
SAP BW |
SAP Basis |
|
7.00 |
SP 29 |
SP 27 |
7.01 |
SP 12 |
SP 12 |
7.02 |
SP 12 |
SP 12 |
7.11* |
SP 10 |
SP 10 |
7.30 |
SP 8 |
SP 8 |
* SP 10 is sufficient for using column-store indexes with SAP BW 7.11. However, SAP may not generally support SQL Server 2012 for BW 7.11 at the point in time when SP10 is released.
These SPs do not contain any user interface to define a column-store index. The next but one SP (e.g. SAP BW 7.00 SP30) will contain the report MSS CSTORE , which has the look and feel of MSSCOMPRESS. With MSSCSTORE you can view and modify existing cubes by creating the column-store index in dialog or in an SAP batch job. In the meanwhile you can use the lightweight report Z MSS CSTORE , which will be delivered using an SAP note.
New Index Layout
With the next SAP BW support package, the index layout for SAP BW cubes changes slightly on Microsoft SQL Server. All new cubes will be created with the new layout. All existing cubes can be adjusted by using SAP transaction RSA1 ("Manage cube" => "Performance" => "Repair DB indexes") or by SAP transaction RSRV ("All Elementary Tests" => "Database" => "Database Indexes of an InfoCube and Its Aggregates"). However, repairing the cubes is typically not necessary. The indexes will be adjusted automatically when creating indexes within a BW process chain.
New Index layout (without column-store)
There will be no P-index on the F-Fact table anymore. This index was helpful in SQL Server 7. In newer SQL Server releases the query optimizer is smart enough to use the existing single column indexes of the F-fact table. However, there are two exceptions: APO cubes and delta-write enabled cubes still have a P-index on the F-fact table.
The field order of the P-index (on the F-fact and E-fact table) has been changed. It now starts with the time dimension, which is used in almost all SAP BW queries.
There is no index for the unit dimension on the fact tables any more. In addition, the index for the packet dimension on the e-fact table for non-cumulative cubes was removed. This already had been introduced in SAP BW 7.11 and will become standard in SAP BW 7.0x, too.
For the dimension tables, single-column indexes had been introduced with SAP BW 7.11. This has also been down-ported to SAP 7.0x. These indexes can improve query performance. However, data load may slow down (during SID generation). Therefore a new feature was implemented: The single-column indexes of the dimension tables are dropped in a BW process chain at the same time as the indexes of the F-fact tables. They are re-created again at the end of the process chain.
Index layout with column-store
On the E-fact table there is a non-clustered column-store index on all table fields. The index is visible in the SAP data dictionary and has the name "CS". Besides the column-store index, there is only one other index on the E-fact table: The P-index on all dimensions of the cube. The P-index is a unique, clustered index, having the time dimension as first field.
A column-store index will only be created on basis cubes, not on aggregates. It will also not be created for transactional cubes. Of course, it will not be created when running on SQL Server releases older than SQL Server 2012.
Creating a cube with CS-index
You can either turn on column-store indexes globally for the BW system or define per cube whether a column-store index is used or not. If column-store indexes are not turned on globally, then creating a cube with a column-store index consists of the following steps:
- Create and activate the cube as usual in transaction RSA1
- Define the cube as a column-store cube in report ZMSSCSTORE
- Create the column-store index by either running "Repair DB indexes" in RSA1 or RSRV or run a BW process chain which contains the process type "Create Index". Alternatively you can simply again activate the cube in the modeling view of RSA1.
Report ZMSSCSTORE
In report ZMSSCSTORE you can define the use of column-store indexes for an individual BW cube or all BW cubes. You can choose one of four different options and then press EXECUTE (F8).
Turn on CS-index for a single cube
The individual cube (CUM_BT) is marked to have a column-store index. However, the column-store index is not created at this point in time (you have to run RSA1, RSRV or a process chain later). The aggregates of this cube are immediately deactivated.
Turn on global flag
When creating or repairing the indexes of any cube in the future, a column-store index will be created. In addition, these cubes will be individually marked to have a column-store, too. When setting "Process all BW Cubes", the content of the field "Process single BW Cube:" is ignored. Be aware, that all aggregates of all cubes are immediately deactivatied.
Turn off global flag
The global flag for column-store indexes is turned off. This does not have any effect on cubes, which are already individually marked to have a column-store. The aggregates of any cube are not touched. Deactivated aggregates are not activated again.
Turn off CS-index for a single cube
The column-store definition for an individual cube is removed. However, this has no effect as long as the global flag is turned on. Deactivated aggregates of this cube are not activated again.
CS-index in SAP Data Dictionary
The column-store index is defined in SQL Server and in SAP Data Dictionary (DDIC). Due to the restrictions of the SAP DDIC, only the first 16 fields of a column-store index are visible in SAP DDIC. However, on the database there are always all table fields contained in the column-store index. The CS-Index is defined in SAP DDIC as "SQL Server Only" ( I nclude MSS ). Once there is a column-store index, all single-column indexes are dropped on the database. In SAP DDIC theses indexes still exists, but they are marked as "Not for SQL Server" ( E xclude MSS )
Here is an example of the index definition in SE11 for an E-fact table having a column-store index:
A similar cube without column-store index looks like this:
Current restrictions and future features
There are a few restrictions you should be aware when using column-store indexes with SAP BW:
Cube compression
Cube Compression is fully aware of the column-store index. The index is automatically dropped at the beginning and re-created at the end of the cube compression. However, an administrator should regularly check for failed cube compression jobs. If it fails for any reason, the column-store index may not exist anymore on the database. The missing column-store has no functional impact, but will probably result in very bad BW query performance.
Repartitioning
The Full Repartitioning also works with column-store indexes. However, it is typically not needed. When having a column-store index, we typically do not see any additional performance benefit by partitioning the E-fact table by a time characteristics. A failed Repartitioning job may also result in a missing column-store index (also this is very unlikely).
Selective Deletion
The Selective Deletion is currently not aware of the column-store index. Therefore you should manually drop the indexes in RSA1 before running Selective Deletion and manually create them again in RSA1 afterwards. Be aware, that Data Archiving is also using Selective Deletion. Column-store support for Selective Deletion is currently in development.
Remodeling
The Remodeling is a very seldom activity in SAP BW systems. In order to use it, you first have to remove the column-store definition for affected cube and re-create the database indexes.
System Migration
Homogeneous and heterogeneous system migration is currently not aware of the column-store index. Column-store support for System Migration is currently in development.