Conditionally trigger a Data transfer and Aggregation on a different cube – Calc Manager CDFs Part II 2

This entry was posted in Awesomeness Calc Manager CDF Planning Tricks on by (updated 2304 days ago)
If you’ve not read the Part I of this series read that one here.
Let’s recap the steps that we did till now.
Depending on the user selection we set a substitution variable, exported data from the cube that we are on (App A)
Now the rule looks like this.

VAR currtime;  
FIX("BegBalance", "FY09", "No Scenario", "Working", {Department})
  "Units"( currtime = @CalcMgrGetCurrentDateTime();
  IF(@IsUda(Entity,"Plan2Cube"))
    @CalcMgrExecuteMaxLScript(
		"414831229,1453902823",
		@LIST("spool on to 'c:/temp/setsubaggexp.log'",
		"login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
		"alter application $5 set variable 'dbexport' '&quot&$6&quot&'",
		"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",
		"logout",
		"spool off",
		"exit",
		@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"))
		);

This is the from my spool file.
I’m using a subvar as my file name!!! (After writing this I found that Mike Henderson has used entire Calc commands as subvars). All those ugliness in the calc script (the one above) is gone in the logs 🙂
If you look at the file name, it is named according to the user selection.
The one highlighted is the time stamp, if you look at that in EXCEL is is 20150924165756.
Now I got my file, over to Step 2
Again I’m staying in App A and going to trigger a clear in App B/App C depending on the user selection.
Now below given is my clear script.

@CalcMgrExecuteMaxLScript(
         "414831229,1453902823",
         @LIST("spool on to 'c:/temp/clearloadagg.log'",
         "login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
         "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",
		"spool off",
		"exit",
		@NAME({Scenario}),
		@NAME({Version}),
		@NAME({Department}),
		"Vision.Plan2",
		);
Now that the data is cleared I’ve to import data into the App.
Here is the script that does it. I’m using a load rule, so that I can remove Product dimension.

@CalcMgrExecuteMaxLScript(
         "414831229,1453902823",
         @LIST("spool on to 'c:/temp/clearloadagg.log'",
         "login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
         "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",
		"import database $4 data from data_file '$5' using server rules_file 'Plan1LD' on error write to 'C:\Temp\LoadError.log'",
		"logout",
		"spool off",
		"exit",
		@NAME({Scenario}),
		@NAME({Version}),
		@NAME({Department}),
		"Vision.Plan2",
		@Concatenate(@Concatenate(@Concatenate(@Concatenate(@Concatenate("C:\\Temp\\Plan2_",@NAME({Scenario})),@NAME({Version})),@NAME({Department})),@CalcMgrDoubleToString(currtime)),".txt"))
);
Data cleared, data loaded, now it’s the time to aggregate the data in App B/App C all the time we are in App A!!!

@CalcMgrExecuteMaxLScript(
         "414831229,1453902823",
         @LIST("spool on to 'c:/temp/clearloadagg.log'",
         "login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
         "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",
		"import database $4 data from data_file '$5' using server rules_file 'Plan1LD' on error write to 'C:\Temp\LoadError.log'",
		"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&)
					@IANCESTORS(&quot&$3&quot&)&scolon&
			ENDFIX' on $4",
			"logout",
			"spool off",
			"exit",
			@NAME({Scenario}),
			@NAME({Version}),
			@NAME({Department}),
			"Vision.Plan2",
			@Concatenate(@Concatenate(@Concatenate(@Concatenate(@Concatenate("C:\\Temp\\Plan2_",@NAME({Scenario})),@NAME({Version})),@NAME({Department})),@CalcMgrDoubleToString(currtime)),".txt"))
);

Here is the spool file from Step 2.
Now the entire Calc.

VAR currtime;  
FIX("BegBalance",
	"FY09",
	"No Scenario",
	"Working",
	{Department})
		"Units"(
			currtime = @CalcMgrGetCurrentDateTime();
			IF(@IsUda(Entity,"Plan2Cube"))
				@CalcMgrExecuteMaxLScript(
					"414831229,1453902823",
					@LIST("spool on to 'c:/temp/setsubaggexp.log'",
					"login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
					"alter application $5 set variable 'dbexport' '&quot&$6&quot&'",
					"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",
						"logout",
						"spool off",
						"exit",
						@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")
					)
				);
				@CalcMgrExecuteMaxLScript(
					"414831229,1453902823",
					@LIST("spool on to 'c:/temp/clearloadagg.log'",
					"login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
					"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",
						"import database $4 data from data_file '$5' using server rules_file 'Plan1LD' on error write to 'C:\Temp\LoadError.log'",
						"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&)
									@IANCESTORS(&quot&$3&quot&)&scolon&
							ENDFIX' on $4",
						"logout",
						"spool off",
						"exit",
						@NAME({Scenario}),
						@NAME({Version}),
						@NAME({Department}),
						"Vision.Plan2",
						@Concatenate(@Concatenate(@Concatenate(@Concatenate(@Concatenate("C:\\Temp\\Plan2_",@NAME({Scenario})),@NAME({Version})),@NAME({Department})),@CalcMgrDoubleToString(currtime)),".txt")
					)
				);  
			ELSEIF(@IsUda(Entity,"Plan3Cube"))
				@CalcMgrExecuteMaxLScript(
					"414831229,1453902823",
					@LIST("spool on to 'c:/temp/setsubaggexp.log'",
					"login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
					"alter application $5 set variable 'dbexport' '&quot&$6&quot&'",
					"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",
					"logout",
					"spool off",
					"exit",
					@NAME({Scenario}),
					@NAME({Version}),
					@NAME({Department}),
					"Vision.Plan1",
					"Vision",
					@Concatenate(@Concatenate(@Concatenate(@Concatenate(@Concatenate("C:\\Temp\\Plan3_",@NAME({Scenario})),@NAME({Version})),@NAME({Department})),@CalcMgrDoubleToString(currtime)),".txt")
					)
				);
				
				@CalcMgrExecuteMaxLScript(
					"414831229,1453902823",
					@LIST("spool on to 'c:/temp/clearloadagg.log'",
					"login $key 453027238000129715402045999930  $key 2007249250379029995041098230508348279730 on localhost",
					"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",
					"import database $4 data from data_file '$5' using server rules_file 'Plan1LD' on error write to 'C:\Temp\LoadError.log'",
					"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&)
								@IANCESTORS(&quot&$3&quot&)&scolon&
						ENDFIX' on $4",
					"logout",
					"spool off",
					"exit",
					@NAME({Scenario}),
					@NAME({Version}),
					@NAME({Department}), 
					"Vision.Plan3", 
					@Concatenate(@Concatenate(@Concatenate(@Concatenate(@Concatenate("C:\\Temp\\Plan3_",@NAME({Scenario})),@NAME({Version})),@NAME({Department})),@CalcMgrDoubleToString(currtime)),".txt")
					)     
				);  
			ENDIF  
		)  
ENDFIX

See those IF condition that is the one which tells me which cube I should go depending on the user selection.

I think the near future (next patch release of Calc Manager) will (hopefully?)include two new functions @CalcMgrMDXDataCopyRTP, @CalcMgrMDXExportRTP.
It might make it, it might not (so don’t hold me to it)
@CalcMgrMDXDataCopyRTP(key,user,pwd,srcApp,srcDB,dtnApp,dtnDB,colQuery,rowQuery,whereQuery,srcMappings,targetMappings,targetPOVCols,maxRowsPerPage,arguments,loggerName)

Yes that has a where clause
@CalcMgrMDXExportRTP(key,user,pwd,fileName,application,database,server,columnAxisMdx,rowAxisMdx,seperator,missing,useUniqueName,useAliasName,supressZeros,rowHeaders,args)

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.

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

  • Reply ↓
    Anonymous

    Hi Celvin,

    I am trying to call DD_Demo = @CalcMgrGetCurrentDateTime(); from a calculation script however, it gives the below error on checking the syntax:

    Error: 1200324 Error compiling formula for [DD_Demo] (line 8): operator expected after [@CalcMgrGetCurrentDateTime]

    DD_Demo is an account member.
    The same works if I use:
    DD_Demo = @CalcMgrGetCurrentDate();

    I could see that both the functions are registered properly for all applications.

    Many thanks,
    Saurav

    • Reply ↓
      Celvin Kattookaran Post author

      You are the second person to complain about this. Which version? Can you try editing the function and see whether the java method is expecting an integer value. You can also try using it this way @calcmgrgetcurrentdatetime(mbrname);