Child pages
  • OT-DBIF-DBPARTITIONS
Skip to end of metadata
Go to start of metadata

Introduction

Partitioning enables tables and indexes to be split into smaller, more manageable components and is especially useful for any large database with high performance requirements. Almost all database providers (except MaxDB) support table partitioning.

For an Infocube, the F- and E-table can be partitioned. ORACLE databases create a partition for each request in the F-table. This is done automatically when the request is written into the cube (during loading) meaning that the number of partitions is growing with time. Regarding the E-table its possible to define partitions for a certain cube by choosing value ranges for time characteristics. F-table partitioning simplifies the deletion of requests (e.g. during cmpression) which is then much faster. The E-table partitioning has been introduced to improve the query runtime: often a query is restricted to a certain (not too large) time period which might allow the system to access only a small fraction of all partitions.

E-table partitioning

You can only partition a dataset using one of the two partitioning criteria ‘calendar month’ (0CALMONTH) or ‘fiscal year/period (0FISCPER). At least one of the two InfoObjects must be contained in the InfoProvider.

If you want to partition an InfoCube using the fiscal year/period (0FISCPER) characteristic, you have to set the fiscal year variant characteristic to constant.

RSA1 -> cube -> Extras -> DB Performance -> Partitioning

Note that you can only change the value range when the InfoProvider does not contain data. If data has already been loaded to the InfoProvider, you have to perform a 'repartitioning' (new feature shipped with BW7x).

For the defined time frame, the system determines the "SIDs" for the selected characteristic and creates the partitions for the time period during activation of the InfoCube. In addition a partition with value "MAXVALUE" is created as "High value". This guarantees that there is a valid partition for all possible values.
If aggregates have been defined for an InfoCube, then the "E-fact table" adopts the partitioning properties of the BasicCube if the partitioning characteristic in the aggregate is not 'aggreagted'.

Queries usually have time related filters. Appropriate conditions for the partition characteristic are derived from the filter conditions for any time characteristic in the query processing. In this case only the data of the affected partitions are read because the optimizer excludes non-affected partitions by the "partition elimination". This can result in considerable performance benefits. Please also review SAP Note 1772036 - Inconsistent Time Dimensions

If you have a look at the F-fact table you will realize that there is an additional column where the SIDs of calmonth or fiscper are stored (e.g. SID_0CALMONTH). In addition there is an index to this column which is called ~900 index.

F-table partitioning

Under Oracle (and also some other data bases) every uncompressed request corresponds to one partition in the F fact table of the corresponding infocube. This partition also brings along a full set of secondary local bitmap index partitions. Hence, there is the potential danger of getting too many data base objetcs which have to be maintained (statistic update index rebuild). Please review the very important SAP Note 590370 !

If e.g. you have a cube with 1000 uncompressed requests and 10 dimensions (every dimension brings with it a local bitmap index) you have 1000 partitions for the table itself and 10 x 1000 index partitions which makes a total of 11.000 database objects. This amount of objects hurts most when updating statistics or dropping/recreating secondary indexes. In situations with a high load frequency you will most certainly run into problems in this area sooner or later.

Its recommended not to have more than 30 partitons, at least if you have more than 100 you should think about compression!
If you would like to check how many partions exist for a certain infocube (or to get a general impression of the situation on the system) you can do the following:

  • Use the report SAP_DROP_EMPTY_FPARTITIONS (works for aggregates as well)
  • Have a look at the infocubes P dimension table (/BIC/D<cubename>P). The number of entries reflect the number of unconmpressed partitions.
  • Use the report RSORAVDV to have a look at the partitioned table on the Oracle database catalog. Use DBA / DBA_PART_TABLES, display TABLE_NAME and PARTITION_COUNT and see, whether there are tables with more than 100 partitions ( PARTITION_COUNT > 100) and eventually restrict TABLE_NAME like '/BI%/F%' to see only the F fact tables.
  • In transaction DB02 (DB02OLD) you can use the function 'detailed analysis' to get all table and index partitions displayed (for a certain fact table)

The F fact table can contain empty or unused partitions. Please have a look at SAP note 430486 where this is discussed in detail. You can use the report SAP_DROP_EMPTY_FPARTITIONS to delete such partitions.

  • Empty partitions are those that no longer contain any data records. They probably result from removing the data from the InfoCube via a selective deletion.
  • Unused partitions might still contain data, however no entry for this request is contained in the packet dimension table of the InfoCube. The data is no longer taken into consideration with reporting (there is always a join between the fact table and P-dimension; if you remove an entry in the package dimension you virtually delete the corresponding partition). The remaining partitions are created if a compression (condenser run) has not been correctly ended.
  • No labels