Executing a MaxL Script Using the @CalcMgrExecuteMaxLScript
The syntax for @CalcMgrExecuteMaxLScript is:
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>Example 1: Using @CalcMgrExecuteMaxLScript
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"
Example 2: Using a Runtime Prompt Variable and a MaxL Argument Holder
Assume the script is not fixed but based on a runtime prompt or an argument to MaxL. If you use a runtime prompt within quoted text, or if you use $1 within quoted text in MaxL, Oracle Hyperion Planning does not replace it. In the example below, you use a runtime prompt instead of hard coding it to "Working". You can rewrite the script using a runtime prompt and a MaxL argument holder.
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}
In this example, {WhatVersion} is a runtime prompt variable. Anything after "exit" in this example is considered an argument to the script. The custom-defined function replaces $1, $2..$n in the script based on the number of arguments.
Example 3: Using MaxL=True
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"
The syntax in this example, results in the following 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;
Note: MDX=true is optional. Using MDX=true replaces the variables with MDX syntax in the Crossjoin sections.
Example 4: Using a List of Functions with Double Quotes and Semicolons
Assume you want to execute the following calculate via 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
This script generates the following:
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;
But Oracle Essbase does not interpret the " and the ; correctly in the strings. To work around this, replace all of the " with "& and all of the ; with &scolon& as follows:
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"
;