Analytics Software

I have a situation here with HYPERION PLANNING APPLICATION.I have two application old and new .The old has old hiearchy and the new has new hierachy.The only diffrence between the two is 15 new base accounts and some roll up moves due to new account members.An example will be an base member last month is now a parent with children beneath it.

Now the crispy part is ,there is calc script that is pretty huge .But this huge script used to run for 25 minutes untill last month ,but starting this month it is taking 4.5hrs.Which is really insane.!!

my ground work: block size on old was 117210 B and new id 119600B.
i am playing with data file cache and index cache setting by setting at different levels .Currently it is set to data cache is 2gb and index cache 10mb.Also tried setting index cache 3 times index file and datacache to several sizes.But nothing really impacted the performance.
no of existing blocks:610972,potential blocks:261903360,level zero data is ~4.5 GB.

SET UPDATECALC OFF;
SET CALCPARALLEL 3;
SET MSG SUMMARY;
SET LOCKBLOCK HIGH;
SET CACHE HIGH; this is my house keeping section.Create blockoneq,aggmissing, is ON on database.Set createmissingblk is not set.Its off by default.*calc* task dim is set 2.As i have 2 large sparse dimensions.

there are no network issues,essbase server as running perfect,also server has 16 gb ram,10CPU @3GHz.Windows 32 bit 2003server.

I broke the script into several pieces.Found out that JUST calc dim(account) is taking more than 1hr 25 minutes.In the whole script calc dim(account) and it appears twice.So bumping the execution time to to almost 2.8 hrs over the head.

here are some logs

when the essbase calculator hits the first calc dim(account). It calculates the several block for several minutes.I want to know what i am missing during my optimization.

Aggregate #Missing values: [Enabled]
Create Blocks on Equations: [Enabled]

[Fri Aug 10 11:07:52 2012]Local/SRFcst/Forecast/essadmin/Info(1012677)
Calculating in serial

[Fri Aug 10 11:07:52 2012]Local/SRFcst/Forecast/essadmin/Info(1012672)
Calculator Information Message: Executing Block - [Base], [Forecast], [FY12], [UPLOAD], [LOBBR_MM], [Bancorp_Planning_Only]

[Fri Aug 10 11:07:52 2012]Local/SRFcst/Forecast/essadmin/Info(1012672)
Calculator Information Message: Executing Block - [Base], [Forecast], [FY13], [UPLOAD], [LOBBR_MM], [Bancorp_Planning_Only]

[Fri Aug 10 11:07:52 2012]Local/SRFcst/Forecast/essadmin/Info(1012672)
Calculator Information Message: Executing Block - [Base], [Forecast], [FY12], [Version_One], [LOBBR_MM], [Bancorp_Planning_Only]
...
..
FInd the script at
http://www.scribd.com/doc/102714066/Script

database and config settings
http://www.scribd.com/doc/102714783/database-Settings-and-config-file

even though i highlighted Calc dim(account)......let me know if i am side tracking with this issue.


any suggestion is greatly appreciated and not be forgotten!!

Edited by: lakshmi on Aug 12, 2012 9:09 PM

Edited by: lakshmi on Aug 12, 2012 9:10 PM

Edited by: lakshmi on Aug 12, 2012 9:13 PM

Edited by: lakshmi on Aug 13, 2012 6:02 AM

Edited by: lakshmi on Aug 13, 2012 6:03 AM