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.

Bildschirmfoto 2013-10-11 um 20.04.44.jpg

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.

  /wp-content/uploads/2013/10/8116608210_3477f4c872_296860.jpg

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) 

/wp-content/uploads/2013/10/image001_296861.jpg

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.

To report this post you need to login first.

21 Comments

You must be Logged on to comment or reply to a post.

  1. Kamal Mehta

    Hi Martin,

    Thanks for sharing.

    Can i request you to add more lines on logical partitioning using Table RRKMULTIPROVHINT i.e. maintaining this table in conjunction with 0infoprovider in Bex.This would certainly complete all aspects of Logical Partiitoning.

    Regards

    Kamal

    (0) 
  2. Kamal Mehta

    Basically covering both the scenario for logica l partitioning such as :

    1. Maintaining Table RRKMULTIPROVHINT and

    2. Using 0Infoprov as characteristics in the Query definition and then using customer exit code to evaluate the concerned info provider rather than reading all of them.

    Thanks

    (0) 
  3. SVU 123

    Hi Martin,

    Thanks for sharing the knowledge.

    I wanted to know the OPTIMAL way of creating DB partitions for Cube.

    SAP recommends 30 Partitions per cubes as OPTIMAL..whereas we end up creating more than that in any Project..say usually 60-100 from Year 2008 till 2016.

    With the increase in DB partitions ,the Performance will be brought down instead of increasing it…In our case we cant Archive the data ..but we want to do DB partition based on CalMonthYear…

    Any idea you can share on OPTIMAL way…any table or T Code..Apart from the Query Runtime Execution…

    Hoping to get some help from you on this.

    rgds

    SVU

    (0) 
    1. JΓΌrgen Noe

      Hi SVU,

      did you also consider logical partitions? With BW 7.3 you can use SPOs. In BW 7.0x create different cubes for each year or combine two / three years in one cube and partition them ph<ysically. Logical partitioning and physical partitioning in combination really helps to increase query perforamce! So I can say 30 to 50 partitions per cube is good, but more than 60 is definitely bad for the database to handle.

      Regards,

      Jürgen

      (0) 

Leave a Reply