#essbase japi

Recently the Essbase CDF sample code was moved and I thought it would be a good idea to let every one know where the new code can be found.

http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html

or

download.oracle.com

Click Sample Code Index on the left

OK Mike is on a roll posting CDF samples.  Check out the latest list: 

https://codesamples.samplecode.oracle.com/servlets/tracking/action/ExecuteQuery?query=essbase1

I am about to present at Kaleidoscope 2009 and thought I should do a blog about the session so that if anyone missed the session or fell asleep they could get the information they need.  Before I start I need to give credit to Mike and Toufic for sharing their code with my and thanks to Mike for helping me get up to speed on CDF's

So What is a CDF?

A CDF or custom defined function is any java class written so that is can interact with the Essbase calculator.

Data, metadata or both can be passed from the calculator to the CDF.

Data or metadata be returned to the calculator.

The CDF can call any outside Java process.

Basically if it can be done it Java it can be run in an Essbase Calculation.

What are the Ways I can run a CDF?

Register a Function. Static methods can be registered with Essbase so they can be run in a calculation script as if they were a native function.  You also want to do this is the person creating the calculation script will not be the same person who developed the function.  The registered function is then run in a calculation script like any other Essbase calculator function.  Here are some docs on registering functions http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/ddl/cdf/examples/register.htm

  RUNJAVA Command

This command will run a ‘Main’ method in a java class by calling it in a calculation script like this RUNJAVA somejavaclass “Parameters String []";

Most CDF’s should and can be written to support both methods and in many cases you may need both to support the function.  For instance if you have a system you want to log into and then pass a bunch of data or Essbase and then log out.  You might use RUNJAVA at the top of the script to do the login and then use a function to get the data and then use RUNJAVA to logout at the end.  This way you do not have to log into the system over and over again as you pull in data.  I show the use of both methods in my blog entry on integrating Twitter with Essbase. http://essbaselabs.blogspot.com/2009/05/ashton-kutcher-can-now-get-my-essbase.html

How do I install a CDF?

Create the JAR file and make sure it does what you want.

Copy the JAR file or files and any related files like properties files to the Essbase server in the directory $HYPERION_HOME /products/Essbase/EssbaseServer/java/udf  For new installations this folder may not exist and you may need to create it.

Modify the file udf.policy and grant the appropriate permissions if needed $HYPERION_HOME /products/Essbase/EssbaseServer/java/udf.policy  For Example:

grant codeBase "file:${essbase.java.home}/essbase.jar" {

permission java.security.AllPermission;

};

Register the function if needed.

You should follow the same rules and syntax as a standard Java Policy file and always error on the side of more secure.

CDF are loaded when the server starts and changes to the JAR file will require that you stop and start the server.

You can view an application log to see the your function loaded correctly.

[Thu May 07 12:54:02 2009]Local/Sample///Info(1200445)

External [GLOBAL] function [@JechoString] registered OK

  Top 5 CDF's

Beyond all the geeky coding stuff is some cool and really functional CDF's and instead of doing a top 5 specific list I have broken them down into the top 5 CDF categories and many of the categories have more than 1 useful function.

Data Functions - Functions like these allow a developer to get at data not normally accessible to Essbase.  In the example I provided (see below) I go after stock data from a free Yahoo http quote service.  This concept can be applied to any number of web service or internal systems.

Essbase Java API based functions - Ever wanted at add a member while doing a calculation, well you can you just need to put the correct files in place and call the Essbase JAVA API in your CDF.  The sky is the limit with the JAPI and I include moving data from one cube to another and creating substitution variables but if you can do it in the JAPI you can do it in a CDF.

J-Alert - Ever wanted to know the progress of your calculation with out having to check the logs?  If so this function is perfect!  It can send emails with various content at any point in the process.

String Functions are ideal for debugging your calculation scripts and enhancing.  Things like string concatenation that are difficult in the Essbase are a breeze with these functions.

J-Export - This has got the be the most asked about CDF in the field and among internal teams.  This allows you to do exports of targeted data to various output types and it does it all screaming fast!

There are three different ways to perform logging using a CDF. The first is using the JAPI logging capabilities. The second is using a third party logging package like “Log4J”. Finally, the Essbase calculator provides for a way to use Essbase logging. I will be discussing the latter two methods in this blog. The Essbase calculator allows for logging within the Essbase application log itself through the use of the Essbase context. Using the logging CDF, this can only be accessed using the RUNJAVA command in a calc script. An example of this would be as follows:

RUNJAVA com.hyperion.essbase.cdf.LogFunctions "The calc script has finished";

Using a third party logger like Log4J, there are dependency files that must be included in the UDF directory along with the CDF jar file. In this case, the log4j-1.2.8.jar file (which can be found under the Hyperion/Common directory structure) and the log4j.properties file. The properties file will need to be modified to direct the log output file to the appropriate location on your machine. There are two functions in the logging CDF:

@JlogDebug (string, string array, double array)

Logs a debug message to the Log4J log file.

@JlogDebug("On Budget" ,@LIST(@NAME(@CURRMBR(Year)) ,@NAME(@CURRMBR(Market)) ,@NAME(@CURRMBR(Measures)) ,@NAME(@CURRMBR(Product)) ,@NAME(Actual) ,@NAME(Budget)) ,@LIST(Actual,Budget));

“2009-04-21 15:49:41,875 DEBUG On Budget :: Jan New York Profit 100-10 Actual Budget 0.0 -2.0E-15”

@JlogError (string, string array, double array)

Logs an error message to the Log4J log file.

@JlogError("Over Budget" ,@LIST(@NAME(@CURRMBR(Year)) ,@NAME(@CURRMBR(Market)) ,@NAME(@CURRMBR(Measures)) ,@NAME(@CURRMBR(Product)) ,@NAME(Actual) ,@NAME(Budget)) ,@LIST(Actual,Budget));

@JlogError("Over Budget" ,@LIST(@NAME(@CURRMBR(Year)) ,@NAME(@CURRMBR(Market)) ,@NAME(@CURRMBR(Measures)) ,@NAME(@CURRMBR(Product)) ,@NAME(Actual) ,@NAME(Budget)) ,@LIST(Actual,Budget));

Not sure if this is covered in any document or on any other blog but I could not find it so I thought I would share the steps I took to do this.

Pre Requites:

Essbase is installed and working and is accessible from the machine where you plan to do your development work.

APS (Analytic Provider Services) is installed, this does not have to be running to use the API but you will need files from this installation.

JDeveloper is installed.  I am using JDeveloper 11.1.1.0.1 but I am sure earlier versions will also work.

  In JDeveloper create a new 'Generic Application' by selecting File New...

  Give your application a name (i.e. Essbase Java API) and click finish.

With the new application selected create a new 'Generic Project' by selecting the File New menu or right clicking on the application in the application navigator.

Give the new project a name and select Java for it's technology.

Click Next and enter a default package name (i.e. com.oracle.essbase.japi) and click finish.

On the server where you have installed APS and Essbase copy the following files and put them in a directory that will be accessible from the JDeveloper project:

%HYPERION_HOME%\products\Essbase\aps\lib\cpld14.jar

%HYPERION_HOME%\products\Essbase\aps\lib\ess_es_server.jar

%HYPERION_HOME%\products\Essbase\aps\lib\ess_japi.jar

%HYPERION_HOME%\common\loggers\Log4j\1.2.8\lib\log4j-1.2.8.jar

%HYPERION_HOME%\products\Essbase\aps\bin\essbase.properties

When copying these files to your machine make sure you have following directory structure:

Local Folder\lib - This folder has all the jar files.

Local Folder\bin - This folder has properties file.

Local Folder\data - This folder is empty and when you run your first application a file will be created there for you called domain.db.

In JDeveloper right click on the project created above and select 'Project Properties'.

Select the section named 'Libraries and Classpath' and add a reference to the 4 jar files from above.

Select the section ' Run/Debug/Profile' and edit the default profile.

In the Java Options for the client JVM add the following parameter:

-DESS_ES_HOME= (i.e. -DESS_ES_HOME=C:\JDeveloper\mywork\EssbaseJAPI\japi)

Now you are ready to program using the JAPI to do this you will need a Java class.  Right click on the project and select New...

Select New Java Class and click OK.

Give the class a name and select any of the options you want.