0

I'm testing loading data to an Essbase Cube via a Load_Buffer. The goal here is to load data through in parallel and then commit the data. Currently, the fastest approach I've found is running the SQL load rule on it's own via MAXL. I've also tried using MAXL "using multiple rules_files" and that took even longer.

The Oracle Essbase Database Administrators Guide mentions you can load data to a buffer for improved speed in parallel. In order to do this, I need to launch 3 MAXL shells simultaneously to a load_buffer.

The problem I'm experiencing is Data C, M, and R complete at different times in :SUB_C. Rule C has 12 million records, Rule M has 10.5M, and Rule R has 15M. I was expecting Data R to complete last, but Rule C persisted and the procedure advanced to committing the change in :SUB_E before all data was loaded.

With the current SQL load rule, execution time is around 333 seconds. With the load_buffer (when it does work), execution time reaches 450 seconds. Each load rule on it's own (C,M, R) takes 100,96, and 220 seconds.

Here is my code:

SET $1-$9 (Parameters 1-9)
CALL    :SUB_A
CALL    :SUB_B
CALL    :SUB_C
CALL    :SUB_D
CALL    :SUB_E
CALL    :SUB_F

GOTO    :EOF

:SUB_A
    ECHO STARTING_REBUILD_%$4%- Started at %time% on %date% > D:\Logs\Rebuild_%$4%_Start.log
EXIT /B

:SUB_B
    REM DIMENSION BUILDS
    ECHO STARTING REBUILD_%$4%
    START "REBUILD %$4%" /W CMD /c D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startMaxl.bat Rebuild_%$4%.msh %$1% %$2% %$3% %$4% %$5% %$6% %$7% %$8% %$9%
    ECHO ENDING REBUILD_%$4%
EXIT /B

:SUB_C
REM DATA LOADS USING LOAD_BUFFER - After a lot of research, these loads tend to end at different times and skip to the next section so parallel won't work
    START "DATA_C %$4%" CMD /c D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startMaxl.bat Rebuild_%$4%_Data_C.msh %$1% %$2% %$3% %$4% %$5% %$6% %$7% %$8% %$9%
    START "DATA_M %$4%" /W CMD /c D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startMaxl.bat Rebuild_%$4%_Data_M.msh %$1% %$2% %$3% %$4% %$5% %$6% %$7% %$8% %$9%
    START "DATA_R %$4%" /W CMD /c D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startMaxl.bat Rebuild_%$4%_Data_R.msh %$1% %$2% %$3% %$4% %$5% %$6% %$7% %$8% %$9%
EXIT /B

:SUB_D
REM - Test
EXIT /B

:SUB_E
    REM COMMIT DATA
    START "COMMIT %$4%" /W CMD /c D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startMaxl.bat Rebuild_%$4%_Data_Z.msh %$1% %$2% %$3% %$4% %$5% %$6% %$7% %$8% %$9%
EXIT /B


:SUB_F
    ECHO ENDING_REBUILD_%$4%- Ending at %time% on %date% > D:\Logs\Rebuild_%$4%_End.log
EXIT /B

:EOF
Mike
  • 133
  • 1
  • 1
  • 13
  • Why are you using `start "" /wait "x.bat" args...`, when `call` should be a better fit. You've asked `C` to start first, immediately followed, _in parallel_, with `F`, then you've asked `R` to start only after having waited for `F` to complete. That means `R` will always start and finish last, and the other two will likely have to compete for first place, depending upon how whatever commands you're running in each batch file prioritise their instructions. Your results do not suprise me. What do you want to happen, which contradicts your actual results? and how can we assist you in fixing it? – Compo Feb 13 '20 at 21:21
  • The shell I'm utilizing will not work if I replace [START " " /W CMD /C] with [ CALL ]. If I use CALL the very last parameter $9 gets interpreted incorrectly and the batch fails. I have tried removing the /W to get them to run concurrently but as soon as one finishes the sub continues to the next step in Sub_D/Sub_E – Mike Feb 13 '20 at 22:37
  • Use multiple START commands and wait for all to complete. https://stackoverflow.com/a/33586872/447901 – lit Feb 13 '20 at 23:48
  • @lit If I read that right, would I literally just need to do this? ``` ( START "" CMD /c startMaxl.bat Data_C.msh %1% etc START "" CMD /c startMaxl.bat Data_M.msh %1% etc START "" CMD /c startMaxl.bat Data_R.msh %1% etc ) | set /P "=" ``` – Mike Feb 14 '20 at 18:30
  • There are many more important factors at play to boost the data load to Essbase, such as: cube defragmentation, block size, data ordering, enabling (and checking that it works when loading) parallel data load. Have you looked at that as well? – Arthur Aug 20 '20 at 06:57

0 Answers0