Introduction

When InfoCubes contain million of records any database operation can be time consuming. Partitioning of the fact table is one of the option to improve performance.

While a compression aggregates data this optimization is changing the structure of the data on the database.

What is partitioning exactly?

By partitioning the data the fact table is physically split into multiple tables. There is a couple advantages with partitions.

Instead of reading the whole fact table searching the information, multiple readings  happen in parallel on individual partitions. During selecting the data the database can drop all irrelevant partitions and reduce the amount of data to be read.

In the example below the query selects period 007.2013. In the first case the query runs through all 6 records while in the partitioned table it only queries the 2nd partition.

Simple read and a slow access across fiscper                           Parallel read access across the partitioned characteristic.

Partitioning on application layer (logical partitioning)

This kind of partitioning is an element considered in the architecture and staging of a datamodel. Logical partitioning is defined having a separate InfoCube for each characteristic which are combined under one multiprovider. For example for each year and each actuality of the data one cube is generated. This had to be done manually creating those cubes. In SAP BW 7.3 SPO (semantic partitioning object) got introduced supporting this approach and make it more manageable. It shortens the implementation time drastically.

By logical partitioning querying a multiprovider and the underlying InfoProvider is a parallel process. If RRKMULTIPROVHINT is maintained correctly certain cubes get even excluded during the query as the business object does not contain relevant data. Considering the maintainance effort the number of InfoCubes should be limited unless with BW 7.3 where  SPO can be used.

As a partitioning characteristic best used is a business relevant object like company code or business areas which only change limited.

Partitioning the fact table at the application layer

Partitioning on database layer (physical partitioning)

Physical partitioning is a powerful datawarehouse tool to handle big fact tables. Unfortunately there is some limitations and restrictions which have to be followed. One for instance is partition is only allowed on time characteristic but this process alone improves drastically the performance.

In some cases the system already generates automatically partitions. These are PSA tables and actually the F-fact tables of an InfoCube which is partitioned by the request id. 

As basis for the partitioning a time characteristic is required which defines the size of each partition. It will only be applied to the compressed fact table. Each e.g. fiscalperiod requires it’s own partition for example a fiscalyeat with 12 regular and 4 special periods  requires 18 partitions. (16 for the periods and one period on each end) 

Partitioning of the fact table on database level

Conclusion

Both options can be applied to an InfoCube but the physical partitioning can only be done as long as the InfoCube does not contain data. Partitioning is not just improving performance it also increases complexity and maintenance effort of a datamodel. A logical partitioning is only advisable on bigger datamodels and avoid unnecessary complexity.