Skip to Content

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.


What?

Detail

Size

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


Status

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

9,8

0

9,8

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

1,3

13,3

14,6

+ 4,8

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

0,5

14,7

15,2

+ 0,6

After creating Columnstore indexes for the Infocube

0,4

1,5

1,9

– 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:


Query

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

12

8

3

Query 2

62

40

26

Query 3

30

17

Query 4

117

29

Query 5

245

27



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:


System

Total runtime for all tested queries [sec]

Source System (Oracle, no database compression)

2500

SAP Hana

300

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

360

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

308

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.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Mel Calucin

    Beate,

    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?

    Regards,

    Mel Calucin

    (0) 
  2. 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.

    Thanks

    Srikanth M

    (0) 
    1. 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.

      Regards,
      Eduardo Rezende
      SAP Support

      (0) 
      1. Srikanth Mandalapu

        Thanks,

        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?

        (0) 
        1. 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?

          Regards,
          Eduardo

          (0) 

Leave a Reply