SAP and MS SQL 2012

Lower TCO without any aditional license.

With the new version of MS SQL, from MS SQL 2012 and on, any SAP customer can benefit from a huge data compression of its database (85%) while improving its SAP performance and make its SAP BW queries 3 to 5 time faster (average) by activating the new column store indexes for SAP BW infocubes. Those techniques have been used in productive systens by Microsoft and organization all over the world. ELSOP has upgrade some of its customer to MS SQL 2012 resulting a more than 85 % SAP BW database reduction and 4 times quicker SAP Bex/BO queries. All of this without any additional license for the customer, within a month, with less than 4 hours down time and in a completelly transparent way for the end user.

Database compression for all SAP products

Data in tables and indexes can be compressed, in addition to data at the row and page levels. Row-level compression reduces database size without additional resource consumption. Page-level compression, which is now a default compression type in SAP, reduces the size of a typical SAP ERP database by up to a factor of four, helping to improve overall response time and reduce storage costs in a dramatic way. According to SAP, applying row compression on an SAP ERP Unicode system on SQL Server resulted in approximately 50 percent space savings and approximately 75 percent space savings when using page compression.

ELSOP has achive a comression from 1,5 TB to 242 GB in one of its Customer's SAP BW system. 

xVelocity - Column store for SAP BW

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. 

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

SAP BW 15,000 partitions (SAP BW more than 1000 partitions)

SAP NetWeaver BW customers who perform daily loads can benefit from the increase in table partitions from 1,000 to 15,000.