BW Performance Tuning Issue on Primay Keys of Billing Condition DSO 0SD_O06

Hi All,

I would like to share some of the performance tips which we have recently faced in this project.

Performance issue while loading the sales billing cube XYZ from DSO ABC :

1. We were facing an performance related issue while loading the data from XYZ to  DSO due to complex ABAP calculations written at the transformation level.

We have optimized the code by moving maximum field routines to End Routine so that we can avoid multiple read on the same table.

But one of the major issue was coming due to lookup on the Billing condition cube – 0SD_O06 as it is having data in millions.

“After checking the code, it has been found that the fiscal variant was missing while selection of data from 0SD_O06 cube. As, it is one of the Primary key of the Billing condition DSO, we need to mention it in the WHERE condition. If our sequence of  key field DSO is Fiscal Variant , Billing Number and Billing item  then where condition should also have same sequence as of your Key field. The database (cost-based optimizer) will most likely decide to use the primary index for selecting the data. So, it is always mandatory to use the Primary key in the WHERE clause otherwise it will effect on the loading performance and delay in data update from DSO to further target”.

After adding the primary key in the code, the performance of the code is increased and time taken to load the data from the DSO to Cube has also been improved immensely.

Statistics:

Before Optimization: Just to load  634 records, the system is taking 3H 15M 50s.

DSO Primary Keys-0SD_O06 :

Code before Optimization :

Missing Fiscal variant = K4 in the where condition whiler selecting data from 0SD_O06

Code after Optimization:

added the Fiscal variant = K4 in where condition data from 0SD_O06

After optimization: Loading 950 records is taking 28 mins 9s.

Hope, this doc helps.

Thanks,

Abhimanyu Sharma.