Dynamic Precision BPC 7.3 Uživatelský manuál stáhnout pdf (Strana 5)

Using SQL Server 2012 Column-Store with SAP BW

SAP COMMUNITY NETWORK scn.sap.com

Usage of Column-Store

In SAP BW each cube has two fact tables. The f-fact table is optimized for data load, while the e-fact table is

optimized for query performance. SQL Server 2012 Column-Store can be leveraged for SAP BW e-fact

tables. A typical scenario looks like this:

 Data is loaded several times a day into SAP BW. Using a BW data transfer process, new data is

inserted into the f-fact table of a cube. A request ID is added to each data row to identify the data

transfer process that inserted the row. The f-fact table has the classical index layout with a separate

row-store index (b-tree) per dimension. This results in typically 10 to 16 indexes per f-fact table.

 Once a day (or less frequently) a BW cube compression runs, typically at night. The BW cube

compression moves (and aggregates) data from the f-fact to the e-fact table. The e-fact table has a

single (unique, clustered) b-tree index: the P-index. In addition, there is one column-store index on

all columns of the e-fact table: the CS-index. The total size of the cube is reduced by 3 factors:

o The aggregation of the data (by removing the request ID)

o The reduction of the number of indexes from typically 10 to 2

o The extreme good compression skills of the column-store

Customers may compress all requests of the f-fact table or compress only older requests (for

example requests older than 7 days). As a result, all data (or at least most of the data) of the cube is

stored in the e-fact table.

 During the cube compression, the CS-index is dropped and re-created. Therefore, it does not matter,

that the column-store is read-only. The index creation for a column-store index is much faster than

for a b-tree index. It scales quite well with the number of CPU threads. By default, SAP BW requests

eight database threads for index creation.

 An SAP BW query is broken down into two database queries: One is running on the f-fact table, the

other is running in parallel on the e-fact table. The database query on the e-fact table can fully

benefit from the column-store. The database query on the f-fact table is also fast, since the f-fact

table only contains a small portion of the cube data.

SAP BW restrictions

There are a few restrictions when using the column-store with SAP BW. For the latest information, see SAP

note 1771177.

Key figures with increased precision

SQL Server 2012 column-store does not support the data type DECIMAL, if the precision is higher than 18

digits. The data type DECIMAL is used in SAP BW for key figures. In SAP BW you cannot create a key figure

with a higher precision than 17 digits on your own (using SAP transaction RSD1). However, SAP can deliver

such key figures using BW Content. There are a few SAP applications based on SAP BW (for example SAP

BPC) that use such key figures with increased precision. Therefore, SAP BW simply does not use the

column-store for cubes with increased precision. This is independent from the column-store configuration

defined in report MSSCSTORE (see below).

SAP system upgrade to 7.3

Any SAP system upgrade of BW systems using the column-store works fine. However, when upgrading from

7.0x to 7.3x, the column-store indexes may be dropped during the SAP upgrade. They have to be re-created

after the SAP system upgrade using report MSSCSTORE. This issue will be fixed in SAP Software Upgrade

Manager (SUM) in the next support package (SL Toolset 1.0 support package 6).

BW Transport Connection

The column-store property of a BW cube is locally defined. It will not be transported from a BW test system

to a BW productive system. When a cube is transported, it is created using the system default (either with or

without column-store) on the target system.

SAP System Migration and System Copy (using either DB restore or R3load) are fully aware of the column-

store. When using R3load, do not forget to run report SMIGR_CREATE_DDL on the source system and

report RS_BW_POST_MIGRATION on the target system. For details, check the SAP System Copy Guide.

1 2 3 4 5 6 7 8 9 10 ... 18 19

Komentáře k této Příručce

Žádné komentáře

Dynamic Precision BPC 7.3 Uživatelský manuál Strana 5

Komentáře k této Příručce