Skip to Content
Author's profile photo Former Member

Experiences with SQL Server Column Store Indexes and SAP BW

As I’ve been quite busy within the last year I didn’t find much time to contribute or share any breaking new for a while – even though the lack of spare time didn’t improve much recently it’s finally time to talk about a ‘new’ topic which at least everyone working with BW has definitely come across lately – Columnstore.

Within the last year I spent a lot of time migrating BW systems to SQL Server, implementing SQL Server Columnstore Indexes and trying to make the queries and loads as fast as I can or if possible faster than they are on Hana. Today I want to share the results with you.

What is a SQL Server Columnstore Index, how does it work and how to implement it in an SAP BW system?

Many documents answer these questions a lot better than I ever could so I will not repeat the explanations for you but rather point you to the documentation that I found most helpful:

Column Store Indexes Described

Brief explanation on
– what Columnstore is
– how it works technically and
– what the main advantages are

Using SQL Server 2012 Column-Store with SAP BW

My personal favorite as the author managed to compress all the information you need to understand:
– which requirements need to be fulfilled
– how to implement column store indexes
– what to expect

within 19 pages

How difficult is it to implement SQL Server Columnstore Indexes and how long does it take?

In my opinion, SAP does a very good job with providing easy-to-use tools on SAP software level to implement quite powerful features on SQL Server level.
I saw this with row/page compression where a single report (MSSCOMPRESS) was all one needed to row/page compress database objects online/offline. With implementing SQL Server Columnstore Indexes it’s as simple again. SAP provides a report called MSSCSTORE which allows you to create Columnstore Indexes for a single infocube or all infocubes in the system and you can also use this report to switch back to rowstore for a single or all Infocubes (while I can’t think of a reason to switch back) with just a few clicks. The interface of the report is quite self-explanatory – if some questions still remain open it makes sense to look into SAP Note 1771177 and  Using SQL Server 2012 Column-Store with SAP BW.

I didn’t do any detailed performance analysis on the runtimes of implementing Columnstore Indexes and runtimes – as always – are influenced by many factors (hardware, system load, database size, and many more). However, the database sizes where I implemented column store indexes varied from 3 to 12 TB, and the runtime for all Infocubes in total varied from 3 to 12 hours.

Real-World Results with SQL Server Columnstore Indexes

The experiences I gained with SQL Server Columnstore Indexes are based on several proof-of-concept projects where the aim was to find out how fast BW queries and data load processes could get after switching from the conventional table structures of Infocubes to the usage of SQL Server Columnstore Indexes. Even though reducing the database size was not an important goal in any of the POCs I could observe a mentionable and sometimes even a massive reduction of the database size in every single case.

Case Study 1 – Reduction of space consumption

These numbers show how the space consumption of the database changed after different actions.




Original Size of the database

Allocated MB in transaction DB02 so no freespace within the DB Files contained in that number. All objects in the DB are page compressed.

9,5 TB

DB Size after compressing requests of Infocubes

All requests except the ones from the current month were compressed in transaction RSA1.
This has been done for all Infocubes. The aggregates still existed.

9,5 TB

DB Size after implementing Columnstore Indexes for the infocubes and deactivating aggregates

All requests except the ones from the current month were still compressed from the last action.
Columnstore Indexes were created with the option to deactivate all aggregates during the run.

6,2 TB

DB Size with Columnstore Indexes for Infocubes and aggregates using Columnstore Indexes as well

Aggregates were configured to use Columnstore Indexes as well and have been activated again

7 TB

Looking at the tables and indexes of an Infocubes in more detail before and after implementing Columnstore Indexes I gained an idea on how this amount of space is saved without doing anything but creating Columnstore.

Space Consumption of E + F fact table of an Infocube after different actions


Size of F-fact table [GB]

Size of E-fact table [GB]

Size of E + F fact [GB]

Delta to last step [GB]

Original state – no BW requests compressed, no Columnstore indexes, page compressed objects




After compressing all requests of the Infocube except the ones from the last and current year




+ 4,8

After compressing all requests of the Infocube except the ones from the last and current month




+ 0,6

After creating Columnstore indexes for the Infocube




– 13,3

In this case I’ve been using SQL Server 2012 for my tests. As Columnstore indexes are not updatable with this SQL Server release they are only used on the E-fact table of an Infocube. After creating a Columnstore Index including all columns of the E-fact table all other secondary indexes on the table become superfluous – for this reason MSSCSTORE drops them when it creates the Columnstore Index. The space previously occupied by the secondary indexes of E-fact tables becomes freespace now. The Columnstore Indexes of course consume space as well but they are stored column-wise and not row-wise and as columns often have similar data, high compression rates can be achieved (also pointed out here Column Store Indexes Described). In all my tests the Columnstore Indexes were quite small in comparison to the table sizes.

In another project I didn’t record the sizes in this level of detail but it might still be interesting to know that the original database size (Oracle with no database compression feature in use) was around 3 TB and after migrating to SQL Server and creating columnstore indexes (page compression in use as well) it went down to 800 GB.

Case Study 2 – Comparison of Query Runtimes

Looking at the runtimes of a defined set of BW queries on the source system (Oracle) and a SQL Server system with Columnstore Indexes I could observe the following runtimes:


Source System [sec]

SQL Server 2012 with columstore on E-fact tables but without any aggregates [sec]

SQL Server 2012 with columstore on E-fact tables and selected aggregates without Columnstore [sec]

Query 1




Query 2




Query 3



Query 4



Query 5



When I carried out this test SAP’s recommendation was to let report MSSCSTORE deactivate all aggregates while creating the Columnstore Indexes. As the above runtimes show I was able to further speed up 2 queries after re-activating aggregates for 2 queries – back then these were still conventional aggregates without a Columnstore. In the SAP released column-store also for BW-aggregates (SAP Note 1951490) which presumably would have speeded up all tested queries again.

The total runtime of all tested queries looked like this:


Total runtime for all tested queries [sec]

Source System (Oracle, no database compression)


SAP Hana


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables but without any aggregates


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables and with selected conventional aggregates without Columnstore Indexes


SQL Server 2012 with page compression and Columnstore Indexes on E-fact tables and on aggregates

Unfortunately didn’t have the chance to test this but presumably < 308

Lessons Learned

Keeping it brief without going too much into detail I learned the following lessons:

  • In any case mentionable, mostly even massive reduction of the database size
  • Speedup by factor 0-50 per query depending on the query and the cube design
  • The bigger the cube, the more speedup
  • If bottleneck is not the database, the speedup with implementing tuning measures on DB level like Columnstore Indexes understandably turns out to be a modest affair
  • Load runtimes improve as less index maintenance is required
  • As soon as the requirements are fulfilled the implementation of Columnstore Indexes is very simple
  • The integration of Columnstore Indexes in SAP BW is constantly improved so it makes sense to look for new features on a regular basis and adopt them
  • Considering the achievable speedup it makes sense to benchmark if a BWA still makes sense or if the required runtimes can already be achieved using SQL Server Columnstore Index

Continuous Improvements

Within the last year Iots of improvements were introduced regarding the integration of SQL Server Columnstore Indexes into SAP BW. To make sure I do not miss any of them I keep looking for SAP documentation, SAP notes, blogs on SCN and the SAP on SQL Server section of MSDN before I start with a new migration. It seems that some improvements are shipped within SPs without being announced explicitly for this reason I also try to always use a recent SAP BW and SAP Basis support package.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Mel Calucin
      Mel Calucin


      Thanks for a very informative blog.  Can you tell me how you got the HANA performance number? Did you start out from an Oracle database and migrate them to MS SQL and HANA?


      Mel Calucin

      Author's profile photo Eduardo Rezende
      Eduardo Rezende

      Great blog Beate! 🙂
      Thanks for sharing.

      Author's profile photo Srikanth Mandalapu
      Srikanth Mandalapu

      Can I implement this for Solution Manager 7.1 SP10 on SQL Server 2012?

      As you know there is a BW component we use in Solman for reporting.


      Srikanth M

      Author's profile photo Eduardo Rezende
      Eduardo Rezende

      Hello Srikanth,

      Solution Manager 7.1 SP10 uses SAP_BW 7.02 SP 13, therefore you must implement notes 1771177 and 1951490. You can also update your Solution Manager to SP13 which contains both notes.

      Eduardo Rezende
      SAP Support

      Author's profile photo Srikanth Mandalapu
      Srikanth Mandalapu


      Yes I did implement those note but I didn't find any SAP note which says Column store is supported for Solution Manager.

      One of the issues I find in our Solman Production is E2E BI HOUSEKEEPING is not doing anything from a long time after implementing Column Store,

      I came across a video at msdn which says after column store index is implemented the Table is set to Readonly Mode,

      Any insights on this?

      Author's profile photo Eduardo Rezende
      Eduardo Rezende

      Hello Srikanth,

      Yes, SQL Server 2012 columnstore indexes are read only. Check Columnstore Indexes for further information.

      Within SAP environment, columnstore indexes are very good for BW cubes, why have you implemented your E2E objects to columnstore indexes?


      Author's profile photo Srikanth Mandalapu
      Srikanth Mandalapu

      Eduardo, do you mean that we can't use CS for E2E cubes?

      Author's profile photo Eduardo Rezende
      Eduardo Rezende

      E2E cubes are updated very frequently, therefore I don't recommend to use columnstore indexes with SQL Server 2012.