Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

SAP® MaxDB - BW Feature Package

 

As of version 7.6.01 Build 04 the BW FeaturePack is available, which improves the performance especially in BW systems.

Moderators: Christiane Hienger

WIKI Space Editor: Thiago Lüttig.

 

 

SAP Notes
  • 1040431 FAQ: SAP MaxDB BW Feature Pack
  • 830468 FAQ: Business Warehouse with SAP MaxDB
  • 896023 Incorrect CREATE INDEX statement in DFACT.SQL

 

 

Detail

 

Hash Join

It has shown that the use of Hash Joins in a larger scale brings bigger performance improvements than originally thought. That is the reason single measures got ported back to the 7.5 version and beyond, for a better use of Hash Joins there too. Even if relatively big tables are hashed, shorter execution times are reached. Our examples led to an average of factor 2.

Available from Releases 7.5 and 7.6; since 7.6.02 the Hash Join may actually be used for every Equal-Join intersection between 2 tables.

back to top

Join Intersection to Fact Table

Through the logical clustering the fact table should have a primary key with the time dimension as its 1st key field. If the optimizer chooses this clustered key field as a join intersection to fact table, then you now have a key range access instead of the former access over a single index (7.5). Tests with our example data showed the join time to fact table halved in pure memory mode. Beside this better join access the reduced number of data pages through compression also has impact.

Available from Release 7.6.01.04

back to top

Moving of whole Dimension Clouds by Creating Execution plans (Optimizer)

In the past some queries stood out where 7.5 chose an unfavorable execution plan. Due to a relatively large number of participating tables the optimizer does not compute all possible plans. In version 7.6 the moving of whole dimension clouds should eliminate this problem with finding strategies. For that cost calculation logic is adjusted now. At the moment too many small dimension clouds are placed in front of the fact table. The resulting cartesian product from this dimension cloud leads to many unnecessary join steps to fact table. The new procedure for moving dimension clouds behind the fact table has been implemented in 7.6.02.

Available from Release 7.6.01.07

back to top

Compression

The fact tables used in a BI-System typically only consist of numeric fields. These again mainly contain the value '0' since this value is set as default. If these numeric values are not stored in the complex numeric database view but stored significantly more compact the required space of the fact tables clearly drops. Referring to the test data at hand the fact tables could be reduced in size by 30% to 60%. Improvements to the data arrangement resulted in space-savings of 30% to 40%. These actions directly lead to a decrease of necessary I/O by an average of factor 2 on fact tables and their indices.

The current version allows compression of tables, which are exclusively based on FIXED and FLOAT columns.

Commands/Table Attributes for Compression

CREATE TABLE ..... PACKED

ALTER TABLE <table name> CLUSTER [(<cluster column list>)][PACKED]

Available from Release 7.6.01.04

back to top

Unicode Column Compression

UCS-2 char type columns may be packed to UTF-8 using ALTER TABLE ... PACKED UNICODE. This means storing the values using less space on disk and in the data cache. During execution values are unfolded to UCS-2. CPU overhead is not significant.

back to top

Clusters

The utilization of performance capabilities from up to date memory systems concerning caching, read-ahead-properties and reading of bigger units is even more important than compression. These features can only then be used for I/O, when data from a single table is placed along a sorting criterion in big, physical successive units - in case of BI it is best to sort by the time dimension. Like this the I/O, which is needed for table scans and range access using the sorting criterion, can happen much quicker. The obtained factor of improvement depends on the storage system and cluster excellence. In our tests the physical I/O reached an acceleration average of factor 10 (with peaks up to factor 20)

To speed up reading operations by accessing a cohesive data pool the following procedures have been implemented within the BW Feature Pack:

  • Changed pages in the data cache are sorted in linear ascending order and are written together.
  • Coherent areas on the volumes are kept free to be able to write big clusters without fragmentation.
  • Sorting: On flushing of the data cache the right and the left logical neighbour of selected pages are searched in the data cache. If these neighbours also need writing they form an ascending sorted coherent chain and are coherently written on a volume.

Tables and Indices which are used for clustering are marked with 'YES' in the system table 'Files' in the column 'CLUSTERED'.

Commands for Table Clustering CREATE TABLE <table_name> (<column_definition>) CLUSTER PRIMARY KEY CREATE TABLE <table_name> (<column_definition>) CLUSTER (<columnName>, ...)

ALTER TABLE <table name> CLUSTER [(<cluster column list>)][PACKED] LOB are stored in a Cluster , when the global parameter CLUSTERED_LOBS is set. Note: The best cluster formation is reached by creating/building a new table.

Available from Release 7.6.01.04

back to top

Prefetching

Prefetching is the parallel import of data pages from disk to the data cache. The data pages are imported into the cache in parallel in blocks of 8 kb of <n> server tasks.

Parameter READAHEAD_TABLE_THRESHOLD activates the prefetch. Depending on availability, the system can use the same number of server tasks for each single-table access as there are data volumes that have been configured.

Prefetching currently works solely for single-table accesses and NOT for joins. It is NOT used during the access to the index. It is NOT used during check data or update statistics.

Available from Release 7.7.07.04

back to top