Slow initial load from DSO to Cube

[Former Member]

Hello experts,

I make a initial load from DSO with about 10 million records to an infocube. I have a selection criteria on 0CALMONTH in the DTP and a dataflow with Transformation/InfoSource/Transformation. I have on startroutine in the first transformation with a LOOP WHERE condition on the SOURCE_PACKAGE.

DTP runs 3 times parallel for writing into the infocube.

The first try I have to stop because it need more than 36 hours. Around 40 to 60 minutes each data package.

Than I splitted the load in yearly parts. First I loades in this mode 4 years. First data package need 40 minutes and the others need 2-4 minutes each.

But than I see that a logic in the startroutine is wrong and I have to reload again. On this load EVERY data package need 40-60 minutes.

Every try we are the only one who runs processes on the system. What could happen that it makes sometimes such differents each data package?

Thanks for help!

Peter

[Former Member]

Hey!

Thank you for help. I had a SELECT SINGLE in my start routine in the LOOP WHERE. Someone told me the WHERE CASE shouldn't happen so often so I expect a SELECT SINGLE should be OK. In fact after checking the WHERE condition I found out that I had just for 2011 data from around 1.3 million records more than 20.000 records hitting the condition.

Don't believe, always check yourself!!!

Now I read FOR ALL ENTIRIES in SOURCE_PACKAGE and one data package needs 2-3 minutes instead of 40-60.

Thanks and best regards,

Peter

[Former Member]

hi Peter,

I think you may have two issue, one is the selection on the dtp. I know made a selection based on 0CALMONTH. If your don't have 0calmonth as primary or secondary index in your DSO, then actually fetching data from the dso itself will take forever for that amount of data in DSO. Looking at the 40min for the first data package actually tells me that the fetching the record is taking too long. You may have issue with start routine too, but I really doubt the first one is the issue.

Please create a secondary index with 0calmonth if it is not in the primary index.

thanks.

Wond

[Former Member]

Peter can you post the code that you have written in the start routine?

Maybe the slow loading time is related to the code written. If possible, the code performance can be improved.

Although its only this load that is running, make a note of the background processes available on the system.

It maybe required to increase these for a faster load.