cancel
Showing results for 
Search instead for 
Did you mean: 

BW Partitioning

Former Member
0 Kudos

Hello everyone our BW query response time has been gradually slowing down now that we are getting up to 5 years of data. Our BW administrator is looking at implementing partitioning on a few of the large cubes to improve performance. As you already know SAP BW already uses database partitioning on many standard tables. We have not enabled partitioning on our large custom cubes. SAP documentation suggests this is a straightforward change and itu2019s configured inside SAP BW workbench and not in Oracle.

When the BW admin implements partitioning on the standard tables will there be any Oracle performance issues, I should be aware of?

Do I need to do any specific tuning to accommodate these changes?

We are currently running Oracle 9.2.0.8 but are going to be upgrading to 10.2.4 in the New Year.

Any information would be highly appreciated

Thanks for your time and have a great weekend.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Pellegrino,

There is simply only one recommendation for a big datawarehouse running on ORACLE:

GO FOR IT!

It's the divide and conquer approach that makes your warehouse fly...

(besides improving READ performance on queries due to partition pruning, writes can speed up drastically

if you think of DELETES going into Undo space vs. DROPs or TRUNCATEs of partitions)

SAP BW handles a lot behind the scenes over RSA1. Whats important is to choose the appropriate partitioning criteria

for compressed cubes (for uncompressed cubes it's fixed on REQUESTID).

You have to choose either 0FISCPER or 0CALMONTH wich is defined by the usage of your most-used big queries (i.e. weekly , monthly, quarterly) and calculate the number of necessary partitions by giving a time period from / to.

Make sure you get a good rows per partition ratio by keeping record numbers big in each partition and partition numbers small by giving a maximal partition number

i.e.

CALMONTH:

max. partition number = 96 (add 1 for MAXVALUE partition)

from 01.2000 to 12.2007 = 8 * 12 + 1 = 97 partitions (for each month 1 partition)

max. partition number = 24 (add 1 for MAXVALUE partition)

from 01.2000 to 12.2007 = 8 * 3 + 1 = 25 partitions (for each quarter 1 partition)

...

bye

yk

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello,

Table partitioning for large databases like BW systems needs GOOD preparation.

As a starting point you should check the OSS-notes for your database release and BW version.

On the SAP marketplace use following keywords : BW table partitioning.

Reference SAP Note (check also related SAP Notes !) :

Note 385163 - Partitioning on ORACLE since BW 2.0

Success.

Wim