Write CDFs in Calc Scripts Using Calculation Manager Groovy Functions

Version 9

    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 ACED.gif

     

    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):

      image001.jpg
            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:

      image002.jpg
            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)

     

     

    Parameter

    Description

    name

    Optional*, name of the Groovy rule

    script

    Optional*, Groovy script

    varNames

    Variable names used in Groovy script

    values

    Values for the Groovy variables

     

    * 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

    image003.jpg
        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:

     

    image004.jpg
      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:

     

    image005.jpg
      Figure 5: Output of CDF

     

    Example 2: Using @CalcMgrGroovyNumber

    image006.jpg
        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:

     

    image007.jpg
      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:

     

      image008.jpg
      Figure 8

     

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

     

    image009.jpg
      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.

     

    image010.jpg
      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

     

    image011.jpg
      Figure 11

     

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

     

    image012.jpg
      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.

     

    image013.jpg
      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:

     

    image014.jpg
      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.

     

    image015.jpg
      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.