Any ideas to improve Agg time?

Any ideas to improve Agg time?

Anonymous
Anonymous

October 30th, 2011, 3:54 am #1

I'm aggregating 4 sparse dimensions using the agg command ... Agg (A, B, C, D)
A is the biggest dimension with 30,000 members, B, C and D have 100 to 200 members.

I've the following settings in my calc ...

SET CACHE HIGH;
SET LOCKBLOCK HIGH;
SET CALCPARALLEL 6;
SET AGGMISSG ON;

The agg starts off quick but slows considerably as it progresses. Is there any way to make this faster? I've around 4Gig of Index cache and it's taking over 12 hours to calculate.

Any ideas will be appreciated.

Quote
Share

Tim Faitsch
Tim Faitsch

October 30th, 2011, 11:47 am #2

What are your cache settings -- index, data and calc cache? Is A the last dim in the outline (not counting attributes)? When the calc runs you want to see a single anchoring dimension with multiple bitmaps in the app log (you might need to change your set msg level to see this).
Quote
Share

Anonymous
Anonymous

October 30th, 2011, 1:35 pm #3

Index - 3.5 G
Datafile - 2 G
Datacache 2 G

A is the last dimension. It reaches a stage where it pretty much stops growing in size but the calc is still running for hours. Could it be because AGGMISSG is ON?
Quote
Share

Tim Faitsch
Tim Faitsch

October 30th, 2011, 4:15 pm #4

You can try turning it off. Also, you could try agging them separately to see where the problem is.

What is the calc cache setting in your essbase.cfg file?
Quote
Share

Joined: April 13th, 2011, 5:00 pm

October 30th, 2011, 4:56 pm #5

I would reorder the dimensions listed in the AGG command from smallest Sparse to largest Sparse.

I'm not sure if the AGG command works like the CALC DIM command but if it does, the reordering will calculate your smaller dimensions first.
Quote
Like
Share

Anonymous
Anonymous

October 31st, 2011, 4:53 pm #6

The agg runs with "Calculator Cache With Single Bitmap For: [E]" ... which is one of my smaller dynamic sparse dimension. With the number of dimensions and the members, it'll be hard for me to set the calcultor cache big enough to have one anchoring dimension and multiple bitmaps.
Quote
Share

Joined: April 4th, 2008, 8:06 pm

October 31st, 2011, 5:04 pm #7

A couple of thoughts.

Try adding
SET CALCTASKDIMS 3;

See http://download.oracle.com/docs/cd/E128 ... skdims.htm
http://download.oracle.com/docs/cd/E128 ... rallel.htm

If you are fixing on every dimension try to fix on only the things you have expected to change e.g. current month, current year, ...

Fixing on the world will mean you are probably acting on data that is perfectly fine.

Regards,

John A. Booth
http://www.metavero.com


Quote
Like
Share

Anonymous
Anonymous

October 31st, 2011, 5:23 pm #8

This is the calc running the first time (total agg). I can limit by a FIX for subsequent calculations.
Quote
Share

Joined: April 4th, 2008, 8:06 pm

October 31st, 2011, 5:46 pm #9

And did you review the notes on CALCTASKDIMS?

Regards,

John A. Booth
http://www.metavero.com
Quote
Like
Share

Tim Faitsch
Tim Faitsch

October 31st, 2011, 6:53 pm #10

The agg runs with "Calculator Cache With Single Bitmap For: [E]" ... which is one of my smaller dynamic sparse dimension. With the number of dimensions and the members, it'll be hard for me to set the calcultor cache big enough to have one anchoring dimension and multiple bitmaps.
I will assume you already set cache high to 200mb.

How many levels are in the dims you are aggregating?
Quote
Share


Confirmation of reply: