6 Replies Latest reply: Oct 18, 2011 2:05 AM by sharad sapra RSS

    Essbase DataExport optimizing

    user11292324
      Hi

      We are having Report Scripts which takes around 6 hrs to extract data on System 11. (11.1.1.1)

      We tried to optimize it with calcscript as DATAEXPORT is faster. But no luck. So we split the script into two.

      One to extracts data from stored members and other from Dynamic calc.

      Stored Extracts is fine but Dynamic member extracts is taking long time. Is there any way to optimize further the export.

      below are setting we are using.

      SET LOCKBLOCK HIGH;
      SET CACHE HIGH;

      SET DATAEXPORTOPTIONS
      {
      /* DataExportDecimal 4; */
      DataExportLevel LEVEL0;
      DataExportColFormat ON;
      DataExportColHeader "Period";
      DATAEXPORTOVERWRITEFILE ON;
      DataExportDynamicCalc ON;
      DataExportNonExistingBlocks OFF;

      };


      essbase.cfg setting

      CALCCACHE TRUE
      CALCCACHEHIGH 150000000
      CALCCACHEDEFAULT 75000000
      CALCCACHELOW 25000000

      CALCLOCKBLOCKHIGH 3000
      CALCLOCKBLOCKDEFAULT 2000
      CALCLOCKBLOCKLOW 100

      Thanks
        • 1. Re: Essbase DataExport optimizing
          894132
          This could be a problem with the structure of your data itself, and if so then teher is very little you can do except to first optimize the database itself.

          How long does it take for a simple export of Level 0 data and for ALL data?
          • 2. Re: Essbase DataExport optimizing
            user11292324
            it takes around 2 hrs to export stored member data as data is huge.
            • 3. Re: Essbase DataExport optimizing
              838610
              All you issues are related to exporting dynamic members. When this option is enabled, Essbase needs to calculate dynamic block in memory before exporting. Based on your database configuration and number of dynamic calc members (mostly should be in dense dimensions), essays needs to decompress dynamic block in memory. Unfortunately, there are very few optimizations that you could do to dynamic calc members or dynamic calc memory, which in turn has an upper limit.

              Is there a way to export data without exporting dynamic calc members? That would be the only sure shot way to making this run faster. Just to give you an example: I did the same in once instance where export took more than 3 days and by reducing/removing dynamic calcs brought it down to less than 30 minutes.
              • 4. Re: Essbase DataExport optimizing
                Vasavya Chowdary
                R u using ASo /BSO?

                if ASo use number of stored members lowest to highest or vise versa i dont remember
                which u can get from member properties in wr.t database

                BSo use all sparse at begging and dence at last make sure u keep time at last

                rest .cfg file dont need to do anything..it will help
                • 5. Re: Essbase DataExport optimizing
                  user11292324
                  This is a BSO cube

                  I tried having Period as last in fix but no luck. below is extract of script

                  //ESS_LOCALE English_UnitedStates.US-ASCII@Binary
                  SET LOCKBLOCK HIGH;
                  SET CACHE HIGH;

                  SET DATAEXPORTOPTIONS
                  {
                  /* DataExportDecimal 4; */
                  DataExportLevel LEVEL0;
                  DataExportColFormat ON;
                  DataExportColHeader "Period";
                  DATAEXPORTOVERWRITEFILE ON;
                  DataExportDynamicCalc ON;
                  DataExportNonExistingBlocks OFF;
                  /*,"Number of Other Colleague Changes"

                  @DESCENDANTS ("D_MAIN",0)
                  */

                  };
                            FIX("C&B","Projection","UnderlyingCurrency","CapitalPlanning","Adjustments","Inter_OpCo_Restatement",
                                           "Intra_Marsh_Restatement","Acquisitions","Dispositions","Foreign_Currency_Rate_Change",
                                           "Correct_Prior_Year_Results","Budget_Restatement","Foreign_Currency_Restatement",
                                           "Accounting_Restatement","CAmount","CRate")

                                 FIX("Local","USD")
                                 FIX(@DESCENDANTS ("D_1000000P"))          
                                           FIX("FY10","FY11")
                                                FIX("Rest of Year Forecast")
                                                     FIX("Working")
                                                          FIX("Hsp_InputValue")
                                                               FIX("Number of Other Net New Hire Colleagues")
                  FIX("BegBalance","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" )

                                                                    DATAEXPORT "File" "," "/opt/rept06/hyperion/products/Essbase/EssbaseServer/app/../../../../../../../var/opt/rept06/hyperion/data/backup/MARSHPLN/DATAEXPORT/Marshpln_B.Marsh_Rep01_D.txt" "#MI";


                  and corresponding ENDFIX
                  • 6. Re: Essbase DataExport optimizing
                    sharad sapra
                    what i have noticed sometimes is that when you export the file in column format the time will be consumed more as compared to the default one.
                    since you are only facing the problem with dynamic calc members you can try to work around the dynamic calculator cache

                    go through the link and all the related commands along with dynamic calc cache and see if there is any improvement

                    http://download.oracle.com/docs/cd/E12032_01/doc/epm.921/html_techref/config/dyncalccachemaxsize.htm