Introduction to CDFs

I attended a Matt Milella presentation several years ago about CDFs and always wanted to use one.  A couple of years ago, I was in a bind at a client who needed very specific to an Essbase extract for integration with SAP, and I actually had a chance to write one from scratch.  It’s not very often that you are faced with such a problem, but it’s great to know that Oracle has given us the tools to handle almost any situation.

Introduction to Custom Defined Functions

The Essbase calculator engine is use in Block Storage Option (BSO) and hybrid aggregation applications. There are 160 built-in calculator functions. BSO databases are known for their superior calculation ability due to these functions; however, occasionally there is a need for additional functionality not covered by the calculator engine or built-in functions. In these instances, Custom Defined Functions can be used to extend the calculator engine.

Custom Defined Functions (CDFs) are Java classes written to interact with the Essbase calculator. CDFs allow data, metadata, or both to be passed from the calculator engine into Java.  Data or metadata can then be returned to the calculator engine. CDFs harness the power of Java, so they are able to call any outside process.

CDFs open a world of possibilities through the use of Java.  They can be used to: submit MaxL commands dynamically from within a calculation, interact with the server operating system, do complex calculations not available with the existing calculator functions, or even fetch data from the Internet. These functions may be deployed at the server-level and available for use by all applications, or at the individual application level.

Java code is compiled into a Class file. Classes must then be packaged into a Java Archive file, also known as a JAR. The JAR file is then placed on the Essbase server in the %EPM_ORACLE_HOME%\products\Essbase\EssbaseServer\java\udf directory for global or server-wide functions or they can be placed in the %EPM_ORACLE_INSTANCE%\EssbaseServer\essbaseserver1\app\ApplicationName directory for an application-level CDF.  When using global CDFs, the udf.policy in the %EPM_ORACLE_HOME%\products\Essbase\EssbaseServer\java directory must be updated to allow Essbase access to the custom code.  A line can be added to the end of the udf.policy file as follows:

grant codeBase "file:${essbase.java.home}/../java/udf/yourCDF.jar" {   
   permission java.security.AllPermission;
};

Next, the CDF must be registered with Essbase.  This can be done in Essbase Administration Services (EAS) by selecting Edit >> Functions on your Essbase server, or within MaxL. To register the @JsendMail function, the MaxL code would look like:

create or replace function '@JechoString' as 
'com.oracle.essbase.cdf.StringFunctions.echoString(String[])' 
spec '@JechoString(strArray)' 
comment 'Echoes back all arguments passed to the function.  To pass an array of arguments use @List(comma delimited list)'

Essbase picks up new Custom Defined Functions on startup.  When creating new global CDFs, the Essbase service must be stopped and restarted to recognize the new function.  When creating application specific CDFs, restarting the application is sufficient. When developing new CDFs, it is recommended to begin with application level functions during the initial testing as it is easier to stop and restart at the application level than restart the Essbase service.

As the application starts, messages are written to the application’s Essbase log about the functions. When creating new functions, check the Essbase application log to verify that the function is registered successfully. Any messages other than a successful registration indicate changes are needed to the CDFs registration in EAS. When a function is registered properly the following message is displayed in the log:

[Wed May 20 10:14:34 2015]Local/Sample///10636/Info(1200445) 
External [GLOBAL] function [@JechoString] registered OK

Once defined properly and registered with Essbase, Custom Defined Functions may typically be run in two ways.  The first method of running a CDF is similar to a typical Essbase calculator function.  When running a CDF using this method, it must be placed inside a calculation member block.  This option calls the Java methods directly from inside the Class file. If the CDF definition was setup “with property runtime” in MaxL, or with the “Runtime” box checked in EAS, the CDF will execute for all members of the calculation.  The amount of blocks impacted by the CDF can be limited with a FIX statement. A typical calculation script using the first option for running a CDF is:

FIX ("New York", "Sales", "Budget",@RELATIVE("Product",0))
"Jan"(
    @INT(@Random()*100)+1;
)
ENDFIX

The second option to run a CDF is using the RUNJAVA command.  This method of running a CDF executes only once and calls the “main” method of a Java class.  The Custom Defined Function must be written properly to accept this method of calling it. Using the RUNJAVA command does not require that a CDF be registered in EAS or by MaxL. A typical RUNJAVA command is:

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions
  true -D C:\\Scripts\\BatchCalc.mxl
  678870187,2425911017
  050927985109683855112314385651
  7592008210957166529169707423014313885401;

That’s all for this week, next time we will talk about prebuilt CDFs from Oracle that we can use right away. Later, we will cover how to write your own CDF (assuming some level of Java coding experience)/