14 July 2014

Calculation Manager, BSO Planning, and ASO Planning combine for an awesome ASO Essbase procedural calculation hack -- Part 1

A bit of a tease

I’m not talking about me.  If anything I am more than a bit too straightforward.  I try not think of all of the own goals I have scored in my Cameron-tells-it-like-it-is way when a bit of discretion would have been better employed.  At least I am learning as I enter my dotage.

Nope, what I’m talking about is this first part of a three part blog post on how to achieve awesomeness in ASO Planning and beyond.  I really wanted to do this all in one shot but it simply became overwhelming in its length and complexity, so I am reluctantly going to break it into three parts with this first one simply laying the ground work.

Here’s how I plan on putting it out in over the next three weeks:
  • Part 1 – You’re reading it; an introduction to the CDFs you likely don’t know about
  • Part 2 – The genius of Joe Watkins
  • Part 3 – Tying it all together across three different Oracle EPM products with of course a glorious hack

What’s the mystery

As you read this, remember what’s in the title:  Calculation Manager, ASO Planning, ASO Essbase procedural calculations, and BSO Planning.  If you came to the Kscope14 session Tim German and I gave on ASO Planning I went a bit into this but there was simply no time to go into detail.  You won’t remember what I am about to show you because it got cut.  And, as I noted, this is just the introduction.  Hopefully that will keep you coming back for the next two installments because what I stumbled into is pretty damn cool.

Here are the clews

Beginning with, I think, Planning 11.1.2.2, standard Essbase (Custom Defined Functions) CDFs are installed with every copy of Essbase.  Surprised?  They’re definitely there in Essbase 11.1.2.3.500 and they actually differ between what’s available to Essbase and what is installed with each Planning application.  Even more surprised?  Oh the things we can find if we but search.  Of course as I am in the “Even a blind pig occasionally finds an acorn” set, I am putting this down to luck.

Two ways to find them

The first (and boring/safe/approved) way to figure out what these CDFs is to be a good little boy (or girl) and use Calculation Manager to insert functions:

Create and insert

Create a rule, insert a script object, and then click on the Insert Function toolbar button.  It’s the very first button – it’s like Oracle wants you to use it or something crazy like that.

Waddayasee?

I hope you see all kinds of functions.  We’re going to be focusing on the Custom Defined Functions (CDFs).

NB – I am doing this in a BSO Planning application.  As we will see in a bit, there are nuances between what’s available to Planning Plan Types and pure Essbase applications.

Here’s the list of functions.  Nothing much new here except the fourth and third from the bottom although for the purposes of this post I will ignore Custom Defined Macros and leave that as an exploratory exercise for you, Gentle Reader.

What’s in the CDFs?

Lots and lots of interesting stuff is what.  Here’s most of what’s available to a Planning application.

Naming standards

I’m not going to cover most of these, but if you look at the above, you will note that there are CDFs that start with @CalcMgr and ones that start with @Hsp.  Telling you that if it starts with @CalcMgr it has to do with Calculation Manager and if it starts with @Hsp it has to do with Planning is not giving away the game.

So what does that mean for Essbase?

The @Hsp functions are only usable in Planning applications.  Don’t believe me?  Create a BSO Essbase Calculation Manager script and go through the same insert functionality.  What do you see?
This makes sense, right?  You’re not in Planning so the CDFs that Oracle wrote to support Planning aren’t there.

There’s more to Calculation Manager than Calculation Manager

Go into good old EAS and create a BSO calc script.  Now look over in the lower left hand side of the script window and select Categorical->User Defined Funcitons – they are the same @CalcMgr functions.  Here’s another hint (possibly a bit of a red herring) to the puzzle – those functions say @CalcMgr but they’re available in plain old calc scripts as well.  
The plot thickens, eh?  :)  What oh what oh what would you do with all of this?

An alternate, and cooler, and faster way to get to all of the above

I know, I know, Cameron has been a good little boy and painted within the lines.  As Donald Fauntleroy Duck would say, Ah, Phooey!

A much faster, and hackier, and more informative, and thus more awesome way to do this is to simply go looking for essfunc.xml on your Essbase server.  Oh, the things you will see.  

Lookatthat

There’s lots of files with that name.  How oh how oh how do they differ?

Two differing properties

Size matters
Discounting the Windows shortcuts, note the spread in size from 7K to 10K.  What do you suppose is in the 7K one?
Nothing but @CalcMgr functions.

And the 9K one?
Nothing but @Hsp functions.

And the 10K one?
A mix of @CalcMgr and @Hsp CDFs.  Curiouser and curiouser.
Location, location, location
Where you live matters, and where essfunc.xml domiciles matters as well.  

The @CalcMgr functions that are available to Plannnig and Essbase live in c:\oracle\middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\java\essfunc.xml.

Planning applications get (it appears) dynamically generated essfunc.xml files that are stored in each Plan Type’s Essbase application folder.  Even more interestingly, it looks like @CalcMgr functions are added to the Planning/Essbase application’s essfunc.xml file on an as-needed basis.  That accounts for the mix of @Hsp (always there in Planning) and @CalcMgr (but only a handful) CDFs in the 10K file.

Why essfunc.xml is better than Calculation Manager’s insert functions

Although you can get the parameters in both Calculation Manager and EAS’ calc script editor, I personally find that many parameters hard to follow and the interface itself is kind of kludgy.  It turns out that the function dialog box in both tools simply reads the examples from essfunc.xml.  
A difference in parameters
Planning
It’s probably a bug, but param1, param2, param3, etc are not particularly helpful when it comes to figuring out what and how the @CalcMgrExecuteEncryptMaxLFile.
Essbase
Ah, that’s a bit better.  Now we can see that the private key, maxlFileName, arguments, and asynchronous parameters are used with @CalcMgrExecuteEncryptMaxLFile although there is still no way to know what the arguments and asynchronous parameters are.

Wouldn’t it simply be easier to read the file?  Why yes it would.

Here’s the @CalcMgrExecuteEncryptMaxLFile function in essfunc.xml

   <function name="@CalcMgrExecuteEncryptMaxLFile" tssec="1378746290" tsmcs="206000" javaSpec="com.hyperion.calcmgr.common.cdf.MaxLFunctions.runMaxLEnFile(String,String,String[],String)">
     <flag value="RUNTIME"/>
     <spec>
       <![CDATA[@CalcMgrExecuteEnMaxLFile(privateKey, maxlFileName, arguments, asynchronous)]]>
     </spec>
     <comment>
       <![CDATA[Calc Manager CDF for running Encrypted MaxL file. RunJava  com.hyperion.calcmgr.common.cdf.MaxLFunctions true -D 2115028571,2505324337 c:/MaxL/maxl.mxl 906388712099924604712352658511 0893542980829559883146306518502837293210. First argument if false, will be an synchromous maxl task]]>
     </comment>
   </function>

Hmm, same ambiguous @CalcMgrExecuteMaxLFile syntax but there’s now something else here – a RUNJAVA command.  At least there’s an example, sort of, although it looks as though both the private key and the encrypted username and password need to passed with this method.

Confusing, isn’t it?

Let’s end the confusion

Whilst I would love to tell you that I figured all of this out, I must confess that I reached out to the Calculation Manager product manager, Sree Menon, and begged for help.  Sree and his associate, Kim Reeve, were beyond helpful.  I promised them that I would share what they taught me.

A brief foray into the ACE program

Btw, lest you think I know Sree because I am an ACE Director, in fact Sree reached out to me way back when I wrote my second post on Calculation Manager.  I don’t think he knew (or cared) about my ACE status.  I am not in any way putting down OTN’s ACE program, I am merely noting that Oracle reaches out to everyone who is an advocate of their products.  That’s a long-winded way of saying if yr. obt. svt. can do it (it being evangelizing, working with Oracle, and maybe becoming an ACE), you most certainly can too and I very much encourage you to do so.  I am on a personal crusade as of this writing to get four people recognized as ACEs because they love Oracle’s products and thus do a ridiculous amount of evangelizing for Oracle.  It will take a long time and a lot of work all round but I’m convinced they will get in – they deserve it, at least in my opinion.

Sorry for the digression, but sometimes I think people think becoming an ACE is an impossible task.  It is a difficult task, and I might note that the first time I was nominated, I was rejected, but now here I am.  Again, if I can do it, you can too.  Don’t be intimidated by the process as making it is incredibly rewarding; probably the most rewarding thing I’ve achieved in my career.  Note that the evangelism works both ways cf. Sree reaching out to me so aim high and you might just make it.

Back to the code

It’s very simple MaxL code.  All that it is doing is logging in to Sample.Basic and running a MDX query.

encryptest.mshs

spool on to "c:\\tempdir\\encryptest.log" ;

login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

/*    The below settings are right out of Developing Essbase Applications    */
alter session set dml_output alias off ;
alter session set dml_output numerical_display fixed_decimal ;
alter session set dml_output precision 4 ;
set column_width 40 ;
set timestamp on ;

SELECT {[Measures].$3}
    ON COLUMNS,
{[Year].Levels(0).Members}
ON ROWS
FROM [Sample].[Basic]
WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

spool off ;

exit ;

Note that $3 command line parameter – yes, you can pass parameters from a calc script to MaxL.  Get your creative juices flowing over that.  I’ll try to demonstrate a few bits of it.

Running the code in EAS via CDF3.csc

It’s actually quite straightforward, although it didn’t seem so when I was figuring it out.

The key syntax notes are as follows:
  • Pass all strings in double quotes
  • Use forward slashes only in file paths
  • Wrap application, server, and any command line parameters in @LIST
  • Those arguments map to command line parameters, so server = $1, database = $2, the first paramter = $3, etc., etc.
  • Wrap any member names in @NAME

Two block issues to be aware of

@CalcMgrExecuteEncryptMaxLFile runs as a member calculation block.  That means that if the FIX that contains the command defines more than one block, it will run that many times.  I only know this because I tried running the command without any FIX and it took (seemingly) forever.  That’s simply because it ran once for each block.  As my copy of Sample.Basic has 376 blocks, the code ran 376 times.  Imagine it in a real database.  Shudder.

Of course if there is no block then @CalcMgrExecuteEncryptMaxLFile won’t run.  Yep, block creation issues raise their ugly head once again.

So what happens when you run it?

Here’s encryptest.log’s output.  Note that $3 resolved to “Sales”.
MAXL> login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

OK/INFO - 1051034 - Logging in user [hypadmin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.

MAXL> alter session set dml_output alias off ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output numerical_display fixed_decimal ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output precision 4 ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].


     essmsh timestamp: Mon Jul 14 16:50:59 2014

MAXL> SELECT {[Measures].Sales}
  2>     ON COLUMNS,
  3> {[Year].Levels(0).Members}
  4> ON ROWS
  5> FROM [Sample].[Basic]
  6> WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

Axis-1                                  (Sales)                                
+---------------------------------------+---------------------------------------
(Jan)                                                                31538.0000
(Feb)                                                                32069.0000
(Mar)                                                                32213.0000
(Apr)                                                                32917.0000
(May)                                                                33674.0000
(Jun)                                                                35088.0000
(Jul)                                                                36134.0000
(Aug)                                                                36008.0000
(Sep)                                                                33073.0000
(Oct)                                                                32828.0000
(Nov)                                                                31971.0000
(Dec)                                                                33342.0000

OK/INFO - 1241150 - MDX Query execution completed.

     essmsh timestamp: Mon Jul 14 16:50:59 2014

So what about RUNJAVA, and why?

There’s an alternate way of running this MaxL file execute, and it is actually there in the essfunc.xml file although again, it is imperfectly documented.

The syntax (and even the properties) are totally different from @CalcMgrExecuteEncryptMaxLFile.

Start off RUNJAVA with the right object name, in this case com.hyperion.calcmgr.common.cdf.MaxLFunctions.

Then, per the above code, you must pass:
  • The asynchronous flag of false
  • A –D to decrypt the MaxL script (just like off of a command line)
  • The public key for decryption
  • The already encrypted username
  • The already encrypted password
  • The server name
  • Do not pass the application and database
  • Any command line prompts
    • The encryrpted username is command line parameter 1 (but you must still pass it this way)
    • The enrypted password is command line parameter 2
    • The server name is command line parameter 3
    • After that you can pass member names (or whatever) as command line parameters 4, 5, 6, etc.
  • Wrap all parameters in double quotes
  • @LIST and @NAME are not required

This is what the MaxL script looks like.  It’s the same except the $3 became a $4.
spool on to "c:\\tempdir\\encryptest.log" ;

login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

/*    The below settings are right out of Developing Essbase Applications    */
alter session set dml_output alias off ;
alter session set dml_output numerical_display fixed_decimal ;
alter session set dml_output precision 4 ;
set column_width 40 ;
set timestamp on ;

SELECT {[Measures].$4}
    ON COLUMNS,
{[Year].Levels(0).Members}
ON ROWS
FROM [Sample].[Basic]
WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

spool off ;

exit ;

Two other things about RUNJAVA

Celvin Kattookaran wrote that he thought RUNJAVA was a better way to go than @CalcMgr*.  I’m not sure I agree with him because of the below.

OTOH, I am sure I love stalking him, just like Glenn Schwartzberg loves stalking me.  The cycle of abuse continues.  Celvin, find someone to pick on.  :)

Syntax checking is gone, gone, gone.  

This will syntax check:

For those of you old enough (sob) to remember taking typing drills in school (my grandmother was a business teacher, taught typing, and that meant when we visited her in the summer we could use a – ooooh, electric typewriter – bored geeks like yr. obt. svt. typed that again and again and again, and Grandma, I miss you), that’s from the man that invented touch typing and isn’t exactly part of Oracle EPM.  

In other words, you had best be sure your syntax is right because Essbase (nor Calculation Manager) will tell you that you’re wrong.

Blocks, what blocks?

RUNJAVA, because it is not a calculation block but in essence a command line, could care less about a block existing, or not.  It’ll run just fine with no blocks, e.g.:
MAXL> login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

OK/INFO - 1051034 - Logging in user [hypadmin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.

MAXL> alter session set dml_output alias off ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output numerical_display fixed_decimal ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output precision 4 ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].


     essmsh timestamp: Mon Jul 14 19:20:02 2014

MAXL> SELECT {[Measures].Sales}
  2>     ON COLUMNS,
  3> {[Year].Levels(0).Members}
  4> ON ROWS
  5> FROM [Sample].[Basic]
  6> WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

Axis-1                                  (Sales)                            
+---------------------------------------+---------------------------------------
(Jan)                                                                  #Missing
(Feb)                                                                  #Missing
(Mar)                                                                  #Missing
(Apr)                                                                  #Missing
(May)                                                                  #Missing
(Jun)                                                                  #Missing
(Jul)                                                                  #Missing
(Aug)                                                                  #Missing
(Sep)                                                                  #Missing
(Oct)                                                                  #Missing
(Nov)                                                                  #Missing
(Dec)                                                                  #Missing

OK/INFO - 1241150 - MDX Query execution completed.

     essmsh timestamp: Mon Jul 14 19:20:02 2014

Again, think about some of the ramifications of the above.  They’re pretty heady, aren’t they?  I’ll come back to them in part three of this series.

That’s all for today

Has your interest been piqued?  We haven’t even gotten to the use case for this and it’s already been a huge amount of work.  No kidding, I spent almost two full man weeks trying to figure all of the above (and a bit more) out for Kscope14.  It was fun and painful, all at the same time.

Have you guessed where I’m taking this?  Send me your comments care of the commend section below.  C’mon, I’m lonely.

Hopefully that was enough for you – that’s over 16 pages and over 2,700 words.  Given that the above is 100% free, surely you’ve gotten value for money.

Be seeing you.

8 comments:

  1. Alright,

    So I'm guessing you're going to do something fancy like:

    - build a flat Planning application, using security groups rather than hierarchies to give users access to dataforms
    - code a fancy push (either using the maxl->dataexport or the direct datapush CDF from bso->aso) to dynamically load data from your data entry forms direct to the ASO cube
    - Do a similar thing for any allocations / calculations etc - passing variables on the fly to use mdx to push the data outputs of the code to ASO as each block calculates
    - This then bypasses the requirement to hold any real data in the BSO app, so you can effectively use it as a calculation shell
    - This allows for fancy bso functions while also dynamically loading the data to an ASO cube where all the reporting sits
    - This elegant workaround therefore invalidates the entire reason for the hybrid bso/aso planning application (as well as the entire exalytics platfrom - why would you need the performance?) therefore deferring a vast number of upgrades and plunging the oracle development teams into fits of despair as they bemoan 'essbase hackers' worldwide
    - Then I guess a shady black car with an oracle number plate will pull up next to you one day on the street, from whence a voice inside speaks 'Mr Lackpour, we would very much like to have a chat with you'

    Something like that?

    Cheers
    Pete

    ps: Either way, can you get on with it?? I want to see what happens!
    pps: If I am correct, and the black car thing happens, don't say I didn't warn you.

    ReplyDelete
  2. Hi,

    Thanks for the blog.

    Do you have any ideas on using the @CalcMgrIntegerToString function?

    This works..

    "Salaries - Other" ="Salaries - Base" * @member(@CONCATENATE("Rate_B", "1" ));

    But if i replace "1" with @CalcMgrIntegerToString("bonus plan") I get the generic "operator expected after [@CalcMgrIntegerToString]" error?

    "Salaries - Other" ="Salaries - Base" * @member(@CONCATENATE("Rate_B", @CalcMgrIntegerToString("bonus plan") ));

    The same is true if i try @CalcMgrIntegerToString("1");

    Any ideas would be great.

    Many Thanks,

    Jo

    ReplyDelete
  3. Hi,

    I left you a comment the other day, just to let you know i worked around my issue by using @HspNumToString.

    Thanks,

    Jo.

    ReplyDelete
  4. Cameron,
    Thanks so much for all the great information; enlightening as usual ! 300X faster than BSO... WOW !
    From what you have seen, is there a compelling reason to use "excecute allocation" over "execute calculation" in the MAXL piece of the currency conversion ?
    Thanks!
    -Bernard

    ReplyDelete
  5. Hi Cameron,

    Thanks for this posting. Have you had any luck using the com.hyperion.calcmgr.common.cdf.MaxLFunctions in a linux Essbase environment (exalytics)? I have successfully used this method to run calc scripts that execute MaxL scripts on a Windows Essbase server, but a comparable calc script on a linux server does not execute the MaxL statement. It's as if it can't find the maxl script file. I've confirmed that the maxl script runs correctly (using a shell script on the linux machine) and I've copied the full path and encryption key directly to the calc script. I'm wondering if perhaps this function isn't supported in a linux environment.

    Any suggestions you may have are greatly appreciated.

    Thanks,
    Wayne

    ReplyDelete
  6. Hi Wayne, any answer about your question? I got the same problem! I use essbase in Linux, and the maxl doesn't work either.
    Any idea?

    ReplyDelete
  7. Hi Leonardo,

    Yes, I did find a resolution to this. If you search Oracle support for version 11.1.2.3 you will find that there is a known bug and fix. Below:

    MaxL Execution From Calc Manager / Calc Script Using RunJava Command Fails in Essbase on UNIX Server (Doc ID 1934024.1)


    Applies to:
    Hyperion Essbase - Version 11.1.2.3.500 and later
    Information in this document applies to any platform.

    Symptoms

    RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions

    The above command does not work on Unix Server whereas the same code works on Windows server.


    Cause

    Missing variable in the opmn.xml file of Essbase server installed in UNIX machines.


    Solution

    In your OPMN.xml file, add the below line:



    It should be as below:






    Restart Essbase service once for the changes to take effect.

    ReplyDelete
  8. Hi,

    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.


    Regards

    ReplyDelete