5 Replies Latest reply on Jun 12, 2018 8:00 AM by Larry's Friday Girl

    Optimizing Aggregation script

    772876

      Hi All,

       

      I am using the below script for the aggregation of Sparse dimensions & its taking 2 Hours to complete. Just wanted to understand how can i optimize the same. I can't find anything which i could alter as AGG command would be faster than Calcdim. Whether @IDESCENDANTS would be even faster than AGG?

       

      SET UPDATECALC OFF;

      SET LOCKBLOCK HIGH;

      SET AGGMISSG ON;

       

      FIX ( Parameters)

      AGG ("Entity","Funding Type","Change Package");

      ENDFIX

       

      Best Regards !!!

        • 1. Re: Optimizing Aggregation script
          USER1211

          For some clarity:.

          • How many members are in each of these dimensions?
          • Do you have alternate hierarchies in these dimensions?
          • How many years are you consolidating?
          • How big is the block size?
          • Was the consolidation always taking a long time or did it just started occurring?(What changed if recent)
          • What version of Hyperion(including patch level) are you on?(e.g Essbase 11.1.2.4.022,HP 11.1.2.4007) or are you on cloud?'
          • What have you done so far in regards to tuning? https://docs.oracle.com/cd/E57185_01/EDBAG/dcaoptcs.html

           

          There are several threads on the forum in regards to optimization, including white papers and many folks(e.g Cameron L) have written blogs on testing and optimization techniques you may want to review also.

          • 2. Re: Optimizing Aggregation script
            772876

            Hi @user1211

             

             

            Thanks for your reply. Below are the responses for the clarification you asked.

             

            How many members are in each of these dimensions?

            Entity: 17286, Funding Type: 12184, Change Package: 110

             

            Do you have alternate hierarchies in these dimensions?

            Yes

             

            How many years are you consolidating?

            5 Years

             

            How big is the block size?

            Block Size (B) : 380760

             

            Was the consolidation always taking a long time or did it just started occurring?(What changed if recent)

            Always

             

            What version of Hyperion(including patch level) are you on?(e.g Essbase 11.1.2.4.022,HP 11.1.2.4007) or are you on cloud?'

            Cloud (PBCS)

             

            What have you done so far in regards to tuning?

            I am planning to alter AGG command with @IDESCENDANTS and then try it

            • 3. Re: Optimizing Aggregation script
              amith.madisetty

              Hi,

               

              1.What are the storage properties you have set for top dimension node ?

              2.How much time it takes for single year ?

              3.We can split the agg by doing 1 dimension at a time , see it improves

               

              ex:

              SET UPDATECALC OFF;

              SET LOCKBLOCK HIGH;

              SET AGGMISSG ON;

              SET CACHE HIGH;

               

              FIX ( Parameters)

              FIX(@Descendants("Funding Type"),@Descendants("Change Package"))

              AGG ("Entity");

              ENDFIX

              FIX(@Descendants("Entity"),@Descendants("Change Package"))

              AGG ("Funding Type");

              ENDFIX

              FIX(@Descendants("Entity"),@Descendants("Funding Type"))

              AGG ("Change Package");

              ENDFIX

              ENDFIX

               

              Cheers!

              • 4. Re: Optimizing Aggregation script
                Adrian Istani

                The quick win you could do assuming you're on the most recent version is:

                 

                1. Optimize for parallelism (CALCPARALLEL). Don't abuse this for user facing calculation

                2. set the CACHE and the LOCKBLOCK to HIGH (you might want to tune your ESSBASE CFG too).

                • 5. Re: Optimizing Aggregation script
                  Larry's Friday Girl

                  My system is PBCS and I tried various techniques recommended.

                   

                  Apart from calcparallel etc recommended by Adrian not one of them made a real difference to my aggregation time, though I know everyone's situation varies.

                   

                  I take it your database is too big to benefit from intelligent calculation?

                   

                  Could you invoke calculations based on updates from forms with correlated hidden parameters to spread the pain?