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.

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.CalcMgrMDXDataCopyCalcMgrMDXDynDataCopy

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

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”).

I didn’t do anything here. That is the entire output. Isn’t that really neat?I had some trouble getting the syntax for CalcMgrMDXDynDataCopyI 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

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!!!!!

Share this:

Like this:

LikeLoading...

Related

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.

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

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.

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!)

– 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.

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 maxl2) 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?