Pass Planning Calc Manager variable run time prompt values to an Essbase Partition

I had a requirement to pass planning calc manager variable run time prompt values to an Essbase Partition to refresh a partition for a specific scenario and version. We know we can run a MaxL file from a planning business rule using CDF. Can we extend this further and pass on run time prompt values to the MaxL file as well so they can be used in a partition definition? Say for example that we want to refresh a partition for a specific scenario and version using the values selected by the user by running a business rule containing run time prompts (RTP).

In another post, Neha explained how to execute an Essbase Calc script with substitution variables set to accept values passed by planning business rule RTP using the SET RUNTIMESUBVARS command.  The concept is the same however here we are just talking about passing RTP values to a MaxL file. Since we can use substitution variables in the area definitions in a partition, I decided to update substitution variables using values from RTP which I then used in the area definition of a partition.

In this case, I created a business rule with a CDF calling a MaxL file. Sample code is given below. Note that {ArchiveScenario} and {ArchiveVersion} are the variables I created in Calc Manager, value of which I want to pass on to a MaxL.

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions "false" "C:\MaxL\UpdateSubVar_CDF.mxl" {ArchiveScenario} {ArchiveVersion};

In the above code, {ArchiveScenario} becomes the first parameter to be passed and hence is referenced as $1 in the MaxL file and {ArchiveVersion} becomes the second parameter to be passed and is referenced as $2 in the Maxl file. These variables were then used in the MaxL file. An example is shown below.

alter system set variable ArcScenario “$1”;
alter system set variable ArcVersion “$2”;

create or replace replicated partition sourceapp.sourcedb
area '@RELATIVE("Income Statement",0),"Forecast","Working",&FcstYear'  sourcearea1
to targetapp.targetdb  at servername
as admin identified by ‘password’
area '@RELATIVE("Income Statement",0),&ArcScenario, &ArcVersion ,&FcstYear'  targetarea1
mapped targetarea1 (‘Forecast’) to (‘&ArcScenario’)
mapped targetarea1 (‘Working’) to (‘&ArcVersion’)
update allow;

Note that if the RTP value has a space, then the substitution variable value will be set without double quotes enclosing it. In this case, the partition definition will not be valid. Here you may have to do some special handling when passing values from BR to the MaxL when setting the value of the substitution variable.

When the business rule is executed, it prompts the user to assign values to the run time prompts. The values for these prompts are then passed to the MaxL based on the order of the parameters in the CDF and used to set the substitution variables which are in turn used in the partition definition. This is just one example of passing RTP values to the MaxL to be used in a partition. I am sure the same concept can be applied to other requirements as well.


2 thoughts on “Pass Planning Calc Manager variable run time prompt values to an Essbase Partition

  1. That was a really nice trick
    Another tip is to restart the application if the partition uses a SubVar and if the SubVar value changes
    I have seen this even in the latest essbase on-premise

Leave a Comment