La sintaxis de @CalcMgrExecuteMaxLScript es:
RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLScriptFunction
<LOG FILE> OPTIONAL ex., "logfile=c:/Temp/maxlrule.log" The path should be accessible from the ESSBASE process
<ASYNCH FLAG> OPTIONAL ex., "true" This means calc engine will not wait for the completion of the maxl script.
"mdx=true"OPTIONAL The variables are replaced with MDX syntax. This applies only to variables within the Crossjoin sections.
"-D" if using encryption
"<PRIVATE KEY>" if using encryption
<SCRIPT LINE>
<SCRIPT LINE>
<SCRIPT LINE>
...
<ALL OTHER PARAMETERS YOU PASS TO ESSMSH> RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLScriptFunction
"-D"
"28567271,1186612387"
"spool on to 'C:\\ALC\\out.log'"
"login $key 893848844082678214004255849650 $key 4647275840619320283077900267208176084380 on localhost"
"alter database 'SampleASO'.'Basic' clear data in region 'Crossjoin(Descendants([TotalPV],Province.Levels(0)),Crossjoin(Descendants([TotalLOB],LOB.Levels(0)),
Crossjoin(Descendants([TotalActivity],Activity.Levels(0)),Crossjoin(Descendants([TotalBrand],Brand.Levels(0)),
Crossjoin(Descendants([BS000],Account.Levels(0)),Crossjoin({[FY14]},Crossjoin({[Working]},Crossjoin({[Apr]},{[Actual]}))))))))' physical"
"logout"
"spool off"
"exit"
Se asume que el script no es fijo, sino que se basa en una petición de datos en tiempo de ejecución o en un argumento de MaxL. Si utiliza una petición de datos en tiempo de ejecución en texto entre comillas o utiliza $1 en texto entre comillas en MaxL, Oracle Hyperion Planning no lo reemplaza. En el siguiente ejemplo, debe utilizar una petición de datos en tiempo de ejecución en lugar de codificarlo como "En proceso". Puede reescribir el script mediante una petición de datos en tiempo de ejecución y un marcador de argumentos de MaxL.
RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLScriptFunction
"-D"
"28567271,1186612387"
"spool on to 'C:\\ALC\\out.log'"
"login $key 893848844082678214004255849650 $key 4647275840619320283077900267208176084380 on localhost"
"alter database 'SampleASO'.'Basic' clear data in region 'Crossjoin(Descendants([TotalPV],Province.Levels(0)),Crossjoin(Descendants([TotalLOB],LOB.Levels(0)),
Crossjoin(Descendants([TotalActivity],Activity.Levels(0)),Crossjoin(Descendants([TotalBrand],Brand.Levels(0)),
Crossjoin(Descendants([BS000],Account.Levels(0)),Crossjoin({[FY14]},Crossjoin({[$1]},Crossjoin({[Apr]},{[Actual]}))))))))' physical"
"logout"
"spool off"
"exit"
{WhatVersion}
En este ejemplo, {WhatVersion} es una variable de petición de datos en tiempo de ejecución. Todo lo que aparezca después de "exit" en este ejemplo se considera un argumento del script. La función definida personalizada reemplaza a $1, $2..$n en el script basado en el número de argumentos.
RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLScriptFunction
......"mdx=true"
......"-D"
......"28567271,1186612387"
......"spool on to '/tmp/Essbase_ClearAndLoadStandardFX.log'"
......"login $key 893848844082678214004255849650 $key 4647275840619320283077900267208176084380 on localhost"
......"alter database glblaso.aso clear data in region ' CrossJoin( {$1} , { ( $2 , [Actual] , $3 , [USD] )} )' physical "
......"import database 'GlblASO'.'ASO' data from server text data_file '../../StdFxExport.txt' using server rules_file 'LdASO' on
......error write to './tmp/EssbaseLoadASOStdFX_errors.log' "
"logout"
"spool off"
"exit"
"LE_83901,LE_83911"
"Jan"
"2014"
La sintaxis de este ejemplo genera el siguiente script:
spool on to '/tmp/Essbase_ClearAndLoadStandardFX.log';
login $key 893848844082678214004255849650 $key 4647275840619320283077900267208176084380 on localhost;
alter database glblaso.aso clear data in region ' CrossJoin([LE_83901], [LE_83911] , { ( [Jan] , [Actual] , [2014] , [USD] )} )' physical ;
import database 'GlblASO'.'ASO' data from server text data_file '../../StdFxExport.txt' using server rules_file 'LdASO' on
error write to './tmp/EssbaseLoadASOStdFX_errors.log' ;
logout;
spool off;
exit;
Nota: MDX=true es opcional. Al utilizar MDX=true, se reemplazan las variables con sintaxis MDX en las secciones de unión cruzada.
Se asume que desea ejecutar el siguiente cálculo a través de CDF:
RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLScriptFunction
"spool on to 'c:/tmp/calc.log'"
"login admin password on localhost"
"execute calculation '
SET CREATEBLOCKONEQ ON;
SET AGGMISSG ON;
FIX (@IDESCENDANTS("BS"),"Plan")
DATACOPY "Working" to "Target";
ENDFIX
FIX ("Plan","Target")
CALC DIM ("Account","Period","Entity","Product");
ENDFIX'
on Vision.Plan1"
"logout"
"spool off"
"exit"
;
OK/INFO - 1012553 - Copying data from [Working] to [Target] with fixed members [Account(1110, 1150, 1100, 1210, 1220, 1260, 1200, 1310, 1332, 1340, 1300,
1410, 1460, 1 471, 1400, 1010, 1510, 1520, 1530, 1540, 1550, 1560, 1565, 1570, 1580, 1500, 1610, 1620, 1630, 1640, 1650, ].
OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
OK/INFO - 1012574 - Datacopy command copied [0] source data blocks to [0] target data blocks.
OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
OK/INFO - 1012714 - Regular member [Sales Calls] depends on dynamic-calc member [Rolling]..
OK/INFO - 1012714 - Regular member [Sales Calls] depends on dynamic-calc member [YearTotal]..
OK/INFO - 1012714 - Regular member [Period] depends on dynamic-calc member [YearTotal]..
OK/INFO - 1012684 - Multiple bitmap mode calculator cache memory usage has a limit of [5] bitmaps..
OK/INFO - 1012668 - Calculating [ Account(All members) Period(All members) Entity(All members) Product(All members)]
with fixed members [Scenario(Plan); Version(Target)].
OK/INFO - 1012677 - Calculating in serial.
OK/INFO - 1012550 - Total Calc Elapsed Time : [0.002] seconds.
OK/INFO - 1013274 - Calculation executed.
MAXL> logout;
User admin is logged out
MaxL Shell completed
El script genera lo siguiente:
essmsh C:\Users\SRMENON\AppData\Local\Temp\2\calc8153268024796650753.mxl
Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.4.000B154)
Copyright (c) 2000, 2014, Oracle and/or its affiliates.
All rights reserved.
MAXL> login admin password on localhost;
""
OK/INFO - 1051034 - Logging in user [admin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.
MAXL> execute calculation '
2> SET CREATEBLOCKONEQ ON;
3> SET AGGMISSG ON;
4> FIX (@IDESCENDANTS("BS"),"Plan")
5> DATACOPY "Working" to "Target";
6> ENDFIX
7>
8> FIX ("Plan","Target")
9> CALC DIM ("Account","Period","Entity","Product");
10> ENDFIX'
11> on Vision.Plan1;
No obstante, Oracle Essbase no interpreta correctamente los signos " y ; de las cadenas. Para solucionarlo, reemplace todas las " con "& y todos los ; con &scolon& como se muestra a continuación:
RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLScriptFunction
"logfile=c:/Temp/maxl.log"
"spool on to 'c:/tmp/calc.log'"
"login admin password on localhost"
"execute calculation '
SET CREATEBLOCKONEQ ON&scolon&
SET AGGMISSG ON&scolon&
FIX (@IDESCENDANTS("&BS"&),"&Plan"&)
DATACOPY "&Working"& to $quot&Target"&&scolon&
ENDFIX
FIX ("&Plan"&,"&Target"&)
CALC DIM ("&Account"&,"&Period"&,"&Entity"&,"&Product"&)&scolon&
ENDFIX'
on Vision.Plan1"
"logout"
"spool off"
"exit"
;