5 Replies Latest reply: Sep 10, 2012 10:10 AM by Alan Hendrickx RSS

    Fix memory issue - move calcs to script??

      Hi All,

       

      I am working on a series of Straight Table charts which contain a number of regular dimensions, calculated dimensions and expressions.

      The problem is that I am facing some serious performance issues at the moment where the app grinds to a halt. When I try to add any additional calculations I am getting an 'Out of object memory' message.

       

      I would appreciate some guidance as to how to resolve this. My first thought is that I should move these calculations into the script to improve performance - does this sound sensible?

       

      If this is the way to go, then the next challenge that I face is the fact that the various fields that I need to use are on different tables. As I will need to complete my calculations between the 'Load' and 'From' - how do I efficiently bring in fields from other tables for this purpose?

      Is there a better way to handle these calculations other then within Load & From?

      Should I be using a Join (and dropping original tables?), Lookup or other? What are the main pros/cons?

       

      Many thanks,

      Alan

        • Re: Fix memory issue - move calcs to script??
          Daniel Rozental

          You shouldnt be doing any calculated dimensions (that's almost never the best solution).

           

          Post your expressions/calculated dimensions.

            • Re: Fix memory issue - move calcs to script??

              Hi Daniel,

               

              Thanks for the advice. Good to know to avoid calculated dimensions in the future!

              Excuse my ignorance, but why are they such a bad choice?

               

              As requested, the Expressions are listed below:

              count(if(CategoryDesc='Financial' and Open_Clos='Closed', ID, NULL))

              sum(if(CategoryDesc='Financial', PercentComp/100, NULL))

              count(if(CategoryDesc<>'Financial' and Open_Clos='Closed', ID, NULL))

              sum(if(CategoryDesc<>'Financial', PercentComp/100, NULL))

              count(if(CategoryDesc='Financial' and Open_Clos='Closed' and PercentComp<>100, 1-(PercentComp/100), NULL))

              sum(if(CategoryDesc='Financial' and Open_Clos='Closed' and PercentComp<>100, 1-(PercentCompletion/100), NULL))

              count(if(CategoryDesc<>'Financial' and Open_Clos='Closed' and PercentComp<>100, 1-(PercentComp/100), NULL))

                   ** Plus 9 others (not included) with similar configurations **

              count(if(CategoryDesc='Financial' and Open_Clos='Closed' and PercentComp=100, sum(FinancialAttained), NULL))

              count(isnull(PercentComp))

              max(TOTAL <ID> DateOfUpdate)

               

              You will see from the above that the expressions are done in pairings. I am trying to streamline the code to handle both at the same time, so the pariings should look more like this:

              if (BenUpdateExists=1

                  ,sum(if(BenefitCategoryDesc='Financial',1,0)
                  ,sum(if(BenefitCategoryDesc='Financial', 1-(PercentCompletion/100), NULL))
              )

              **Bonus marks if you can see why this is returning only null results!!

               

              I have removed all but one calculated dimension minor IF statement which does not appear to be causing any issues.

               

              Thanks

              Alan

               

               

               

              • Re: Fix memory issue - move calcs to script??
                Robert Hutchings

                I use then (calculated dimensions) at times. They seem to work fine.