Data Load Performance

[Former Member]

Hi

We're having trouble with getting large volumes of data into some of our cubes.

1 of our cubes (Listing) takes around 01h45 to load about 20 million records.

Another cube (Vendor) takes around 00h30 to load 36 million records.

Most of the time, in both scenarios, is taken up with SID generation ... is there a way to speed this up?

In the Listing scenario, the load to PSA has been split into about 15 infoPackages. The total time taken to run all of those infopackages (in 3 parallel streams) is about 15 minutes.

In the Vendor scenario, a single infopackage is used to load to the PSA, and takes around 2 hours to load into the PSA

The following picture shows one of the DTP requests for the Listing scenario:

For this example 3:16 for datapackage 8, of which 2:29 were taken up for the SID step.

Not sure why this takes so long, and if there's anything to do to speed it up.

A similar screenshot of the Vendor DTP:

That looks like similar performance to the listing DTP. However, when I look at the later requests in the Vendor DTP, they suddenly go a whole lot quicker towards the end:

I guess my questions are:

1. How do I speed up the SID generation

2. Why does SID generation get quicker when more & more records are processed, and why would that not apply in the Listing scenario when there are multiple PSA requests to load

3. Why would an infopackage take so much longer (36 million / 1.5 hours) when running as single infopackage compared to smaller packages (15 minutes in parallel / 20 million records)  --> This one I can sort of understand, that the multiple smaller packets are better, but it then seems to harm the DTP performance.

Cheers,

Andrew

[siddharth_wagh]

Hi Andrew,

During the transactional data load, each record goes to database and pick new DIM-ID.

Since we have huge amount of data, the performance of the loading will decrease. Because all

the records will go to database table and gets new DIM ID numbers every time.

So in order to rectify this problem, we need to use ‘Buffered Numbers’ rather than the hitting the database every time. Follow below steps:

1)  Go to SE37 & Put the Function Module RSD_CUBE_GET to find the object name of a dimension 

     that is likely to have a high number of data sets.

2)  Press F8 and enter the following in function module settings:

      • I_INFOCUBE = 'Info Cube Name'

      • I_OBJVERS = 'A'

      • I_BYPASS_BUFFER = 'X'

      • And Execute

3) In the result screen:

   The number of dimensions are contained in table 'E_T_DIME'. Double click on it to see the   

   dimensions.

4) Go to Column “NOBJECT”, you will get all the relevant number ranges (for example BID0002145)

5) Use Transaction SNRO to display the number range for a dimension used in BI.

    Go to SNRO t-code -> enter the number range object -> click on the „Change‟ Button.

    Then you will get the Number Range Object Maintenance Screen.

6) Now Choose Edit -> Set-up buffering -> Main memory

    Define the 'No. of numbers in buffer' in Number Range Object Change Screen .Set this value to

    500, for example. The size depends on the expected data quantity in the initial and in future

    (delta) uploads.

7) Test your data load. If not improved, try to increse the size of range buffer and re-test.

Siddharth

[Former Member]

Hi,

3 tips:

1. Increase DTP Setting for batch processing, you can increase it till 9.

2. Check if you can reduce amount of data to be loaded by putting relevant filters in DTP. Sometimes we load more then what is actually required by business.

3. Data Package: Reduce data package and also try splitting your DTP by say for example fiscal year, fiscal period.....when lot of ABAP Code is involved in start routine and end routine, you can play around with data package size and try to find out one suitable to your scenerio. When lot of ABAP Code is involved I have seen data package size 1000 or lesser then it.