PARTITIONING

Info cube partitioning

InfoCube Partitioning

So what is partitioning and why do it?

You use partitioning to divide the data in an InfoCube into multiple, smaller, independent and related segments. This separation improves system performance when performing data analysis InfoProvider, this is because the system knows only to read a particular part of the disk to retrieve the data you are after. In addition it can perform parallel data reads of multiple partitions speeding up the queries execution time. There are always two extra 'catch all' partitions created as well as the ones you explicitly specify in order to store all of the low and high values that fall outside of the normal range, as shown in the example below:

What partitioning options do you have?

Physical Partitioning

Physical partitioning is done at database level and is can only be based on one of the following date based criteria: Calendar Month (0CALMONTH) or Fiscal Year/Period (0FISCPER).

An increase in performance is only seen for the partitioned InfoCube if the time dimension of the InfoCube is consistent. In the standard example shown on the right only the first record is consistent.

To setup partitioning for an InfoCube, choose Extras -> DB Performance -> Partitioning and specify the value range. Where necessary, limit the maximum number of partitions, the SAP recommended optimal maximum number of partitions is 30-40, so consider this when planning the range spilt. All changes are transparent for the user and behind the scenes you still have one logical database table.

In BW 3.5 you had to setup partitions whilst the InfoCube was empty but this constraint has disappeared in BI 7.0 (for all database providers except for DB2). In 7.0 it is not only possible to partition whilst the InfoCube has data but also re-partition any existing groupings. Repartitioning can be useful if you have already loaded data to your InfoCube and you have loaded more data into your InfoCube than you had planned when you partitioned it, you did not choose a long enough period of time for partitioning or some partitions contain no data or little data due to data archiving over a period of time.

You can access repartitioning in the Data Warehousing Workbench using Administration, or in the context menu of your InfoCube.

Logical Partitioning

Logical portioning is done at the data level and is achieved by separating out related data into separate InfoCubes that are joined into a multi-cube. In this case the related groupings are not solely restricted to time only, although by year would be a sensible choice, you could partition by plan and actual data, regions or business area. All queries based on the multi-cube will automatically use parallel sub-queries as required to retrieve the result-set thus improving query performance.

Conclusions

For anyone involved in planning solutions the logical partitioning will already be part of everyday life for comparing Plan vs Actuals data but other InfoCubes that contain a fair amount of data could benefit from being split into smaller cubes by for example year. Not only will this bring performance benefits but also make it easier to archive old unused data.

In BW 3.5 many cubes were partitioned at their point of creation but maintenance was a real issue and many were left with inadequate ranges over time, now this problem has been addressed in BI 7.0 it should be much easier to keep the systems running in a optimal state and well worth investigating.