Skip to Content
Author's profile photo Martin Grob

Improve performance – by partitioning the factable in #SAP #BW

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.

Assigned Tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks for sharing your knowledge.

      Regards,

      Sushant

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks for your comments and support!

      Martin

      Author's profile photo Kamal Mehta
      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

      Author's profile photo Kamal Mehta
      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Thanks Kamal for your input.. I'll make the update..

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Thanks for considering.

      Author's profile photo CH Raman
      CH Raman

      One of the major point to consider performance wise and neat explanation.

      Thank you

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks Raman

      Author's profile photo Former Member
      Former Member

      Thanks for Sharing all BW Performance wise Blog  🙂

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      you're welcome glad you like it

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Hi Martin

      Thanks for sharing another blog from you.  Nicely presented.  good one

      Keep going.

      Regards,

      Krishna Chaitanya.

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks glad you like it

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice document martin.... 🙂

      Nicely presented.

      Regards,

      Sm

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks

      Author's profile photo Former Member
      Former Member

      Good one..is it something similler to SPO ?

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      yeah SPO does make use of the same concept on logical partitioning

      thanks

      Author's profile photo Former Member
      Former Member

      Hi Martin,

      thanks for sharing your knowledge and your experiences.

      Regards,

      Michael

      Author's profile photo SVU 123
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi Martin,

      Thanks for sharing the blog. 🙂 .  Nicely presented.  good one.. 🙂

      Regards,

      Lalit

      Author's profile photo Former Member
      Former Member

      Hi Martin,

      Nice Blog, Keep sharing as you always do.

      Regards,

      VIkrant