Dynamic Precision BPC 7.3 Instrukcja Użytkownika Pobierz pdf (Strona 9)

Using SQL Server 2012 Column-Store with SAP BW

SAP COMMUNITY NETWORK scn.sap.com

Aggregates

By creating a fully optimized aggregate, you can enormously speed up the runtime for a particular BW query.

However, customers often try to avoid the overhead of creating BW aggregates. If aggregates are really

needed, customers try to find a compromise between the overhead of aggregate maintenance and BW query

performance: A single BW aggregate is typically not fully optimized for a single BW query. In return, the

same aggregate can support a variety of BW queries at the same time.

When using the column-store, you typically do not need aggregates at all. Therefore, you should deactivate

the aggregates of a cube at the same time when defining the column-store (in report MSSCSTORE, see

below). The definition of the aggregates then still exists in SAP BW. However, the database tables of the

aggregates are dropped.

Note: We recommend not using BW aggregates for cubes using the column-store for the following reasons:

1. BW aggregates never use the column-store. This is by design in to support the BW Change Run

2. The BW OLAP engine is not aware of the column-store. Therefore, the OLAP engine may decide to use

an aggregate although the basis cube has a column-store index

3. Typically, queries using BW aggregates are slower than queries using a column-store index

4. Not using aggregates results in further disk space savings and less administration efforts

BW compression

Since the non-clustered column-store index is read-only, it has to be dropped at the beginning of the BW

cube compression and re-created at the end of the cube compression. This is all performed automatically. A

BW administrator has not to take care of this, with one exception: If the BW cube compression fails for any

reason (for example due to a full database transaction log), the column-store index may not exist anymore

on the database. In this case, the index has to be created manually using SAP transaction RSA1 (see below

at “Repair indexes”).

You may argue that the BW cube compression takes more time when using the column-store, since you

have to re-create the column-store index. However, it is often the other way round. You do not need to

maintain all the single column indexes of the e-fact table when compressing a cube with column-store index.

The index creation of a column-store index is very fast. By default, SAP BW is requests eight SQL Server

threads for this task. You can further increase the parallelism for index creation by setting the RSADMIN

parameter MSS_MAXDOP_INDEXING (see below).

The BW cube compression on Microsoft SQL Server is today much faster than a few years ago. By using the

SQL MERGE statement, the aggregation from the f-fact to the e-fact table has become faster. The deletion

of the rows from the f-fact table has become much faster due to an improved partition drop algorithm in SQL

Server 2008 SP2 and newer SQL Server releases.

However, the cube compression nowadays contains an additional step. It runs an update statistics on all

database tables of the compressed BW cube. You can turn off the update statistics by setting a RSADMIN

parameter. However, we do not recommend doing so. For details, see

http://blogs.msdn.com/b/saponsqlserver/archive/2012/08/20/how-update-statistics-really-works-in-sap-

bw.aspx

Note: BW query performance is best, if all BW request are compressed and therefore the column-store index on the e-

fact table can be fully leveraged. However, you do not need to compress a cube each time a new request is loaded

into the cube. You can schedule BW cube compression once a day or even once a week. Thereby you make sure

that the majority of the BW request is always compressed. This is typically sufficient to get a good BW query

performance.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 ... 18 19

Komentarze do niniejszej Instrukcji

Brak uwag

Dynamic Precision BPC 7.3 Instrukcja Użytkownika Strona 9

Komentarze do niniejszej Instrukcji