Conditionally trigger a Data transfer and Aggregation on a different cube – Calc Manager CDFs Part I 6

This entry was posted in Awesomeness Calc Manager CDF Planning Tricks on by (updated 2304 days ago)
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
  1. I need to clear data from App B/App C depending on the user selection (Entity)
  2. Now transfer data from App A to App B/ App C depending on the user selection (Entity)
  3. 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 (&quot&$1&quot&,
		@RELATIVE(&QRFPer1,0),@RELATIVE(&QRFPer2,0),@RELATIVE(&QRFPer3,0),@RELATIVE(&QRFPer4,0), 
		&QRFYr1,&QRFYr2,&QRFYr3,&QRFYr4,
		&quot&$2&quot&,
		Units,ASP,&quot&Gross Margin %&quot&)
			CLEARDATA &quot&$3&quot&&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 &quot& 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' '&quot&$6&quot&'", /*Set the application variable*/
			"execute calculation '    
			FIX (&quot&$1&quot&,
				@RELATIVE(&QRFPer1,0),@RELATIVE(&QRFPer2,0),@RELATIVE(&QRFPer3,0),@RELATIVE(&QRFPer4,0),
				&QRFYr1,&QRFYr2,&QRFYr3,&QRFYr4,
				&quot&$2&quot&,
				Units,ASP,&quot&Gross Margin %&quot&,&quot&$3&quot&)
					@IDESC(&quot&P_TP&quot&)&scolon&
			ENDFIX
			SET DATAEXPORTOPTIONS
				{               
				DataExportLevel ALL&scolon&
				DataExportPrecision 16&scolon&
				DataExportColFormat ON&scolon&
				DataExportColHeader Account&scolon&
				DataExportOverwriteFile ON&scolon& 
				}
			&scolon&
			FIX (&quot&$1&quot&,
				@RELATIVE(&QRFPer1,0),@RELATIVE(&QRFPer2,0),@RELATIVE(&QRFPer3,0),@RELATIVE(&QRFPer4,0),            
				&QRFYr1,&QRFYr2,&QRFYr3,&QRFYr4,
				P_TP,
				&quot&$2&quot&,
				Units,ASP,&quot&Gross Margin %&quot&,&quot&$3&quot&)
					DATAEXPORT &quot&File&quot& &quot& &quot& &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.

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

6 thoughts on “Conditionally trigger a Data transfer and Aggregation on a different cube – Calc Manager CDFs Part I”

  • Reply ↓
    Peter Nitschke

    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

  • Reply ↓
    Peter

    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

    • Reply ↓
      Celvin Kattookaran Post author

      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 🙁

  • Reply ↓
    Mohan

    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.

    • Reply ↓
      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