Calculation Manager Execute MaxL and MDX Data Copy 8


It’s been a longtime dear reader. My apologies for not updating this blog.

There were many reasons.

  • I bought a house and was busy settling in.
  • I picked up a new hobby – gardening and landscaping (which was kind of obvious). I was/is always fascinated with gardening. When I was a kid I used to pick up the plants to see whether roots are coming or not 🙂
  • I was busy with preparing for KScope – My first KScope and it was a great experience to meet all the people you know from forums, blogs,….. You should sign up for Kscope15.
For all those who thanked me during the conference for this blog (and for those who are reading this) , I would like to thank you instead – you are the reason behind this blog.

I wanted to write about this longback when the feature was introduced and it was just another post (like other 25 of them) in my drafts folder. Now you might be thinking why I’m posting about this all on a sudden. Everything happens for a reason and I’ll talk about that real soon 🙂

CalcMgrExecuteEncryptMaxLFile and CalcMgrExecuteMaxLEnScript was introduced in 11.1.2.3.000, what does that do.

It does exactly what the name suggests – it can execute an encrytped MaxL script or a MaxL file.

Imagine a scenario where you’ve a Planning application and an ASO reporting cube. To move data we have to run a batch which will export the data and then use MaxL scripts to load it into the ASO cube.

Now imagine what such a function can do. You can write a rule in Planning which will export only the data which the user is working (using variables – POV, Pages) and load it into ASO cube. Isn’t that wonderful.

SET DATAEXPORTOPTIONS
  {
  DataExportLevel "LEVEL0";
  DataExportRelationalFile ON;
  DataExportOverwriteFile ON;
  };

FIX({hypVersion},{Product},{Scenario},&QRFYr1,@IDescendants(&QRFPer1),{Entity},"9000","9800","Employee Benefits %")

DATAEXPORT "File" "," "E:\HyperionScripts\Data\HeadCount.txt";

ENDFIX

Yeah I know what you are thinking – What happens if multiple users execute the same script? For now DATAEXPORT won’t allow filename to be parameterized.  However think about the possibilities!!!!


I didn’t use Planning (I love Sample Basic) in my example, however it’ll work the same.

I’ve a new database which is BasicT (this one has more members in Market). I’m extracting “New York” data and uploading it to “India” and here is what my Calc script looks like.

SET DATAEXPORTOPTIONS 
  { 
  DataExportLevel "LEVEL0";
  DataExportRelationalFile ON; 
  DataExportOverwriteFile ON;
  };

FIX ("100-10","New York","Actual","Sales");

	DATAEXPORT "File" "," "E:\HyperionScripts\Data\NewYork.txt";

    "Jan"(
	@CalcMgrExecuteEncryptMaxLFile("111191293,855988891","E:\HyperionScripts\MaxL\LoadData.msh",@List("255128950961305925989033982","485558906683403323216820347593940712", "Azgard", "Sample","BasicT","'E:\HyperionScripts\Data\NewYork.txt'","Ld_Bas"),
"true");
    )

ENDFIX

This is my MaxL statement

/*
Name:    LoadData.msh
Purpose: Load Data 
Created: Jul-09-2014
Author:  Celvin Kattookaran
Usage:   Maxl script launched from Essbase
Run-time prompts:  7
		1 User
		2 Password
		3 Server
		4 Application
		5 Database
		6 Data file
		7 Rule file
Substitution variables: none

Modification History:
 MMM-DD-YYYY - Name - description of modification
*/

/* Spool the results of Load Data process to a log file */
spool on to 'E:\HyperionScripts\Logs\MaxL\Load_Data.log';
set timestamp on;
iferror 'Finish';

/* login with username and password */

login $key $1 $key $2 on $3;
iferror 'Finish';

/* Load Data */

import database $4.$5 data from data_file $6 using server rules_file $7 on error write to 'E:\HyperionScripts\Logs\MaxL\DataLoad.err';

iferror 'Finish';

define label 'Finish';
spool off;
logout;
exit;

Execute the script and there you go, data loaded to the target.

I thought about using RUNJAVA – just to see the syntax, however I will advice you all to use RUNJAVA always. There is reason behind it 🙂

Syntax for RUNJAVA is different than the function and here is how it looks like.

SET DATAEXPORTOPTIONS 
  { 
  DataExportLevel "LEVEL0";
  DataExportRelationalFile ON; 
  DataExportOverwriteFile ON;
  };

FIX ("100-10","New York","Actual","Sales")

	DATAEXPORT "File" "," "E:\HyperionScripts\Data\NewYork.txt";

ENDFIX

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions "true" "-D" "E:\HyperionScripts\MaxL\LoadData.msh" "111191293,855988891" "255128950961305925989033982" "485558906683403323216820347593940712" "Azgard" "Sample" "BasicT" "'E:\HyperionScripts\Data\NewYork.txt'" "Ld_Bas";

As you can see RUNJAVA doesn’t need a FIX statement, and it will not execute the statement for all members. We’ll talk about this in detail when we look at the next undocumented functions.

Now if you ask me what true and false does in the function, the only answer I’ve is – It runs asynchronously when true. I’ve seen that true is fast.

Now let’s talk about two undocumented functions. They are the reason why I’m writing this blog. I was excited to figure out the syntax for it.

CalcMgrMDXDataCopy
CalcMgrMDXDynDataCopy

Oh what do they do –

  • First functions can accept MDX queries and it’ll export the data from a BSO copy and will load it to a target cube (ASO/BSO)
  • Second function – if you are not good at MDX, will write an MDX query for you and it’ll export the data from a BSO copy and will load it to a target cube (ASO/BSO)
Why I was existed about it?

Think about performing a datacopy in ASO – now we’ve to depend upon execute allocation (using share). Yeah I know it works, however think about just writing a MDX query and loading it into a different application or think about a DATAEXPORT like script from ASO.

Yes the function generates a a neatly formatted report using MDX script. I had a feeling that the function is using GridAPI and Sree Menon (PM for Calc Manager)confirmed that they are using GridAPI to export data.

For all the folks who are interested. Sree Menon updated that it was a POC in which DEV team wanted to move data from BSO to ASO. Well we just got lucky isn’t it? 😉

I wrote this calc script for exporting data from Sample Basic and load it to Sample BasicT

FIX("Actual")
Jan
(
@CalcMgrMDXDataCopy( "admin", "p@ssword", false, "Sample", "Basic", "Sample", "BasicT", "SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])" , "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDataCopy.log", null, null, null, India, "Azgard");
 )

ENDFIX

What was it doing, it kept exporting/loading the same data for all the members it process. (I didn’t have a clue – Sree informed me about this). Here is the reason why I asked you to stick with RUNJAVA always.

Following are Sree’s words on the syntax

Since this was just a POC, Source/Target mapping does not work like partitioning. It is just source meber to target member (Apr to April). No functions are supported.
Since the mdx can return large data, for performace, maxRowsPerPage could be set to say 1000 which means as soon as the grid has 1000 rows and update to ASO is done.
At the end remaining rows are also pushed. If the maxRowsPerPage is set to “-1”, then all the data is filled in to one grid.
sourceMemberMappings could be something like @List(“Apr”, “Qtr1”) and targetMemberMappings could be @List(“April”, “Quarter1”).

After following his advice this is how the script looks like.

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy "admin"  "p@ssword"  false  "Sample"  "Basic"  "Sample"  "BasicT"  "SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])"  "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDataCopy.log" "New York" "India" -1 "" "Azgard";

Here is the syntax from the specs @CalcMgrMDXDataCopy(user, password, useToken, sourceApplicaionName, srcDatabaseName, targetApplicationName, targetDatabaseName, mdxQuery, loggingFileName, sourceMemberMappings, targetMemberMappings, maxRowsPerPage,tgtPOVCols,server)

I didn’t use tgtPOVCols here, but if you have different dimensionality and you want to load it to a POV, then you can mention them here. Let’s look at the output now.


Query:SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])
User:admin
useToken:No
Source:Sample/Basic
Target:Sample/BasicT
Signed in User:admin
Grid Size: Rows(14) Cols(6)
			India			
			100-10	100-20	100-30	
Budget	Jan	Sales	640.0	#Missing	#Missing	
Budget	Jan	COGS	260.0	#Missing	#Missing	
Budget	Feb	Sales	610.0	#Missing	#Missing	
Budget	Feb	COGS	240.0	#Missing	#Missing	
Budget	Mar	Sales	640.0	#Missing	#Missing	
Budget	Mar	COGS	250.0	#Missing	#Missing	
Actual	Jan	Sales	678.0	#Missing	#Missing	
Actual	Jan	COGS	271.0	#Missing	#Missing	
Actual	Feb	Sales	645.0	#Missing	#Missing	
Actual	Feb	COGS	258.0	#Missing	#Missing	
Actual	Mar	Sales	675.0	#Missing	#Missing	
Actual	Mar	COGS	270.0	#Missing	#Missing

I didn’t do anything here. That is the entire output. Isn’t that really neat?

I had some trouble getting the syntax for CalcMgrMDXDynDataCopy

I think the definitions are little messed up in the specs.

This is from the specs

@CalcMgrMDXDynDataCopy(user, password, useToken, sourceApplicaionName, srcDatabaseName, targetApplicationName, targetDatabaseName, logFileName, sourceMemberMappings, targetMemberMappings, maxRowsPerPage, slicerAxisQuery, axisQuery, povCols, server)

and this is the one which works

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDynDataCopy "admin"  "p@ssword"  false  "Sample"  "Basic"  "Sample"  "BasicT" "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDynDataCopy.log" "New York" "India" -1 "" "([New York])" "[100].CHILDREN" "[Margin].CHILDREN" "[Qtr1].CHILDREN" "{Budget, Actual}";

So I think this is what the spec should really look like

@CalcMgrMDXDynDataCopy(user, password, useToken, sourceApplicaionName, srcDatabaseName, targetApplicationName, targetDatabaseName, logFileName, sourceMemberMappings, targetMemberMappings, maxRowsPerPage, povCols, slicerAxisQuery, axisQuery);

You don’t need a server for this one.

What will this do, it generates the MDX for you!!!!!

Query:SELECT [100].CHILDREN on 0, [Margin].CHILDREN on 1, [Qtr1].CHILDREN on 2, {Budget, Actual} on 3 FROM Sample.Basic WHERE ([New York])
User:admin
useToken:No
Source:Sample/Basic
Target:Sample/BasicT
Signed in User:admin
Grid Size: Rows(14) Cols(6)


The first line is the MDX generated by the function.

That was a really lengthy post and I hope that Calc Manager team will expand this function and give us an ASO DATAEXPORT and more 🙂


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.

8 thoughts on “Calculation Manager Execute MaxL and MDX Data Copy

  • Anonymous

    Celvin,

    Very timely stuff — I have a big blog post on a bunch of the @CalcMgrExec functions coming out this Monday including full documentation, where to find the parameters for the things, etc., etc., etc. Great minds think alike, right? 😉

    Did Sree mention that these data copy functions were to be deprecated in future? I thought that's what he told me but perhaps I got that wrong given your conversation with him.

    Btw, the reason your @CalcMgrMDXDataCopy ran multiple times is because your FIX statement is selecting more than one block. Select just one and you'll see it run the once. I know this is true with @CalcMgrExecuteEncryptMaxLFile and I can't see why it wouldn't be true with the copy command.

    Last comment on this (and hey, it will be in my blog too) — @CalcMgr* will do syntax checks — RUNJAVA will not and it is more than a little confusing to figure out what and what is not wrong with the statement that way.

    Regards,

    Cameron Lackpour

  • Peter Nitschke

    G'day Celvin (and I suppose Cameron, who is apparently stalking you).

    Can I just confirm that these functions (CalcMgrMDXDataCopy & CalcMgrMDXDynDataCopy) are actually in stock 11.1.2.3?

    I can find the cdf for running the encrypted MAXL file – but attempting to use either of the MDXdatacopy functions fails validation in calc manager with an 'error: the function @calcmgrMDXdatacopy' cannot be found. Similar story in EAS.

    They obviously also fail when run.

    Are you sure it didn't creep in in a planning patch? Alternatively is it only in .500? (though I figure you would have mentioned that)

    Also – sincere thanks for posting this up – definitely some interesting work, and the concept of what is effectively a live ASO database may be a game changer (well, until they actually get the BSO/ASO hybrid database thing working…at which point we'll all have to redesign everything again!)

    Cheers
    Pete

  • Peter Nitschke

    G'day again,

    a quick note and a question/suggestion?

    – It looks like you can export the functions from 11.1.2.3.500 and import them to 11.1.2.3. Haven't done a full gambit of testing yet, but they imported fine.
    – I'm interested in the function @HspCreateEssbaseServerFile – particularly as a means to potentially get around the file contention problem you had in your first example. No matter what I try though, I can't actually actually get it to run!

    The function lists as:
    @HspCreateEssbaseServerFile(Session ID, App Name, Database)
    but it's not accepting the session ID – failing with a error saying it's expecting a number and finding a string.

    Error: 1200354 Error compiling formula for [4010] (line 6): expected type [NUMBER] found [STRING] ([@HspCreateEssbaseServerFile]) in function []

    Anything particular you can think of? To be honest I'm very confused by the requirement to pass a session ID anyway – still not 100% sure how I'm going to pull that out on the fly anyway.

  • Pranav

    HI Calvin,

    Nice blog! I just would like to ask, if i have Replication partition which moves data from BSO to ASO and i have created maxl to move data.

    Now, I want that maxl to trigger from this command @CalcMgrExecuteEncryptMaxLFile

    I have not tried it but is it possible?

    Thanks
    Pranav

  • BillR

    I've had issues with maxl's not handling errors correctly and then hanging the particular esssvr. Had to daisy chain mxl/batch:
    1) CDF to run maxl
    2) Maxl just runs a batch passing params along (with display system version to get error code 0 every time).
    3) Batch then does the work including running the real maxl.

    Did you encounter similar issues with error handling? Better solution?