Freigeben über

Using Columnstore on ERP tables

  • Artikel

SQL Server columnstore indexes are optimized for aggregations of large amounts of data. Therefore, they are successfully used in SAP's data warehouse system SAP BW since years. ERP systems typically still use rowstore (b-tree) indexes, because they are optimized for the most common data access pattern of ERP systems: Directly reading a few rows specified by very selective filters or the primary key. However, there are also reporting queries in ERP systems, which have to access a large number of rows. Such queries would benefit from a columnstore index, too.

When talking about ERP below, we mean all non-BW products of the SAP Business Suite like ERP or CRM.

Since we released our first version of SQL Server columnstore in 2012, we constantly get requests from SAP customers for using the columnstore on an ERP system, too. This was not possible for various technical reasons in SQL Server 2012 and 2014. This restriction is gone in SQL Server 2016, see also https://blogs.msdn.microsoft.com/saponsqlserver/2016/11/11/sql-server-2016-improvements-for-sap-bw. You can now create an additional Nonclustered Columnstore Index (NCCI) on an SAP ERP table, which results in a hybrid data storage: The table itself, the primary key and all existing indexes stay in row format (b-trees). Only the new, additional index is stored in columnar format.

Customer Scenarios

SAP will not deliver columnstore indexes on ERP tables within SAP installation or upgrade. The NCCI is intended as a tuning option for specific customer scenarios. Selecting suitable ERP tables and testing the impact of the NCCI is a consulting project. An NCCI will certainly improve reporting performance. However, it may have negative impact on transactional throughput. Every modification of a data record has to be done on the columnstore, too.

For cubes of an SAP BW system, the columnstore index replaces several rowstore indexes, which results in disk space savings. This is possible, because we exactly know the workload of SAP BW cubes. However, for ERP systems, we cannot exactly tell you unneeded indexes. Each customer implements different customizing and has a different work load mix. Therefore, the NCCI is intended as an optional, additional index.

Using the columnstore in an SAP ERP system is not intended as a replacement for a dedicated SAP BW system. SAP BW is fully optimized for reporting queries. A distinct SAP BW system separates the reporting workload from the transactional workload of SAP ERP.

Creating a Columnstore Index

After applying SAP Note 2419662, you can create one NCCI per table using SAP report MSS_CS_CREATE. This Columnstore Index always contains all fields of the table (with a few exceptions, e.g. IMAGE fields). Report MSS_CS_CREATE only has three parameters: Table name, index name and degree of parallelism, which defines the number of logical CPUs used for the index creation.

erp

You can schedule report MSS_CS_CREATE as a batch job. The NCCI is always created offline, means concurrent row modifications on the same table are blocked while the NCCI is being created. SQL Server 2016 does not support the online creation of an NCCI. This feature is planned for the next version of SQL Server.

Integration in SAP DDIC

All indexes in SAP are defined in the SAP Data Dictionary (DDIC). Unfortunately, an index in DDIC is restricted in the number of columns and the number of bytes per index row. Therefore, we had to trick the DDIC somehow: For an NCCI, the index columns in DDIC and on the DB do not always match. However, you do not have to take care about this: The new SAP report MSS_CS_CREATE creates the NCCI on the database. At the same time, it creates a DDIC definition for the NCCI, which fulfills all DDIC requirements.

DDIC does not know anything about the columnstore property of an index (it is stored as a DDSTORAGE parameter). This results in a restriction for creating the NCCI in SAP: You cannot transport an NCCI from the development system to the productive system. Instead, you have to create the NCCI on both systems separately using report MSS_CS_CREATE.

Best practices

Columnstore indexes are only useful for large tables. Therefore, you should not even consider creating an NCCI on an ERP table with less than 10 million rows. As a matter of course, an NCCI is only useful on tables, which are used for long running, complex reporting queries. Ideally, these tables have a low or moderate rate of change.

For best reporting performance, you should make sure that all columnstore rowgroups are compressed. The concepts of columnstore rowgroups and the procedure of rowgroup compression is described in https://blogs.msdn.microsoft.com/saponsqlserver/2015/03/24/concepts-of-sql-server-2014-columnstore. For SAP BW, rowgroup compression is performed as a final step during data load. In SAP ERP, there is no separate data load phase. Instead, ERP tables are updated all the time during normal working hours. If you have a dedicated time window for your reporting, you might run the columnstore rowgroup compression strait before running your reports (which are supposed to use the NCCI). For this purpose, you can use report MSSCOMPRESS as described in https://blogs.msdn.microsoft.com/saponsqlserver/2016/11/25/improved-sap-compression-tool-msscompress.

erp2

There are two options in report MSSCOMPRESS for processing columnstore rowgroups:

  • When choosing "Compress Rowgroups", then an ALTER INDEX REORGANIZE command (with the option COMPRESS_ALL_ROW_GROUPS=ON) is performed, if (and only if) there are uncompressed rowgroups in the columnstore index of the selected table.
  • When choosing in addition "Force CS Reorganize", then the ALTER INDEX REORGANIZE command is always performed. Thereby small rowgroups are merged (in addition to the rowgroup compression of open rowgroups).

For very large SAP ERP tables (some hundred million rows), the rowgroup compression is not as important as for SAP BW. Since an SAP ERP table is never partitioned, you can have a maximum of one million uncompressed rows. On the other hand, the ALTER INDEX REORGANIZE also optimizes already compressed rowgroups when lots of UPDATEs and DELETEs had been executed before. Therefore, you might run the rowgroup compression as a periodical SAP batch job using the scheduler in report MSSCOMPRESS.

Conclusion

The 3rd generation of SQL Server columnstore provides lots of improvements. You can now use the columnstore even for SAP ERP systems. Therefore, it is highly recommended to upgrade to SQL Server 2016.

Comments

  • Anonymous
    June 15, 2017
    I was trying to create an index on GMIA, I got the below error, ,[LOEKZ] ,[BILLING_BLOCK_9] ,[DOC_ORIGIN_9] ,[.INCLUDE] ,[WBS_SHORT_9] ,[AWSYS] ) with ( maxdop = 2 ) Column name '.INCLUDE' does not exist in the target table or view. DDL time(___1): .........5 milliseconds The SQL statement was not executed Index could not be created completely in the database Index GMIA-ZCS could not be created Request for GMIA could not be executedAs I understand the problem .include is a structure but not a field on the table.
    • Anonymous
      July 25, 2017
      The comment has been removed
  • Anonymous
    July 05, 2017
    Hi Martin,This sounds very promising. We tried this on a ECC6 system on 750 SPS 5. It worked on some tables.But when we used a table with a .INCLUDE, the following happened at the end of the actication logColumn name '.INCLUDE' does not exist in the target table or view."DDL time(___1):" ".........5" milliseconds sql: "DELETE FROM DDSTORAGE" "WHERE DBSYSABBR = 'MSS'" "AND TABNAME = 'MSEG'" "AND INDEXNAME = 'ZCS'" "DDL time(___2):" ".........1" milliseconds The SQL statement was not executed Error creating index "MSEG~ZCS" in the database Index "MSEG"-"ZCS" must be created in the database Index "MSEG"-"ZCS" was activated Thanks,Florian
    • Anonymous
      July 25, 2017
      The comment has been removed