9 Replies Latest reply: Jul 23, 2014 8:55 AM by AmarnathK RSS

    Custom Calculations in ASO

    902361

      Hi,

       

      I have a requirement to calculate level0 accounts and then they need to aggregate to upper levels so I am trying to write a custom calc and it fails to update all the cells (partially updates) when I had to calculate a particular Account for all level 0 memebersin the database. The below are the scripts:

       

      MaxL:

       

      execute calculation on database 'Test'.'Test' with

      local script_file "\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\app\Test\Test\Calc1.csc"

       

      POV "Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({[Party_XXXX]},{Descendants([Currency], [Currency].dimension.Levels(0))}),{Descendants([Ins], [Ins].dimension.Levels(0))}), {[Load]}),{[As of]}), {Descendants([Port], [Port].dimension.Levels(0))}), {Descendants([Source], [Source].dimension.Levels(0))}), {Descendants([System Key], [System Key].dimension.Levels(0))}), {Descendants([Source Type], [Source Type].dimension.Levels(0))}), {Descendants([TimePeriod], [TimePeriod].dimension.Levels(0))})"

       

      SourceRegion "{[Acct1], [Acct2]}";

       

      CalcScript:

       

      [AcctA] := [Acct1] + [Acct2] - ([Acct1], PrevMember[TimePeriod].level)

       

      This works if I run the Calc on smaller chunks of data but when the entire level0 is chosen it only partially updates. I did not see any pattern for this behavior. It is very random. I have checked here in the forum and I see that some others also faced the same issue and they moved onto using execute allocation. I tried to use same but it is running more than 30 mins and I had to kill it eventually. May be that is because of the calc script. I have opened a ticket to Oracle and they are yet to respond so meanwhile I wanted to check with you folks here if anyone have any suggestions.

       

      Below is my Allocation script:

       

      Execute allocation process on database Test.Test with

      POV "Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({[Party_XXXX]},{Descendants([Currency], [Currency].dimension.Levels(0))}),{Descendants([Ins], [Ins].dimension.Levels(0))}), {[Load]}),{[As of]}), {Descendants([Port], [Port].dimension.Levels(0))}), {Descendants([Source], [Source].dimension.Levels(0))}), {Descendants([System Key], [System Key].dimension.Levels(0))}), {Descendants([Source Type], [Source Type].dimension.Levels(0))}), {Descendants([TimePeriod], [TimePeriod].dimension.Levels(0))})"

       

      Amount "([Act_Calc])"  /*created this member and it has the above calc script as member formula*/

       

      Target "([AcctA])"

       

      Range ""

       

      Spread;

       

      Any help is appreciated.

       

      PS: I am posting this while away from work so please ignore any syntax errors.