Many of you know this already that Calc Manager 11.1.2.3.502 patch was released, however what you don’t know and may not be able to find out (well eventually you’ll) is about those Custom Functions in Calc Manager.
There are 60 CDF functions included in this patch (opposed to the 16 in .500 release)!!!! that is a huge number.
It is quite a huge task to talk about all those functions, however I’ll try to cover some of the interesting ones.
Some of the time functions and string to number that I built as a CDF are now there in Calc Manager.
I guess the Calc Manager team is one of best teams that I had a chance to work with. They are ready to hear your feedback, ready to implement those and always look for opportunities to improve the tool.
3 kudos to Sree Menon and Kim Reeve and the Calc Manager team.
The function that I’m going to talk about is an interesting one. I did blog about this earlier, and was not sure whether it’ll be removed from the next release. Calc Manager team was able to see the value and they made it better.
To run the new MDX functions (there are two @CalcMgrMDXDataCopy and @CalcMgrMDXExport), you’ll have to encrypt the username and password using a key. Have a look here to see how it is done.
CalcMgrMDXDataCopy
Now what is so exciting about this function. Well think about updating an ASO reporting cube from a Planning application in real time.
Yes you can do it by implementing custom functions, custom applications and what not, however What about a single function which extracts data and then loads that for you 🙂
You don’t believe me, let’s see it.
That is a rule which uses RUNJAVA command. (I like using it and yes you can use the function which does a syntax check for you ;))
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy
“fhe34i5hsdudoi3fhkjhds” /* key */
“0K44GgAMKPRjHY1SqF6OcK38x7BWUbTzOfT4F28sPRncN0qDqGGE7Oc3NFolAPw4” /*user*/
“KsivsXGrvXKti62/+vuK3b42IkPjNZdntYyrASYV8/4EgOXSw/uWR14jjK8Y4yWF” /* password */
“Vision” /* from application */
“Plan1” /* from database */
“VsionASO” /* to application */
“Vision” /* to database */
“NON EMPTY(crossjoin(crossjoin(crossjoin(crossjoin(crossjoin({DESCENDANTS([YearTotal],[Period].Levels(0))},{[Plan]}),{[FY14]}),{[Working]}),{[000]}),{[P_000]}))” /* MDX that defines the column members */
“NON EMPTY({Children([Capital Expenditures]), Children([7300])})” /* MDX that defines the row members */
“” /* source member mappings, can be empty */
“” /* target member mappings, can be empty */
“No_Country” /* Target POV columns, members from dimensions that do not exist on the source*/
“-1” /* rows per page */
“c:TempCapExpenditure.log”; /* log file , can be empty */
Everything is self explanatory except source/target member mappings and rows per page.
Source/Target member mapping – This can be used when you have different member names let’s say you source has Apr and Target has April then you can add Apr in source and April in target and the function will replace it for you.
It is similar to the report script RENAME function.
Rows per page – If you define -1 it’ll load the entire output, if you says 1000 then it’ll only load 1000 rows.
I can now setup a form which uses this business rule
Now when a planner saves the form, tadaaa your reporting application will be updated in real time.
That’s my ASO pull 🙂
Now issues (oh yeah there are some).
You cannot use Run Time prompts in Planning (if you are running this from Essbase you can use RTPs), MDX is treated as a string and Planning doesn’t replace RTPs if they come as a string.
Since this is done from a Webform, you’ll have to hard code your column and row into the MDX. (Once the issue with RTP is fixed you can use your user variable here. Thanks Christian, that was an awesome hack)
Things to keep in mind
You’ll have to check the log file and see if function executed successfully or not, Planning web and Essbase logs will say that it was successful 🙁
If you are thinking about implementing this do not use NON EMPTY statements in your MDX query, what if the Planner realized that an entry was wrong and decides to clear it. Yes it might take time to run the query without NON EMPTY, however I’ll take correct results over wrong one 🙂
If you are using Function (not RUNJAVA) selection it’ll still show you the option to select member in MDX row and column axis, do not use that option. Also you’ll have to make sure that the strings are enclosed in “s. It is a simple fix which you can do. I know you are smart 🙂
Improvements
Good logging mechanism – Planning/Essbase can look at the execution and tell you that it failed.
I would like to see if we can apply a where/page clause in the functions, so that I’m not pulling those members which can essentially my POV/Page in Planning.
CalcMgrMDXExport
For years Essbase folks were looking for a way to get a clean export using MDX and finally we’ve something from Oracle.
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXExport
“fhe34i5hsdudoi3fhkjhds”/* key */
“0K44GgAMKPRjHY1SqF6OcK38x7BWUbTzOfT4F28sPRncN0qDqGGE7Oc3NFolAPw4” /* user */
“KsivsXGrvXKti62/+vuK3b42IkPjNZdntYyrASYV8/4EgOXSw/uWR14jjK8Y4yWF” /* password */
“C:TempMDXExport.txt” /* export file */
“ASOSamp” /* from application */
“Sample” /* from database */
“Azgard” /* server name */
“{[{rtp_Promo}]}” /* column axis */
“NON EMPTY Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({[Units]},{[Curr Year]}),{DESCENDANTS([MTD],[Time].Levels(0))}),{[Sale]}),{[Cash]}),[Age].Levels(0).members),[Income Level].Levels(0).members),{DESCENDANTS([All Merchandise],[Products].Levels(0))}),{[Online]}),{DESCENDANTS([North East],[Geography].Levels(0))})” /* row axis */
“|” /* seperator */
“0” /* #Missing replacement */
“false” /* use unique name, if you have duplicate names enabled*/
“false” /* use alias name */
“true” /* suppress zeros */
“true”; /* row headers, similar to DataExportDimHeader ON + DataExportColHeader */
Again a self explanatory function.
I really like the option of using unique name and alias(keep in mind that DATAEXPORT cannot export alias)
I can use runtimeprompts here, because this is an Essbase Calc Manager rule and Calc Manager launches this (I told you Calc Manager DEV team is awesome)
Output
Now that is a really neat output and you can change your MDX query to change the position of the dimensions.
Things to keep in mind
The issue with this one is if your query is wrong, if Essbase cannot allocate memory, if it exceeds 2^64 limit (well that is a lot of ifs), the script will still tell you that it finished successfully. It won’t log the output to the file or to the Essbase app log. Only way to find out is where the file is created/updated with new timestamp.
I always use MDX script editor to see whether my MDX query is working or not, so that way you can make sure everything is working. It is using Grid API and I guess will have some performance impact. Well that can be another blog 🙂
If you are using Function (not RUNJAVA) selection, do not use Member selection in MDX query. You’ve to type the MDX query there.
Improvements
A good logging mechanism, so see if the script was really successful or not.
A where/Page clause condition where I can specify my members – this way I can reduce the amount of time spend to write the output. (remember the less to write the faster it’ll be)
These functions are really great (considering the fact that it is version 1). Hope to see those improvements coming in.
Thanks Celvin, I need to play with this (especially the formatted MDX!). Is there no provision for a WHERE clause, or any MDX more complex that a row / column specification? I'm thinking "WITH" etc for custom-defined members.
Tim that is biggy
I can see that the function is issuing a SELECT, "on columns", on rows" and FROM statements.
Grabs everything from column and rows and builds the MDX statement.
We should combine a list and send this to Sree, he is quick in adapting improvements
Celvin, will they be releasing details on the new cdf or updating the ui to call each of the params with their proper syntax (I.e. Param1 is login, param2 is password etc.)? Or will we just need to reference the Essbase .xml file you refer to in another blog? If not what oracle people can be reached out to for feedback?
Thanks for the info Celvin. I'm trying to use the CalcMgrMDXDataCopy. Does the above command needs to be used in a FIX statement or just use it as a calc script and use it on a BSO form. Also I am using java -jar calcmgrCmdLine.jar -encrypt password/username to generate encrypted username and password. What is the /*key*/ ? in the first line how does one generate that?
Please let me know.
The key can be a string of your choice. There is a limit in length. If you use only number must be 22 characters. If you use a combination of numbers and letters it depends on the letters. I personally use a random combination of 22 numbers to avoid the hassle of calculating the correct key length depending on its content.
I've used RUNJAVA so it cannot be under a FIX, if you are using a FIX statement then remember to fix on a single cell.
key can be anything that you can think, I just typed that key. (can be anything)
Dilan, Oracle did release a post with some use cases. https://t.co/ozUZrThWKs
Dilan, Oracle did release a post with some use cases. https://t.co/ozUZrThWKs
Awesome ! But looks the key to encrypt the password/user name needs to be exactly 22 bytes.
Hi Celvin
First of all.. awesome post…very informative..
I am facing issues while using @CalcMgrMDXDataCopy function in version 11.1.2.4,
I am trying to copy data from Planning cube to ASO essbase cube, which resides on different server. Its neither copying data, nor logging anything in logs..
Is it possible to copy data from planning to Essbase ASO cube which is on different server? As there is no parameter in function syntax to pass target server details…
Thanks in advance..
Try MDXDataCopyServer function that should allow you to transfer between servers.
Hi Celvin,
This blog is very helpful, one question are we able to read RTP values from webform? I tried MDXDataCopy but still prompts for RTP when executed to run on save. Looked into @CalcMgrMDXDataCopyRTP but that looks like that doesnt work with run on save with webform.
I can take RTP. Did you try hiding the prompts? Both on form and Calc Manager rule?
I did, for some reason it prompts for member selection each time…I did set to hide prompt in webform and calc manager rule.we are on 11.1.2.4.006 CM and 11.1.2.5.005 planning.