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:
Brief explanation on
My personal favorite as the author managed to compress all the information you need to understand:
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.
DB Size after compressing requests of Infocubes
All requests except the ones from the current month were compressed in transaction RSA1.
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.
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
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
After compressing all requests of the Infocube except the ones from the last and current month
After creating Columnstore indexes for the Infocube
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]
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)
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
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
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.