- 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.
It does exactly what the name suggests – it can execute an encrytped MaxL script or a MaxL file.
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!!!!
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
/* 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;
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";
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)
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 🙂
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
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
Pete,
Sorry I forgot to mention that these(MDXCopy) is in 11.1.2.3.500.
I think there is good news in store about Hybrid engine (I'm guessing that there will be a patch which will address Hybrid issues)
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.
You'll have to supply that as a number itself, however the return is a String, not sure how you can use it.
@HspCreateEssbaseServerFile (2981101565,Vision,Plan1);
Hello, I enjoy reading through your article post. I wanted to write a little comment to support you.
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
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?