If the prebuilt CDFs from Oracle that we discussed last time don’t meet your needs, it is also possible to create your own Custom Defined Function. Using a Java integrated development environment (IDE) is the simplest method for beginners, although it is possible to create a JAR using nothing more than a text editor and command line interface with Java.  There are several IDEs available for Java; however, I prefer to use Oracle’s JDeveloper as it makes doing this work very easy.

JDeveloper

JDeveloper makes Java development and deployment to JARs very easy. I am currently using JDeveloper 11.1.1.9 because it is compatible with both Java 1.6 and 1.7.  The EPM System 11.1.2.4 is shipped with Java 1.6, so using some version of Java 6 will prevent any errors due to incompatible code.

The software can be downloaded from the Oracle Technology Network. For just programming Essbase CDFs, select the Oracle JDeveloper Java Edition download. Unzip the downloaded files to the C:\ drive for simplicity, and it will create a C:\jdeveloper folder. At the first startup, point JDeveloper to a JDK installation.  The Oracle EPM JDK can be used at C:\Oracle\Middleware\jdk160_35 to ensure compatibility.  Any Java 6 JDK could be used and later versions are recommended due to security vulnerabilities in the lower Java 6 releases.

Next we need to create a generic Java project and add a new Java Class.  Here we will enter our code.  When writing a Java CDF that may be called using the RUNJAVA method, we need to add in an additional parameter to the “main” method.  Typically in Java programming the main method only accepts a String array (String[]) called “args”.  With Custom Defined Functions, we must accept a calculator context parameter as well as our String[] args. The main method description will appear similar to:

public static void main(com.hyperion.essbase.calculator.Context ctx, String[] args) {
   doSomething();
}

By adding the calculator context to our Java Class, JDeveloper will give errors as it is unable to find the definition for the context object. In order to properly compile our Java Class into a JAR, we must first make JDeveloper aware of the context object that is part of the Essbase server in a file called Essbase.jar.  To add Essbase.jar into the Classpath for our code, right-click the project in JDeveloper and select Project Properties… as shown below:

Next, navigate to the Libraries and Classpath option of the Project Properties and click the Add JAR Directory button.  Browse to the Essbase.jar file and click Select.  If done properly, your screen will be updated showing the Essbase.jar file as a Classpath entry as shown here:

To deploy our Class to a JAR, we must first create a deployment profile in JDeveloper.  To create a deployment profile, right-click the project, select Deploy >> New Deployment Profile… as shown below:

In the New Gallery dialog box, select Deployment Profiles then select JAR File in the right pane like this:

In the Create Deployment Profile – JAR File dialog box, enter a name for the Deployment Profile.  This will become the name of your JAR file.

In the JAR Options dialog box, select the checkbox to Include Manifest File (META-INF/MANIFEST.MF) and browse to select the main class.

Next, to deploy our Class to a JAR file, simply right-click on the project again and navigate to Deploy >> ProfileName, then click the Finish button to deploy the Class to a JAR file.

Now that we have our JAR file, the next steps are to copy the JAR to our Essbase server and register the function in EAS or by using MAXL.

After restarting Essbase, we should then be able to use our new code in an Essbase calculation script:

SET UPDATECALC OFF;
"Budget" = @CREATEBLOCK("New York"->@RELATIVE("Product",0));
FIX ("New York", "Sales", "Budget",@RELATIVE("Product",0))
"Jan"(
  @INT(@Random()*100)+1;
)
ENDFIX
AGG (Product, Market);

 

CDF Tips for Java Developers

Custom Defined Functions should be programmed using static methods in Java.  These classes typically are not be overridden and are used as utility classes, so using static methods is the best practice.

CDF methods can accept any number of parameters. These parameters may use any of the following data types:

• boolean
• byte
• char
• hyperion.essbase.calculator.CalcBoolean
• float, double
• lang.String
• short, int, long
• arrays of any of these types

The CalcBoolean object is defined in the Essbase.jar file and is equivalent to the Essbase calculator’s Boolean function and has values of TRUE, FALSE, or #Missing.

Any return values to Essbase can be void, or any of the above data types. Returned data is mapped to an Essbase-specific data type.  String return values are mapped to a String. Boolean return values are mapped to the CalcBoolean data type.  Any other return value is mapped to a double data type.

CDFs must start with the @ symbol, the rest of the name can contain letters, numbers, or the following symbols: @, #, $, and _.  CDF names must not include spaces.

If multiple users can run the same CDF at a time, the variables can be shared and overwritten.  For thread safety, I have used the synchronized attribute on methods in Java with success.

Any passwords or other properties that may need changed can be stored in a *.properties file 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.  Your methods can then use the *.properties file as a resource and read the values at runtime.  This is a better method than saving passwords directly in your Java code.

Custom Defined Functions open Essbase calculations up to a world of possibilities. There are several prebuilt options from Oracle that may meet any needs outside the traditional Essbase calculator engine. If the existing prebuilt code does not solve your specific need, custom code can easily be implemented to solve almost any calculation need in Essbase.

Oracle has given Essbase administrators several different prebuilt CDFs that can be used anytime they are needed. There are three main locations for prebuilt CDFs: Oracle’s Sample Code site, Calculation Manager, and Hyperion Planning.

Oracle Sample Code

Located at (http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html), Oracle’s Sample Code site offers eight different downloads containing several CDFs.  These downloads contain precompiled JAR files, the necessary MaxL statements to register the CDFs, and usually a sample calculation to show how to use the CDFs.  The eight downloads are:

• Financial Functions: Calculates loan maturity, principal and interest payments, and interest rates
• Export Functions: The main function used in this CDF was replaced by the DATAEXPORT Essbase calculator function
• Email Functions: Sends emails during calculations to alert administrators or users
• Date Functions: Includes various functions to work with dates, some are epoch date format and some are YYYYMMDD format
• Data Functions: Allows retrieval of external data from files or the Internet
• Counter Functions: Counts the number of passes through an outline during a calculation for tuning purposes
• Logging Functions: Logs messages to a specified file, replaced by @CalcMgrLog and @CalcMgrLogText functions
• String Functions: Manipulates text fields, many of these functions are also available in @CalcMgr* text functions

Calculation Manager CDFs

Beginning with Calculation Manager 11.1.2.3.502 and up, many prebuilt Custom Defined Functions are automatically deployed to the Essbase server and ready for use. Version 11.1.2.4 of Calculation Manager includes 67 prebuilt functions.  Many of these functions are for text and date manipulation.  The date functions are primarily for use in Capital Expenditure and Workforce Planning applications using the YYYYMMDD date format, but can be used in any application that stores dates using that format.

In addition to the text and date functions, there are six miscellaneous functions that can be useful in a variety of situations.  The six miscellaneous functions are:

• @CalcMgrExecuteEncryptMaxLFile – allows the execution of an encrypted MaxL file.
• @CalcMgrExecuteMaxLScript – streams MaxL code to the Essbase kernel for immediate execution
• @CalcMgrLog – writes messages to a specified text file, can include data from Essbase for validations
• @CalcMgrLogText – writes text-only messages to a specified text file
• @CalcMgrMDXDataCopy – using MDX queries, this function can select a range of data blocks in one application and copy the data to a target application
• @CalcMgrMDXExport – using MDX queries, this function exports data to a file quickly without the ugly formatting of a traditional MDX export

Some of these functions are not properly registered after applying the patch.  The errors can be fixed by editing the essfunc.xml file in the %EPM_ORACLE_HOME%\EssbaseServer\essbaseserver1\java directory. The following edits need to be made to have all of the CDFs registered properly:

• @CalcMgrGetCurrentDate
  • Change getCurrentDate(int) to getCurrentDate()
• @CalcMgrIndexOf
  • Change indexOf(String,int) to indexOf(String,String,int)
  • Change @CalcMgrIndexOf(text, beginIndex) to @CalcMgrIndexOf(text, searchString, beginIndex)
• @CalcMgrLastIndexOf
  • Change lastIndexOf(String,int) to indexOf(String,String,int)
  • Change @CalcMgrLastIndexOf(text, beginIndex) to @CalcMgrLastIndexOf(text, searchString, beginIndex)
• @CalcMgrSubstring
  • Change substring2(String,int,int) to substring(String,int,int)
  • Change @CalcMgrSubstring2(text, startIndex, endIndex) to @CalcMgrSubstring(text, startIndex, endIndex)
• @CalcMgrMDXExport
  • Change hyperion.calcmgr.common.cdf to com.hyperion.calcmgr.common.cdf.MDXExport

Hyperion Planning CDFs

Hyperion Planning also comes with several prebuilt CDFs.  In version 11.1.2.4, Planning contains 23 CDFs available for use in both Calculation Manager rules and in Essbase calculation scripts. When attempting to use these rules in Calculation Manager, the parameter definitions are not descriptive, as shown in the picture below:

To get a better feel for the parameters for each @Hsp* function, view their definitions in Essbase Administration services as shown below:

Next time we will cover creating our own CDFs from scratch.

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