cancel
Showing results for 
Search instead for 
Did you mean: 

Delete overlapping requests runs for 5+ hours ...This is too long

Former Member
0 Kudos

We are on BW 3.5 and have a process chain that loads 3 years of data from a cube on the APO Server and AOP data from the BW server into a cube on the BW server. Each load is in its own infopackage. This loads happens every week. We want to delete the loads that occurred the week before using the delete overlapping requests. It is set up as follows:

<b>1.</b>Drop index of Cube

<b>2</b>.Load Current Year data into Cube via Infopackage #1. Loads approximately 32 million records. Selections on calweek: 200701 – 200752. Infosource: InfoSource for APO-DP Cube from APO(ZAPODP_I11). Datasource: APO-DP Backup Cube(8ZAPODPC73). Source System: APP Client 200(APPCLNT200). The Data targets tab is set up as follows: ‘Automatic Loading of similar/identical requests from the cube’ wherein Delete Existing Requests Conditions: Infosources are the same. Selections are overlapping.

<b>3</b>.Load Current Year + 1 data into Cube via Infopackage #2. Loads approximately 32 million records…Selections on calweek: 200801 – 200852. Rest is same as above #2.

<b>4.</b>Load Current Year +2 data into Cube via Infopackage #3. Loads approximately 32 million records…Selections on calweek: 200901 – 200952. . Rest is same as above #2.

<b>5</b>.Load AOP data into Cube via Infopackage #4. Loads approximately 135,000 records. Infosource: AOP Plan(8ZAOPC01). Datasource: AOP Plan(8ZAOPC01). Source System: BW Production(BWPCLNT100). The Data targets tab is set up as follows: ‘Automatic Loading of similar/identical requests from the cube’ wherein Delete Existing Requests Conditions: Infosources are the same. Selections are overlapping

<b>6.</b>Create index on cube

<b>7</b>.Delete overlapping requests from cube. The process chain step is set up to ‘Use Delete Selections for Infocubes from the Infopackages’

<b>The issue is that step #7 runs for more than 5 hours.</b> That is way too long. When we manually delete the requests, it takes 5 minutes. How can I fine tune this processing? Thanks in advance!

<b></b>

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

The chain was much faster today (about 2.5 hours), so yes, it was caused by the aggregates in our case.

Perhaps an eye-opener for your case.

Cheers,

Ed

I see that the guy in the previous reply already copied my findings in his reply. He forgot to add "Don't forget to give points" though.

Former Member
0 Kudos

Hi MAry,

Deletion of request takes longer time when the request is filled in Aggregates of the cube. Some time the job even may fail if the request is compressed in the aggregates. so the better solution would be deactivate the aggreagtes and re activate them after deletion of duplicate requests.

regards,

Prasad

Former Member
0 Kudos

Hi

have you looked for OSS note?

Are processes available for deletion? - This might be case.

Thanks

Gaurav

Former Member
0 Kudos

Thanks for the prompt response, Gaurav.

I did look in OSS and did not find anything on long running request deletes.

What do you mean when you ask if there are processes available for deletion? Do you mean dialog processes?

Former Member
0 Kudos

What is your dbms? Does it support partitioning? If it does then deleting requests merely drops the corresponding partitions, which should be very fast.

If it does not support partitioning then you're running SQL delete statements to delete the data, which could take a long time if you're deleting tens of million of records. BTW how long does it take to load the same data?

P.S. Just saw your last point about manually deleting the requests taking only 5 minutes. Did you delete one request at a time or multiple request at once? The previous poster wanted to know whether you had background processes to run the deletion job. i.e. Did the deletion really took 5 hours, or did it wait a few hours to start the job?

Message was edited by:

Rick Chau

Former Member
0 Kudos

Hi,

Yes Mark, I am talking about dialog process only.

Gaurav

Former Member
0 Kudos

Any new insight in this?

I'm facing the same problem where a deletion of an overlapping request (150.000 records) takes 9.000 seconds. Other delteions are also slow. This used to be much faster. Is the deletion related to other processes, like indexes, masterdata references in the cube, parameters in Oracle, etc?

The advise given earlier about partitioning is not an issue for us.

Thanks,

Eduard

PS, we're on BW 3.5, patch level 13, soon to upgrade to patch level 20.

Former Member
0 Kudos

Deletion owuld be faster if its based on Partition...

Hope it helps

Former Member
0 Kudos

I think I have found the cause. It's the aggregate on the cube. With aggregates, deleting a request creates a lot of work dropping indexes and rebuilding the aggregates.

I have now removed the aggregates. Tomorrow I will see if our nightly load has gone back to acceptable runtimes.

Eduard

PS: In retrospect now, our rediculously slow deletion of overlapping requests started when we added an aggregate to this cube. Silly me for finding out only now.