Freigeben über

Simplified and faster SAP BW Process Chains

  • Artikel

Based on customer feedback over the course of the last 12 months we implemented several improvements in SAP BW. The code changes improve SAP platform migrations of very large SAP BW instances from other DBMS to SQL Server significantly. Furthermore, they simplify and accelerate SAP BW process chains for very large BW systems. The most important improvements are related to columnstore rowgroup compression, which is described below. Other recent improvements will be discussed in a separate BLOG.

Customers, who use the columnstore on Microsoft SQL Server 2014 (or newer), can now benefit from performance improvements and additional functionality. The new code is delivered in upcoming SAP BW Support Packages (SPs) and as a correction instruction in SAP Note 2329540 - Rowgroup Compression Framework for SAP BW / SQL Server. For implementing this SAP note, you need a minimum SAP BW SP. Therefore, we increased the minimum, recommended SP and updated SAP Note SAP Note 2114876 - Release Planning SAP BW for SQL Server Columnstore.

BW Rowgroup Compression

A SQL Server columnstore is organized in rowgroups, having up-to one million rows per rowgroup (see https://blogs.msdn.microsoft.com/saponsqlserver/2015/03/24/concepts-of-sql-server-2014-columnstore for details). For best query performance, you should trigger a columnstore rowgroup compression at the end of all BW Process Chains, which load data into a cube. You can either use a BW Cube Compression or the BW Process Chain type "Index Repair" for this purpose. However, some customers had an issue with this.

  1. When converting all cubes to columnstore, you had to add "Index Repair" to all existing process chains. Customers often wanted to avoid the pain of changing each and every process chain.
  2. In some BW systems we migrated we were faced with hundreds of thousands of rowgroups on SQL Server. Since a single rowgroup can contain up-to one million rows, these rowgroups could theoretically cover some hundred billion rows. It turned out soon, that queries against columnstore related SQL Server system tables (sys.column_store_row_groups) did not scale too well. Therefore, reading the metadata from SQL Server was even slower than running a rowgroup compression. This resulted in an unexpected long runtime of the BW Process Chain type "Index Repair". This issue does not occur on smaller systems with a reasonable number of DB partitions and rowgroups.

Simplified Process Chains

With the new Rowgroup Compression Framework of SAP Note 2329540, you can trigger a rowgroup compression also with the BW Process Chain type "Update Statistics". Update Statistics is typically already included in a Process Chain, even when the Process Chain was originally created for a different database platform. A typical process chain for loading data into a cube looks then like this:

pc

The new functionality is explicitly coded for BW Process Chains only. When running a manual Update Statistics (button "Refresh Statistics") in SAP transaction RSA1, the columnstore rowgroups are not compressed. If you want to perform a manual rowgroup compression in SAP transaction RSA1, you still have to choose the button "Repair DB Indexes"

111416_1441_Simplifieda2.png

Faster Process Chains

It was not possible to change the SQL Server system table sys.column_store_row_groups (in SQL Server 2014 and 2016). However, we achieve now very good query performance on columnstore metadata by using a different SQL Server system table (sys.system_internals_partitions) instead. For implementing this we had to change huge parts of the BW code in several areas (BW index repair, BW index check, BW update statistics, BW cube compression...)

Reading the metadata using the new method is several thousand times faster on very large BW systems. This results in much faster process chains, which contain the process chain types "Index Repair" or "Cube Compression". The only thing you have to do, is applying SAP Note 2329540. There is no need for configuring anything or for setting any RSADMIN parameter.

However, once SAP Note 2329540 is applied, you have various configuration options regarding columnstore rowgroup compression in SAP BW. The options are described in detail in the SAP Note. The major reason, why we implemented these options, was to be prepared for future changes in SAP BW and SQL Server. Furthermore, the new RSADMIN parameter standardizes already existing (undocumented) parameters. Without setting any RSADMIN parameter, you automatically get the preferred default behavior (which also depends on the SQL Server version).