Using SQL Server 2012 Column-Store with SAP BW
SAP COMMUNITY NETWORK scn.sap.com
© 2012 SAP AG 10
Configuring Column-Store in SAP BW
To use the column-store for SAP BW cubes, you first have to define the cubes, which should have a column-
store index on the e-fact table. This definition is stored in an SAP Data Dictionary table. The next step is to
convert the e-fact table by dropping unnecessary B-Tree indexes and creating the column-store index. You
can perform both steps either separately or within a single job using SAP report MSSCSTORE.
Defining cubes with Column-Store index
The Data Dictionary table MSSSTORAGE contains SQL Server specific storage parameters and global
settings. For each e-fact table using the column-store, there is a row in table MSSSTORAGE. You can use
report MSSCSTORE to change the settings in table MSSSTORAGE.
Note: Table MSSSTORAGE contains additional rows that are not related to the column-store. Do not modify the content
of table MSSSTORAGE manually. Always use report MSSCSTORE.
Converting cube according to definition
Defining the column-store index for an SAP BW cube is very fast, since it only modifies the SAP Data
Dictionary. Actually converting a cube can take some time. Therefore, we recommend running the
conversion always as a batch job. During the conversion to the column-store, several b-tree indexes are
dropped and a column-store index is created. When converting back to the row-store, the b-tree indexes
have to be re-created. Actually, converting the cube is nothing else than repairing the indexes of e-fact table
according to the definition stored in table MSSSTORAGE.
Note: Creating indexes, in particular b-trees, requires a significant amount of system resources: CPU, memory, I/O and
data and log space on the database. In a typical BW system, process chains drop and create indexes the whole
day. To reduce the required transaction log space, it might be a good idea to set the SQL Server recovery model
to Bulk-logged. Keep this in mind when setting up your backup strategy for the database of your BW system.
There are several ways to convert a BW cube from row-store to column-store, and vice versa:
Report MSSCSTORE
Using SAP report MSSCSTORE is the easiest way to convert the BW cube. SAP strongly recommends
running the report as a batch job. MSSCSTORE is described in detail below.
Activate empty cube
If an SAP BW cube is empty, a cube activation creates all database tables and indexes dependent on the
configuration in MSSSTORAGE. Therefore, you can simply convert an empty cube by activating it in SAP
transaction RSA1.
Manymanuals.com
Manymanuals.de
Manymanuals.fr
Manymanuals.it
Manymanuals.pl
Manymanuals.cz
Manymanuals.es
Manymanuals-pt.com
Commentaires sur ces manuels