Using SQL Server 2012 Column-Store with SAP BW
SAP COMMUNITY NETWORK scn.sap.com
© 2012 SAP AG 6
SAP Data Dictionary Support
Generally, the SAP Data Dictionary (DDIC) only supports indexes with up to 16 columns. Furthermore, the
DDIC does not support special index types, like column-store. Therefore, the SAP DDIC was extended by
table MSSSTORAGE, which persistently stores SQL Server specific database storage parameters.
MSSSTORAGE contains the column-store configuration per table, which defines whether or not a column-
store index is used. In addition, MSSTORAGE contains a system default, which is used for new tables.
The column-store configuration in SAP DDIC is taken into account for the following activities:
Cube activation in SAP BW
Index checking and creation in SAP BW
Index consistency checks in SAP DDIC
Table conversion in SAP DDIC (for example in SAP transaction SE14)
SAP system copy using R3load (if SMIGR_CREATE_DDL is properly executed)
When transporting (for example from a development to a productive system), a cube is always created using
the system default on the target system. Therefore, SAP recommends using the same system default of the
column-store configuration for the source and the target system.
When using the column-store, a BW e-fact table has two indexes on the database:
P-index:
Unique, clustered b-tree index on all (up to 16) dimensions of the cube
CS-index:
Non-clustered column-store index on all fields (dimensions and key figures)
Index Layout in SAP DDIC
When using the column-store, the additional CS-index is created in SAP DDIC. The obsolete single column
indexes are not deleted in SAP DDIC. They are simply marked as Not for SQL Server (Exclude MSS).
Index using Dimension COND_C01T
Index using Dimension COND_C011
Index using Dimension COND_C012
Index using Dimension COND_C013
Index using Dimension COND_C014
Index using Dimension COND_C015
Index using Dimension COND_C016
Index using Dimension COND_C017
Index using Dimension COND_C018
Columnstore Index on ALL fields
Unique Index (Key Fields) for Table
/BIC/ECOND_C01
Index layout on database with CS-index
By choosing Database Object from menu Utilities in SAP transaction SE11, you can see the indexes on the
database. The following example shows the indexes of an e-fact table using the column-store. Here you see
up to 16 columns of the table as part of the CS-index. However, in reality, the CS-index contains all 21
columns of the table. SAP does not show the additional index columns here to make sure that the
consistency check with SAP DDIC works fine.
Manymanuals.com
Manymanuals.de
Manymanuals.fr
Manymanuals.it
Manymanuals.pl
Manymanuals.cz
Manymanuals.es
Manymanuals-pt.com
Komentáře k této Příručce