Groovy in PBCS and On-Premises Hyperion Planning – Part I 8

This is going to be a lengthy series. (Or so I think). I’m going to cover new features that was added to two on-premises Groovy functions and will also talk about how those examples can be done in PBCS. I’ll also cover a new future version of PBCS which allows you to write Groovy scripts in Calc Manager.

This is a follow-up to the article that I wrote “Write CDFs in Calc Scripts Using Calculation Manager Groovy Functions”. In that I discussed about two new functions

1. @CalcMgrGroovyString
2. @CalcMgrGroovyNumber

Now these two functions open up a wide (and creative and wild of course) world of possibilities (as opposed to the common belief of the person who commented on the above article 😉 ). We are going to discuss what’s new in 11.1.2.4.008 Calc Manager.

What’s new in 11.1.2.4.008

If you read the earlier article I mentioned, you might’ve noticed the “Setting up” section where a properties file can help you whitelist packages, classes, aliases, and methods. In 008 release what Calc Manager team did was to allow you to perform ducktyping and remove sandboxing using a property file, groovycdf.properties. Now what this does is, you can fully utilize the Groovy power. Well if it quackes and walks like a duck it is a duck, isn’t it?

This property file also goes to udf folder.

You can enable ducktyping and remove sandboxing by specifying two options.

staticcompile=false

sandbox=false

Keep in mind that now Calc Manager assumes that you take responsibility for your actions. Anything that write in Groovy even if that is something that deletes all system specific files; that is just going to do what it is supposed to do.

What else is new in this version!!!!!

Calc Manager now allows you to execute MaxL scripts from Groovy. essmsh is the default variable that you can use in your Groovy script to invoke MaxL.

essmsh.runScript(String maxlcommands)

essmsh.runScript(String privatekey, String maxlcommands)

essmsh.runFile(String maxlfile)

essmsh.runMaxlFile(String[] arguments)

I’m going to use an example of reclassification to introduce the new features. (one using encrypted and another using non-encrypted MaxL commands)

 Reclassification

Reclassification is probably a GLish term and technical folks from the multi-dimensional world is probably not going to relate to it. But what if I ask you to move data from one place to a new place. That is what exactly reclassification does. It just moves data from one GL account to another one. We are going to discuss that in a new light in this article.

So I’m not going to follow the regular method of running a Calc Manager rule multiple times with prompts or even creating an alternate hierarchy and worse create a new dimension (oh I’ve seen that too). This method lets the user take control and run “n” number of reclassification without have to answer anyone (I mean run-prompts 🙂 ).

How is it done on On-Premises Hyperion Planning

As I was mentioning earlier this doesn’t fall in the regular way of running a rule which has prompts and run them over and over.

What I’ve done in order to achieve this is, well I can show you two different ways to do it. I chose entity as my reclassification dimension instead of account.

1. A webform which allows the user to enter the new entity. This form will allow the user to enter the new entity (as a free form text) where they wish to move the data.
2. A webform which allows the user to select a new entity. This uses a smartlist.

Method 1

I had to use the term “Flex” because is that what the client wanted to call the process and I’ll be calling “reclassification” as “flexing” from here onwards. Now what you see in Column A is nothing but all the entities that I’ve in the system. If those entities seem familiar, you guessed it right; that is Sample Planning application – Vision.

Now B1, C1, D1, and E1 are accounts that I created.

• B1 – Destination Entity, is a Text Data Type Account
• C1 – Flex ID, is a Smartlist Type Account (This is done as a tracking mechanism to see who flexed what)
• D1 – Entity Check, is a Smartlist Type Account (This is done to make sure that even if user tries to move data from one member to same member the rule is not executed).
• E1 – Flex Date, is a Date Type Account (This gives you an idea when the flexing happened).

Calc Manager Rule

What on earth are you doing there?

🙂

Here is what is happening.

Line 24 I’m making sure that I’m running the rule only when Destination Entity is filled by the user.

Second check in real world was something different, client wanted to check whether both Entities have same Currency code or not. I was lazy to change Vision application, so I changed that to check whether both entities are same or not. If they are same don’t process anything, if they are not copy data from source to destination.

Line 25 is where I’m checking whether the Entities are different or not. If they are equal, then I populate OrgCheck as “Entities are same”, else I populate the date and also update that the entities are different. This call will also run the Groovy script and copy the data from source to destination.

Groovy Script

In plain text

import groovy.sql.Sql
//textid='12'
println textid
println sourceentmbr

Sql sql=Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE", "smpplnadmin", "smpplnadmin")

String destentmbr

try{
sql.eachRow("select VALUE from HSP_TEXT_CELL_VALUE where TEXT_ID="+textid){
destentmbr = it.VALUE
if(destentmbr.isDouble())
destentmbr = Double.parseDouble(destentmbr).longValue().toString()
}

} finally {
sql.close()

if(sourceentmbr!=destentmbr) {

essmsh.runScript("""
login admin password on localhost;
execute calculation '
SET AGGMISSG ON;
SET UPDATECALC OFF;
SET EMPTYMEMBERSETS ON;

FIX("Plan",
&CurYr,
"Working",
@LEVMBRS("Period",0),
@REMOVE(@DESC("Account"),@RELATIVE("FlexAccounts",0)),
@IDESC("P_TP"))

DATACOPY "$sourceentmbr" TO "$destentmbr";
CLEARDATA "$sourceentmbr";

ENDFIX
'
on Vision.Plan1;
logout;
exit;
""")
}
}
return destentmbr

Line 6, I’m connecting to Oracle database with a user that has access to Planning tables/table. The one which we are looking for in this case is called HSP_TEXT_CELL_VALUE. This is a Planning internal table.

The way Planning free form text works with Essbase is using this table as a mapping mechanism.

As you can see from the image above every unique text is stored with a unique value. Text_ID is what gets stored in Essbase and Planning magically shows you the text in Webforms and Smartview and Hyperion Financial Reporting.

Line 11, Groovy Script is running a select statement using the number stored in Essbase (TEXT_ID) and returning the VALUE.

Line 14 is done to make sure that the script returns a string as you can see from the image of the table if user enter 460 instead of ‘460 (first is treated as a number and second as string) two entries are made and we need the return to be 460 not 460.000000 (460 is the entity member)

Line 22, Groovy is now going to execute a calculation using MaxL
If you look at that calc what I did there was to use DATACOPY against a function.
If I write this in a plain Essbase context what I did was

DATACOPY @CURRMBR(“Entity”) TO @MEMBER(@CalcMgrDBSelect(
"fhe34i5hsdudoi3fhkjhds",
"w3etsJLpCQbdLgV2pd9eF1fLxe71vhedOQcMZ8+gIFgvSaW0a397tr9r3+x2b85P", /* generated using java -jar calcmgrCmdLine.jar -encrypt -key fhe34i5hsdudoi3fhkjhds username */
"h3IPZj4f9e9NITMCZi1IZ2D925/jJ34dqASpwaWkVo/XHAUz9/x7m3sUvrQxsdQ0",
"jdbc:oracle:thin:@localhost:1521:XE", /* JDBC URL */
" select VALUE from HSP_TEXT_CELL_VALUE where TEXT_ID=?", /* Select Statement */
@HspNumtoString("DestEntity”)));

This is not at all possible earlier and is now totally possible 🙂 🙂 🙂

Here is the video of me flexing 😉

Two things which were not possible earlier can be now achieved using this feature.

1. Use functions where it was not allowed earlier, like did with DATACOPY
2. You can also now use custom functions in a FIX statement.

You can also perform this using @CalcMgrDBSelect and run that inside a block, however in that case any data massaging (like the one I’m doing to return only string in line 14) cannot be done.

Note: Keep in mind that @CalcMgrDBSelect or the select in groovy is going to create sessions so fast you might have to increase the number of processes on relational side. I had to increase the processes on my Oracle database to 500

ALTER system SET processes=500 scope=spfile;

I’ll discuss Method II and a cool trick in the next one.