SQL Server Column-Store with SAP BW Aggregates

• First published on MSDN on Mar 13, 2014 by Martin Merdes.
• This blog post might be outdated by now. The latest documentation regarding SQL Server Columnstore on SAP is available in https://www.sap.com/documents/2019/04/023e5928-487d-0010-87a3-c30de2ffd8ff.html and in SAP Note 2116639
• The performance impact of SQL Server Columnstore is described in https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Performance-evolution-of-SAP-BW-on-SQL-Server/ba-p/368073

Last week we extended the SAP BW support for SQL Server 2012 column-store by releasing SAP note 1951490 . The correction instructions of this note contain several improvements. The most important one: You can now use the column-store for BW aggregates. It is not restricted any more to BW basic cubes. Furthermore, the user interface of report MSSCSTORE has been enhanced. Finally, the performance of BW cube compression (which is also part of the BW aggregate fill and rollup) has been increased. The last item is described in BLOG https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Increasing-BW-cube-compression-performance/ba-p/367736

How to apply the new code

Before starting, we recommend reading the updated SAP notes 1771177 and 1951490. Then you should apply the correction instructions of

1. SAP note 1771177 - SQL Server 2012 column-store support for SAP BW :
   This note contains the necessary correction instructions for using SQL Server 2012 column-store. If this note was already applied, then download the newest version of the note in SAP transaction SNOTE and re-implement it. The corrections of this note are comprehensive. They are already contained in the newest BW support packages. Therefore it is a good idea to install the newest BW support package rather than applying the note.

1. SAP note 1951490 - SQL Server Column-Store for SAP BW Aggregates :
   This note contains the necessary correction instructions for using BW aggregates with SQL Server 2012 column-store. It can be applied for all SAP releases and support packages, which already support SQL Server 2012 column-store. {For some support packages, note 1771177 may be required as a prerequisite.}

1. SAP note 1990050 - Aggregate fill does not use parallel SQL query :
   This note is only necessary for SAP release 7.0x

Why using aggregates when having a column-store?

Originally we did not support the column-store for aggregates. On the one hand, the column-store perfectly scales with millions and billions of rows. Therefore there is typically no need creating aggregates when using the column-store. On the other hand, you typically do not benefit much from the column-store (compared with the row-store) when having only a few thousand rows. Therefore we originally recommended creating a column-store index on the SAP BW basis cube and deactivating all aggregates.

However, customer experience has shown some scenarios where we clearly benefit from having aggregates even for SAP BW cubes with a column-store index. Therefore some customers still created aggregates. Since there was no column-store support for them, the aggregates still used the row-store (B-tree indexes). Since the BW OLAP processor is not aware of the indexes on the database, BW queries preferred the aggregates (with row-store) to the basis cube (with column-store). It is pretty clear that this is a suboptimal solution. Therefore we now always create the aggregates the same way as we create the basis cube. If the basis cube has a column-store index then all aggregates will have one. If the basic cube uses the row-store then all aggregates use the row-store, too.

For most BW cubes there is still no need to create aggregates when using the column-store. However, for the following scenarios aggregates may be useful:

1. Ability to delete faulty requests:
   For SQL Server 2012 you can only use the column-store for the BW e-fact table. Uncompressed BW requests are stored in the f-fact table, which cannot use the column-store. Many customers do not want to compress all BW requests. Typically they only compress requests, which are older than a few weeks. This procedure allows deleting faulty requests, if the wrong data is identified within a few weeks.
   By default, BW aggregates are fully compressed, even if there are uncompressed requests in the basic cube. When using aggregates, customers can use the column-store for all requests. However, they still can delete faulty requests from the basic cube (and then rebuild the aggregates).

1. Reduced OLAP time of BW-queries:
   If there is data in the e-fact and f-fact table, SAP BW runs two independent DB-queries, against e-fact and f-fact table on the database server. The result of both queries has to be aggregated (GROUP BY) on the BW application server. Dependent on the size of the DB result sets, this aggregation can consume a huge part of the BW-query runtime. When using aggregates (with all data in the e-fact table), there is no need for the aggregation in the BW application server.

1. Faster BW queries on aggregates with only a few characteristics:
   SAP BW automatically uses as many line item dimensions as possible in an aggregate. This is independent from the actual design of the cube. All dimensions (except the PACKET and UNIT dimension) of a cube containing only a few characteristics (up-to 16) are line item dimensions. This means, there is no dimension table. BW queries on these "flat" aggregates are faster since there is no need to join the e-fact table with the dimension tables.

1. Faster BW queries when using navigation attributes
   Aggregates contain navigation attributes directly in the fact tables. In contrast, basic cubes have to be joined with the attribute tables. Therefore, BW-queries using navigation attributes can run faster on aggregates compared with basic cubes.

How to set the column-store property for an aggregate

In report MSSCSTORE you can define the column-store property for a BW basic cube. There is no way to define it for an aggregate. The column-store property of an aggregate is always in sync with the column-store property of its basic cube, once you apply SAP note 1951490. If a cube has been created with column-store index in the past then its aggregates had no column-store index. When applying SAP note 1951490, the existing aggregates are not touched. They still have no column-store index. They are therefore inconsistent on the database. However, the aggregates can still be used (with row-store). They will be automatically converted to column-store during the next aggregate fill or rollup. Alternatively you can manually repair the database indexes of the aggregates in SAP transaction RSRV, RSA1 (Repair Aggr. DB Indexes) or report MSSCSTORE.

Improvements of report MSSCSTORE

By applying SAP note 1951490, a new version of report MSSCSTORE is also available. When repairing the indexes of a cube, all indexes of its aggregates are repaired, too. You can easily recognize the new version by the new function key “Status of single cube”


By pressing this function key, you get an overview of all fact tables of a particular cube and its aggregates:


The dialog box above shows several details of the cube:

1. Number of lines:
   The cube consist of the basic cube (LOAD_A01) and one active aggregate (100010), each having a f-fact and an e-fact table. This results in 4 lines in the dialog box above. The Type and Table-Name of the fact tables are displayed in a separate column.

1. DDIC-Status:
   The SAP DDIC (Data Dictionary) expects having a Read-Only Column-Store Index for the f-fact tables and B-tree indexes for the e-fact tables. This is calculated by the setting of MSSSTORAGE (which can be set by report MSSCSTORE) and the running SQL Server version.

1. DB-Status:
   The DB-Status compares the expected indexes in SAP DDIC with the actual indexes on the database. The DB status is green in this example, means all indexes exist on the database as expected. This is exactly the same status as seen in transaction RSRV or RSA1 (Check DB indexes, Check Aggr. DB Indexes)

1. Rows:
   The number of rows for each fact table is displayed. In the example you can see that all rows of the basis cube are stored in the f-fact table (with row-store), while all rows of the aggregate are stored in the e-fact table (with column-store). This means that only the aggregate – not the basic cube – benefits from the column-store. If you want to benefit from the column-store in the basic cube, then you have to perform a BW cube compression (which moves rows from the f-fact table to the e-fact table)

1. VALID FROM:
   In this column you can see the minimal SQL Server version, which is necessary to use the configured column-store index. For example, SQL Server 2012 does not support the column-store for key figures with an increased precision. One example is the cube 0CCMDAACT from CCMS. When defining the column-store in report MSSCSTORE, the cube looks like this on SQL 2012:
   
   We have defined, to use the non-clustered column-store index for the e-fact table. Therefore MSSSTORAGE = “NONCLUSTERED”. However, the cube has at least one key figure with increased precision. This is only supported as of SQL Server 2014. Therefore the DDIC Status is “B-Tree Indexes”.
   Once you upgrade your system to SQL Server 2014, the cube looks like this:
   
   Since we are on SQL 2014, the DDIC Status is “Read-Only CS”. However, the cube was not touched during the upgrade from SQL Server 2012 to 2014. Therefore, there is still no column-store index on the database. This will be automatically repaired during the next run of a process chain, which creates the database indexes. Alternatively you can manually repair the indexes using RSRV, RSA1 or MSSCSTORE. After that it looks like this: