This Blog provides information about running SAP Applications on the Microsoft Platform. The Blog is written by people who are working with SAP on the Microsoft Platform for decades.
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 Increasing BW cube compression performance.
Before starting, we recommend reading the updated SAP notes 1771177 and 1951490. Then you should apply the correction instructions of
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:
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.
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: