This discussion is archived
6 Replies Latest reply: Oct 18, 2011 12:05 AM by sharad sapra RSS

Essbase DataExport optimizing

user11292324 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    it takes around 2 hrs to export stored member data as data is huge.
  • 3. Re: Essbase DataExport optimizing
    AmitGupta Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points