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

Using SQL Server 2012 Column-Store with SAP BW

SAP COMMUNITY NETWORK scn.sap.com

Customer Experience with SAP BW using the column-store

Using the column-store has many benefits. The disk space requirements are much lower and BW query

performance is consistently better. Furthermore, BW administration is simplified, since you typically do not

need to maintain BW aggregates anymore.

Space Savings

SQL Server 2008 introduced row and page compression, which already reduced the disk space usage for

SAP BW tables. By using the column-store, you can further decrease the disk space usage of SAP BW e-

fact tables by additional 50% to 80% (even compared to page compression). The actual space savings

depend on two factors:

 The number of dimensions in the cube

When using the column-store, all single column indexes (one per dimension) can be dropped. The

more dimensions you have, the better disk space savings you see.

 Cardinality of the dimensions

The column-store compresses data much better compared to the row-store. The compression ratio

depends on the cardinality of the dimension (the number of rows in the dimension table).

We saw consistent space savings on our own test systems and on customer systems:

Cube

Data Type

Area

Rows

#Dim

Page comp.

Column-store

Saving

PERF_CSE

generated

Sales & Distribution

100,000,000

15.10 GB

6.61 GB

56%

CCA_C001

customer

Cost Center

21,000,000

2.70 GB

0.57 GB

79%

PY_C001

customer

Payroll

17,000,000

1.74 GB

0.39 GB

77%

PS_C001

customer

Projects

10,000,000

1.54 GB

0.38 GB

76%

GL_C10A

customer

General Ledger

16,000,000

1.42 GB

0.52 GB

63%

0TCT_C02

customer

OLAP Statistics

49,000,000

9.43 GB

1.89 GB

80%

Query Performance

SQL query performance is consistently increased when using the column-store with SAP BW. However, the

degree of performance increase of the column-store varied. We saw a wide spread, dependent on the data

and type of query. Some queries were up to 50 times faster, while others hardly benefitted from the column-

store index. The majority of the SQL queries we tested, ran about 3 to 6 times faster. You can benefit most

from column-store indexes under the following conditions:

 SQL Server has enough memory to keep the column-store index (of the selected table) in RAM

 SQL Server has at least a few idle CPU threads to use parallelism

 The selected table has at least a few million rows

Typically, an SAP BW query consists of several SQL queries running in parallel. One SQL query runs

against the f-fact table while another SQL query runs in parallel against the e-fact table. When using BW

multi-providers, you see additional parallel running queries. Therefore, SAP BW is configuring SQL Server

intra-query parallelism (max degree of parallelism) very conservatively: Only two CPU threads are used per

SQL query (however, you can change the default configuration using RSADMIN parameter

MSS_MAXDOP_QUERY).

For example, a BW query running on a multi provider of four basis cubes, runs eight SQL queries (on four e-

fact and four f-fact tables) in parallel. Each of them is using two CPU threads. Therefore, you leverage at

least 16 CPU threads when running this single BW query.

In addition to the SQL Server data cache, there is another important cache, the OLAP cache. SAP BW

typically caches the results of BW queries on its own. Therefore, many BW queries do not need to access

the database, or at least do not have to retrieve all data from the database. For this kind of queries you can

hardly see any benefit from the column-store, even if the SQL queries are faster by multiple factors.

However, if the BW OLAP cache is not filled, you clearly see a performance improvement.

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

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

Žádné komentáře

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

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