Oracle ACE Director Celvin Kattookaran explains how to use Groovy in conjunction with Calculation Manager to generate Custom Defined Functions (CDF) to enhance existing Essbase calculation functions, and to generate new ones.

By Celvin Kattookaran

Introduction

Custom defined functions (CDFs) can enhance Essbase calculation functions or be used to generate new functions. CDFs are written in Java, and, in general, Essbase does not provide tools to create them. You can read more about CDFs—how they are created, registered, updated, removed, viewed and copied—here.

However, if you are using Calc Manager version 11.1.2.4.006 you don’t need to follow the lengthy process of registering, updating the user-defined functions (UDF) policy and copying the JAR file (the container that holds your Custom defined function); you also get two functions that can be used to generate CDFs in calc script itself.

Let’s look at how this is done on an on-prem Essbase/Planning server.

Setting up Groovy on Essbase Server

Before you start writing a CDF in calc script, you need to perform a one-time setup exercise.

1. Download a JAR file (groovy-all-<version>.jar) from https://mvnrepository.com/artifact/org.codehaus.groovy/groovy-all to the UDF folder on the Essbase server (<MIDDLEWARE_HOME>/products/Essbase/EssbaseServer/java/udf):

   
         Figure 1

2. By default, many Java/Groovy classes, methods and packages are blacklisted, but you can whitelist them by creating a properties file, as shown below:

   
         Figure 2

3. properties example
   whitelistpackages=java.io,java.net
   whitelistclasses=groovy.sql.Sql,groovy.lang.GroovyClassLoader,java.lang.Integer,java.lang.Double 
   whitelistaliases= 
   whitelistmethods=

Once these steps are completed, restart Essbase services, so that the set up takes effect.

Now that we are done with the setup to create CDFs in calc script itself, let’s get familiar with some concepts.

Getting Familiar with CDFLogger and Groovy Functions

From Calc Manager 11.1.2.4.006 onwards, you’ll get two new functions that support Groovy language in calc scripts.

@CalcMgrGroovyNumber: Use this function if your Groovy script is returning a number

@CalcMgrGroovyString: Use this function if your Groovy script is returning a string.

You need to careful about the return (output) of the Groovy script because Essbase treats numbers and strings differently.

Let’s say that if your script is not returning a value (I’ve included an example for this in the Examples section), you can use the RUNJAVA command:

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF

You can turn on the logging for CDF calc script by using a RUNJAVA command.

CDFLogger

By default, CDFLogging<n>.log goes into <EPM_INSTANCE>\diagnostics\logs\essbase\essbase location.

Changing the path of CDFLogger

You can specify a different path for the logger using the keyword path:

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "path" "C:/Temp";

Append vs Clear

By default, CDFLogger appends to the existing log file. If you want to clear the log file every time you run the script, you can do that using the keyword clear:

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear";

Show system variables

If you want to print some of the system details (e.g., environment variables and Java variables), you can do that using the keyword system:

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "system";

Keep in mind that the “system” keyword works only if you are using the default location (i.e., if you are not using the “path” keyword).

Change logging level

You can specify logging level by using the keyword level:

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINER";

Seven different logging levels are available. INFO is the default logging level used by CDFLogger.

1. SEVERE
2. WARNING
3. INFO
4. CONFIG
5. FINE
6. FINER
7. FINEST

Change the format of logger

You can change the formatting of the log by using the keyword format:

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "format" "%1$tb %1$td, %1$tY %1$tl:%1$tM:%1$tS %1$Tp %4$s: %5$s%n";

Format uses SimpleFormatter in Java:

• 1$ represents a timestamp
• 2$ is the caller (I don’t think this field is logged)
• 3$ is the logger and this case it is CalcCDFLogger
• 4$ is the level (INFO is default)
• 5$ is the message
• 6$ is the throwable associated with the message; if unavailable, it is an empty string
• n is the new line

The format shown above will print the logger as below:

Jul 13, 2016 9:42:58 AM INFO: Binding text=[null]

Jul 13, 2016 9:42:58 AM INFO: Binding MaxL=com.hyperion.calcmgr.common.groovy.cdf.MaxLGroovyShell@3160e069

Jul 13, 2016 9:42:58 AM INFO: Binding logger=java.util.logging.Logger@7ab7b3f9

Jul 13, 2016 9:42:58 AM INFO: Binding len=[null]

Jul 13, 2016 9:42:58 AM FINE: Name:leftgroovy Script: Var Names: [text, len] Values: [New York, 3]

You can switch to UPPERCASE in the fields by using T or S:

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "format" "%1$tb %1$td, %1$tY %1$tl:%1$tM:%1$tS %1$Tp %4$s: %5$S%n";

This will change messages to UPPERCASE:

Jul 13, 2016 9:44:16 AM INFO: BINDING TEXT=[NULL]

Jul 13, 2016 9:44:16 AM INFO: BINDING MAXL=COM.HYPERION.CALCMGR.COMMON.GROOVY.CDF.MAXLGROOVYSHELL@7B99F8E6

Jul 13, 2016 9:44:16 AM INFO: BINDING LOGGER=JAVA.UTIL.LOGGING.LOGGER@7AB7B3F9

Jul 13, 2016 9:44:16 AM INFO: BINDING LEN=[NULL]

Jul 13, 2016 9:44:16 AM FINE: NAME:LEFTGROOVY SCRIPT: VAR NAMES: [TEXT, LEN] VALUES: [NEW YORK, 3]

@CalcMgrGroovyString and @CalcMgrGroovyNumber

As stated, String function can be used for a Groovy script that returns a string and Number function can be used for a Groovy script that returns a number.

The syntax for the GroovyString function is as follows:

@CalcMgrGroovyString(name,script,varNames,values)

@CalcMgrGroovyNumber(name,script,varNames,values)

* One of these should be present

As you can see from the syntax, you can pass either a script or a name of the script. I think I can read your mind now: “I get what the script is, but what is the name?”

I'll explain the name field in detail.

As we all know, Essbase calculations are done by blocks; if your FIX statement has more than one block (which they usually do), the command gets executed for each block.

For a Groovy script to execute it must first be compiled. If you are going to run a multiple-block calculation and are using an in-line Groovy script (supplying the script field in the command), the script is going to be compiled for each block.

Here lies the answer to the name field: compile a Groovy script first with a name and then use that compiled script in your calcs.

For compiling the script you need to use a RUNJAVA command. Let’s look at the syntax of RUNJAVA Groovy CDF:

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF

“compile” or “run”,

“-file” “file name” or “ name the script”,

“comma separated variable names”,

“comma separated variable values”; /* values are not needed if you are just compiling */

If you use the file name, it will be used as the name of the script.

Now let’s look at some sample code:

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"compile" /* run or compile */
"leftgroovy" /* name of the script, which will be used in @CalcMgrGroovyString */
"String left(String text, int len)
{
 if(text?.length() <=len){
 return text
 } else if (len < 0){
 return null
 } else {
 return text[0..len-1] 
 } 
} /* end of the groovy class */
left(text,Integer.valueOf(len))" /* this is calling the class created above, and provides us the return string */
"text,len"/* variables used in groovy script */
;

Above, I’ve compiled a Groovy script in a calc using RUNJAVA.

This will make more sense when we move to the example sections.

Example Essbase Groovy Functions

  In this example, I’m using the same Groovy script as in the section above.

Example 1: LEFT function in Calc Script

    Figure 3: LEFT function in Calc Script

I’ve created a Groovy script that mimics EXCEL LEFT function.

Lines 12-13 in Figure 3 are explained in can are explained in Figure 4, below:

  Figure 4

When you run this script, the Groovy script leftgroovy gets compiled and cached.

In lines 25-33 in Figure 3, I’m trying to find all markets (@RELATIVE(“Market”,0)) that start with New for “Jan” “Budget” “Sales” of “Cola” (alias of 100-10).

@CalcMgrGroovyString("leftgroovy","",@LIST(text,len), @LIST(@NAME(@CURRMBR("Market")),"3"))

I defined the script name and passed my variables as a list. For the list values, I asked the script to send Name of the current Market member as my text and 3 as my length.

I used another CDF to compare the text return from Groovy script.

@CalcMgrCompare(text1, text2,ignoreCase)

Figure 5 illustrates the output:

  Figure 5: Output of CDF

Example 2: Using @CalcMgrGroovyNumber

    Figure 6: Using @CalcMgrGroovyNumber

In this script I’m generating “Jan” “Sales” “Budget” numbers for “New York” for the children of “Colas”, which are “Cola”, “Diet Cola” and “Caffeine Free Cola” using a Groovy script. I know that this can be done easily as an Essbase calc script; however, I wanted to point out the use of “Double” in Groovy script. Essbase numbers are doubles and you do need to keep that in mind when you create a number return Groovy script.

Figure 7 illustrates the output of that script:

  Figure 7

Example 3: Currency conversion without storing rates and currency attributes

Two tables in my Oracle database store the required (entity, currency and rates) information:

 
  Figure 8

This script gets the current entity and passes the rate to the calc.

  Figure 9

In the script above, I’m using a .groovy file.

Line 5: please look at the section on setting up JDBC drivers on Essbase server (below).

Lines 8 to 13: I’m compiling a Groovy script stored on my Essbase server that can be used later.

Lines 15 to 27: performs the currency conversion.

  Figure 10

I’m using Java sql capabilities in the Figure 8 script. All this script does is find the rate associated to the entity by joining two tables (FXRATES and DIM_ENTITY).

entmbr will be passed from the calc script.

Output

  Figure 11

Example 4: Update substitution variables in relational database, RUN a stored procedure and load data all from calc script

  Figure 12

In this script, I’m using a .groovy file.

Line 5: please look at the section on setting up JDBC drivers on Essbase server (below).

Lines 8-13: I’m running a Groovy script stored on my Essbase server. It has two variables, curmth and curyr. I’m passing the values of two substitution variables into the Groovy script.

Lines 16-36: Once the procedure is executed I’m using @CalcMgrExecuteMaxLScript to load data into Essbase.

  Figure 13

Line 9: I’m issuing an update statement passing Essbase substitution variables, which updates a SQL table, as illustrated in Figure 14, below:

  Figure 14

Line 15: I’m executing a Stored Procedure in SQL.

From the CDFLogger (sample section given below), you can see the substitution variables are passed to the Groovy Script.

[2016-07-13 15:50:20] [INFO] GroovyCDF main: [run, -file, C:/Temp/runsqlprocedure.groovy, curmth,curyr, Jul,FY03]

[2016-07-13 15:50:20] [INFO] Binding MaxL=com.hyperion.calcmgr.common.groovy.cdf.MaxLGroovyShell@1d1d2066

[2016-07-13 15:50:20] [INFO] Binding curmth=Jul

[2016-07-13 15:50:20] [INFO] Binding logger=java.util.logging.Logger@4f2b6c89

[2016-07-13 15:50:20] [INFO] Binding curyr=FY03

Setting up JDBC Drivers on Essbase Server

In order to execute statements against a relational database from calc script, you should set up the Java Database Connectivity (JDBC) driver on the Essbase server.

  Figure 15

Download the required JAR files to the UDF folder on the Essbase server: (<MIDDLEWARE_HOME>/products/Essbase/EssbaseServer/java/udf)

Restart Essbase services, so that the set up takes effect.

Use RUNJAVA command to load the JDBC driver.

RUNJAVA com.hyperion.calcmgr.common.database.cdf.DatabaseFunctions "load_driver" "oracle.jdbc.driver.OracleDriver";

Conclusion

Calc Manager’s Groovy functions open up a whole new world of possibilities in calc scripts. You can directly query relational tables, update relational tables and run stored procedures from an Essbase Calc Script itself.

You don’t have to worry about those CDF JAR files when you migrate/upgrade/move across servers. You don’t have to depend on a third party developed CDFs and wonder whether they are reliable or not.

You’ve got the power!

About the Author

Celvin Kattookaran is an Oracle ACE Director and Principal Architect with Huron Consulting Group. He is known for developing creative and effective business solutions to address his clients’ challenges. He is a frequent contributor to Oracle Forums as well as the Network 54 Essbase forum. His blog provides solutions to various riddles of EPM. During his leisure time he develops utilities for EPM products that make a consultant’s life easier. For more information on Celvin, please visit his blog: www.orahyplabs.com

This article has been reviewed by the relevant Oracle product team and found to be in compliance with standards and practices for the use of Oracle products.