This is really cool and you’ll see why.
I’ll go ahead the describe the scenario, so that you can think about a different way to do this.
A friend of mine has a unique scenario where there is are three Planning applications (there are more).
App A has an Entity dimension, which has divisions
App B has a Division dimension.
App C has a Division dimension.
If a User enters data in App A and depending on the Entity he entered data, the data needs to be transferred to App B/App C and then run a rule on App B/App C
I can transfer data using @XWRITE, however I’m not going that route considering if you have to use BSO ASO mix environment.
I started on this kind of a wild journey to give him a solution. Couple of calls to and from Sree Menon (I’m amusing by his passion towards the product) to discuss some future enhancements and some geeky talk and here we are with a solution (there could be a different one).
I know that many of you now know and have used @CalcMgrExecuteEncryptMaxLFile, however I’m sure that you completed ignored @CalcMgrExecuteMaxLScript, the reason why I’m saying that it is cooler than it’s cousin and the “why?” will be revealed in this post.
Things that I know
- I need to clear data from App B/App C depending on the user selection (Entity)
- Now transfer data from App A to App B/ App C depending on the user selection (Entity)
- Run an agg on App B/App C depending on the user selection (Entity)
My first choice was to go with @CalcMgrExecuteEncryptMaxLFile, and then try to pass the members from Web Form as variables in the calc string.
execute calculation 'SET AGGMISSG ON; FIX($1, all other members) CLEARDATA $2; ENDFIX' on Sample.basic;
However that will not work, MaxL won’t replace anything inside ‘.
This is where @CalcMgrExecuteMaxLScript is powerful.
@CalcMgrExecuteMaxLScript( "414831229,1453902823", @LIST("spool on to 'c:/temp/clearloadagg.log'", /* Spool the results */ "login $key 453027238000129715402045999930 $key 2007249250379029995041098230508348279730 on localhost", /* Login */ "execute calculation ' SET AGGMISSG ON&scolon& FIX ("&$1"&, @RELATIVE(&QRFPer1,0),@RELATIVE(&QRFPer2,0),@RELATIVE(&QRFPer3,0),@RELATIVE(&QRFPer4,0), &QRFYr1,&QRFYr2,&QRFYr3,&QRFYr4, "&$2"&, Units,ASP,"&Gross Margin %"&) CLEARDATA "&$3"&&scolon& ENDFIX' on $4", "logout", /* Run a calculation to clear data, load data, and roll up */ "spool off", "exit", /*Everything after "exit" is a variable, if you want multiple items as $1 use @LIST */ @NAME({Scenario}), @NAME({Version}), @NAME({Department}), "Vision.Plan2") );
Yeah it is sort of ugly(not readable), hey it is powerful (didn’t you hear that the first time). I’m generating a calc script from a calc script 🙂
You might be wondering why those "& and &scolon& are there, that is a trick Sree’s team added to get double quotes and semicolons into MaxL.
Everything after exist will be substituted.
Step 1 is done
Now data transfer, I was so excited that I could use @CalcMgrMDXDataCopy and be done with it, however it cannot happen in this scenario. This function cannot perform substitution of variables, RUNJAVA on other hand can do it. I guess Sree and team has a hack in place for RUNJAVA (for both MDX – copy and export) which does the substitution. I cannot use RUNJAVA as I’ve to check whether the entity belongs to App B or App C and that will be inside a FIX statement.
I’m using DATAEXPORT and then use a load rule to load it. You can use XWRITE if you are transferring the data to another BSO cube.
Now load rule is only used if there is a difference in dimensionality, else you can do a free form load.
The trick is to export the data into different files for different users – well another custom calc manager CDF function 🙂
I can get the current date time stamp and attach that to my file – well yeah if I could get even the milli seconds that would be great, however the function give only seconds. I guess two users using same intersection at same second would be a rarest event.
@CalcMgrExecuteMaxLScript again.
"Units"( currtime = @CalcMgrGetCurrentDateTime(); IF(@IsUda(Entity,"Plan2Cube")) @CalcMgrExecuteMaxLScript( "414831229,1453902823", @LIST("spool on to 'c:/temp/setsubaggexp.log'", /* Spool the results */ "login $key 453027238000129715402045999930 $key 2007249250379029995041098230508348279730 on localhost", /* Login */ "alter application $5 set variable 'dbexport' '"&$6"&'", /*Set the application variable*/ "execute calculation ' FIX ("&$1"&, @RELATIVE(&QRFPer1,0),@RELATIVE(&QRFPer2,0),@RELATIVE(&QRFPer3,0),@RELATIVE(&QRFPer4,0), &QRFYr1,&QRFYr2,&QRFYr3,&QRFYr4, "&$2"&, Units,ASP,"&Gross Margin %"&,"&$3"&) @IDESC("&P_TP"&)&scolon& ENDFIX SET DATAEXPORTOPTIONS { DataExportLevel ALL&scolon& DataExportPrecision 16&scolon& DataExportColFormat ON&scolon& DataExportColHeader Account&scolon& DataExportOverwriteFile ON&scolon& } &scolon& FIX ("&$1"&, @RELATIVE(&QRFPer1,0),@RELATIVE(&QRFPer2,0),@RELATIVE(&QRFPer3,0),@RELATIVE(&QRFPer4,0), &QRFYr1,&QRFYr2,&QRFYr3,&QRFYr4, P_TP, "&$2"&, Units,ASP,"&Gross Margin %"&,"&$3"&) DATAEXPORT "&File"& "& "& &dbexport&scolon& ENDFIX' on $4", /*Run a calculation for roll up and dataexport */ "logout", "spool off", "exit", /* Anything after exit are variables */ @NAME({Scenario}), @NAME({Version}), @NAME({Department}), "Vision.Plan1", "Vision", @Concatenate(@Concatenate(@Concatenate(@Concatenate(@Concatenate("C:\\\\Temp\\\\Plan2_",@NAME({Scenario})),@NAME({Version})),@NAME({Department})),@CalcMgrDoubleToString(currtime)),".txt") ) ); ELSEIF (conditions) <commands> ENDIF )
This is what I’m doing in the above script.
- Set an Essbase variable to currenttime stamp
- Using the Essbase variable set a substitution variables (The sub var will have the Scenario+Version+Department+CurrentTimeStamp)
- Run an aggregation on products (this is all based on Vision app) – this is done because App B and App C don’t have Product dimension
- Export data using the sub var
Did you see the last substitution
@Concatenate(@Concatenate(@Concatenate(@Concatenate(@Concatenate(“C:\\Temp\\Plan2_”,@NAME({Scenario})),@NAME({Version})),@NAME({Department})),@CalcMgrDoubleToString(currtime)),”.txt”))
Isn’t that cool 🙂
I guess this is going to be way to lengthy to fit into one blog post, so we’ll split this into two parts 😉
Wait for the next part where you can see the full script in play along with the MaxL logs.
Hey,
You're not kidding – that's neat as hell!
When I found you couldn't pass variables through using CalcMgrExecuteEncryptMaxLFile – I ended up getting very Rube Goldbergy and passing variables through the maxl file to a batch command to WRITE a maxl statement and run it. Your way is much much better.
And using maxl to write calc commands is very elegant. Can't wait for the next post.
p
Hey Celvin,
I’m not sure if it’s the new website design – but looks like a lot of the images (on this post and some of the others) have gone walkabout? I’m just getting text now.
Thought I’d let you know.
p
Thanks Pete, yeah there are some issues and I’m trying to fix them.
They were not pictures, I’m using Syntaxhighlighter (if someone wants to copy the code, they can do it since it is text) for highlighting and what happened was when I moved from blogger to WordPress all those lines/codes were removed. I’m in the process of restoring them from beginning of time ;). Just reached this post. Another 5 more years to go 🙁
Thanks for updating the most real time scenarios celvin, Can you please help on my request,Currently i am facing the performance issue with my planning batch( taking 14 hours) and in that one of the aggregation script is taking approx 7 hours.i am trying t trouble shot but no luck, requesting you to please provide me your valuable inputs
Regards,
Mohan.
this is the script i am using and causing for the delay.
//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET CACHE HIGH;
SET LOCKBLOCK HIGH;
SET CALCPARALLEL 3;
SET AGGMISSG ON;
SET CALCTASKDIMS 2;
FIX (“HSP_InputValue”,
&CurVer,
&CurBud, “Budget_HQ_Adj”,
&BudYr1,&BudYr2,&BudYr3,
@Relative(“Entity”,0),”LegalEntity_NA”)
FIX((@REMOVE(@LEVMBRS(“Account”,0),@UDA (“Account”, “No Rate”)) OR (@UDA (“Account”,”No Rate”) AND @UDA (“Account”, “B2A_ACC”))),
@Relative(“Other Metrics Input”,0),
@REMOVE(@LEVMBRS(“Currency”,0),”LocalInput”)
)
FIX(“BegBalance”,Jan:Dec)
AGG ( “Source”,”REL_LegalEntity”,”Product”,”REL_PCCode_CO”,”PCCode_CO”);
ENDFIX
ENDFIX
FIX((@REMOVE(@LEVMBRS(“Account”,0),@UDA (“Account”, “No Rate”)) OR (@UDA (“Account”,”No Rate”) AND @UDA (“Account”, “B2A_ACC”))),
@Relative(“Other Metrics Input”,0),”Cost of Sub-debt (%)”, “FCY Shareholders Fund Rate (%)”,
“LocalInput”
)
FIX(“BegBalance”,Jan:Dec)
AGG ( “Source”,”REL_LegalEntity”,”Product”,”REL_PCCode_CO”,”PCCode_CO”);
ENDFIX
ENDFIX
ENDFIX