Using templates to create dynamic rules in Calcmanager 11.1.2.4


Hi guys and happy new year!!!

And to start well the new year what’s best then a post?

Today I want to talk about the new version of Calculation manager (11.1.2.4). I know that it is out for a while now but still I think it has some cool features that are not explored.

In all Planning project, sooner or later, we come to a time that we need to create a currency conversion Rule (at least I like to create a custom Rule for performance reasons). Also some companies uses a lot of currencies.

Before continue I need to say that in our case I find out that less code is equal a less performance. What I mean by that is that for the forecast horizon range period for example, instead of use “IF” and test my 15/18 months horizon I triplicate the code using “FIX” and using “SET EMPTYMEMBERSETS ON ;”.

This set command ignores the “FIX” if it returns an empty set. This approach increases the performance a lot, some times more than 8 times (In this currency example, if I ran it at channel level with “IF”, toke 8 hours, with “FIX” takes 1 hours).

Ok that means I rarely use “IF” in my Rules.

Well, you can already imagine the size and row boring and prone error is the Rules if I use only “FIX” right? However, with the “Template” feature in calcmanager and the ability to call any template or rule using a script this nightmare turns in to a dream!

Let us see how it works!

A Currency conversion for forecast applications normally has two parts:

First parts is a period range part.

Second part is the currency conversion itself.

With calcmanager, we can create two template, one for the period and the other for call the currency conversion part.

Then for the Currency conversion calculation, I create a simple core template with just a formula and a script on it:

UDA Loop Template

The “dtp_Quote_UDA”  is a DTP (design time prompt) variable with a function that will insert double quotes in every value that comes from the “dtp_UDA” DTP variable (this will be used to get values from the outside template), this way we can have use just one variable to do two papers, currency name and UDA value. The code is:

@QUOTE([dtp_UDA])

The inside the Currency calculation script we will have:

Currency Script

As we can see inside the script, I used the “dtp_Quote_UDA” as well the “dtp_UDA”. This simplify the amount of parameters I need to pass and the maintenance as well. Let’s think, we need the same information, one with double quotes, for the UDA values and other without quotes, for the Rate name.

With this technic we need to pass just once the value, let’s say BRL, and in the code Calcmanager will replace before the execution in all places, and we’ll have @UDA(Entity,”BRL”) as well HSP_Rate_BRL.

This is awesome because now I have just 8 line of code that will be transformed in any amount of times I want. The best thing is, or everything is right or everything is wrong J

Because calcmanger now we have a layer between the code written and the code generated, and this is pretty cool because opens a huge windows for creativity. You can even generate the entire code dynamically.

Ok, the next step is to loop this template once for each currency we have. For this, I created another template. This one will be used for the Forecast horizon period range as well for loop the currencies.

Period loop template

Again, the code is pretty simple, just  two fixes and one script.

For the “Period FIX” we use two DTP variable to get the value of Year and period from the outside rule ([dtp_Period] and [dtp_Year]).

The product fix is just something related with our architecture and we do not need to bother about it.

Now the “Loop Currency” is a script that will call N number of times our first template. How can we do that with a script?

Basically every time you drag and drop a template inside a rule or to another template behind the graphic design calcmanager generate a command line. This code exists thanks to its API, and you can use it to manipulate and generate almost any kind of code inside calcmanger.

Currency loop template

As we can see, inside the script we have a “Fix” for the USD currency, (that is the only different conversion) and one row for each currency.

Each row is calling a template “%Template(name:=Currency Conversion – 2 – UDA Loop” from an application “application:=”WWOPS””, a plan type, “plantype:=”Pnl””, and is passing two DTS values, one for the UDA and other for the Entity, “dtps:=(“dtp_UDA”:=[[AED]],”dtp_Entity:=[[dtp_Entity]])”.

As you can see, you can pass a DTP variable using the variable itself (dtp_Entity:=[[dtp_Entity]]).

If you want to create this API code and don’t know how to write the right syntax you can just drag your template to a rule/template, set everything and change your view to “Edit Script” or “View Script”.

Edit script

Now we just need to create the rule that will call this template for the three range of periods we have:

Currency rule

Again a simple design with a small amount of components. Here we have our SET commands, a main fix and the three templates, each one calling the previous template for a different period of range.

Period Range

The final result is a Rule with 1213 rows generated from a 8 rows template. This is the magic of calcmanager and templates. You can simplify everything, you can create dynamic aggregations, that will change depending of the application and cube, you can create codes that changes depending of the member that is coming from the forms, everything with small set of code that is reusable anytime we want!

Rule code 1Currency code 2

…….

A dynamic way to build a currency rule in calcmanager. A lot faster to build and a lot easier to maintain, since if a new currency start to be used you just need to copy and paste one line in “Currency Loop” script, change the currency and it’s done.

Build Rules using templates looks more work and some time a little bit complicate but I remember well how much time I expend changing BRs and I can guarantee that this way is much faster and easier to develop and mainly to maintain.

In the end we just create a Rule and two templates that contains just one core calculation, in my case a script calling 47 times this core, some fixes, and that’s all. It was less than 60 rows of written code to generate 1213 rows. Pretty good for me 🙂

Rules ante templates

Hope you guys enjoy and I wish a happy new year for all and you dears ones.

Happy new year!!!! A new year full of surprises!

 

Advertisements

5 Responses to “Using templates to create dynamic rules in Calcmanager 11.1.2.4”

  1. Hi,

    We are trying to generate the script dynamically based on the year selected by user. The requirement is simple if the user selects the year as current year then the period range should be &Currmonth:Dec else it should be Jan:Dec.

    our script has only below code %Template(name:=”Generate_year_T”,application:=”myApp”,plantype:=”myDB”,dtps:=(“dYear”:=[[{vYear}]])).

    We have tried using @COMPARE function in template but it seems to be not working it takes {vYear} in the function instead of the actual year value.

    Can you provide your inputs on this

    • Hi Abhi how are you?

      You can try to use a combination of @CalcMgrCompare and @Name. @Name returns a string value (in case you are compare a RTP vs a member name) and you should use like this: @NAME({RTP_Member}) and @Name(Member).

      The @CalcMgrCompare is a straight forward function with one catch:

      @CalcMgrCompare(text1, text2,ignoreCase)
      com.hyperion.calcmgr.common.cdf.StringFunctions.compare(String,String,boolean)

      The ignoreCase syntax. ignoreCase is a Boolean value as to weather or not case (Upper or Lower) should be considered.

      The thing is, you need to provide @_true or @_false as parameters instead of 0 and 1 (as would be expected….)
      Then in the end you should have something like this:
      @CalcMgrCompare(@Name{vYear},@Name(&Currmonth),@_true) and this will return true or false and you can validate it using 1==1 or 1==2 for True and False, but 1 or 0 would not work.

      I hope this can help you.

      Thanks!

      • Hi, Thanks for the reply.

        I tried this but it is not working, I created a boolean with prompt and passed the value as @CalcMgrCompare({vYear}, “FY17”,@_true) but still it didnt compare the value.

        My question now is does it really calculates the value or simply take whatever is passed to the dtp like the case where {vYear} is passed to the DTP instead of actual value.

        We are using template so as to remove the need of IF statement to check for CurrentYear

      • Well, when you ran the calc he should replace everything with the right value, but if you don’t pass anything I think he’ll pass the name of the variable or its default value.

        Now, about the IF’s, I think you’ll need to use it since the result of this comparison is 1 or 2 (true and false). You need to do something with that!

        Maybe if you try to look for a internal calcmanager command you can do the IF or Case inside the script instead of in “essbase”.

        I’ll try some stuff here, and if you figure out a way to do it, please let me know 😉

        Thanks!

  2. Waiting for your update or incase i figure it out will update

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: