Thursday, July 30, 2015

Essbase CDF Workshop Part I

Introduction

I decided to write a series of posts on a topic that is as old as essbase itself (maybe not - but it’s pretty old). But, in my opinion, it  was not sufficiently covered, did not get enough attention by consultants, and as a result, it is a sort of taboo to talk about with many customers.


The topic is CDF or Custom Defined Functions. If you are on this page you probably know that:
  • Those are “additional” functions written in Java.
  • You can write your own, or use existing ones.
  • They are registered on Essbase server
  • They can be called from calculation scripts/Business Rules as if it was a native Essbase function.


From my experience there are not that many essbase developers that actually do create their own CDFs. From client point of view somebody will have to maintain those CDFs, probably expand for new requirements, and since this is a rare (and expensive) skill, they try to stay away from custom code. Which is fully understandable, and I often supported those decision. As a result essbase developers don’t have much incentives to develop this skill, and this is a vicious (virtuous - depends who you ask) cycle.


I think things started to change when Oracle provided a package of CDFs with Planning. In fact there are so many useful CDFs there, that one might think that there’s no need anymore to develop your own. And of cause there are sample CDFs that you can download from Oracle:


What i try to show here with a few use cases, is that mastering CDFs helps you to become a better Essbase developer, even if you never deploy them for your client. The points below are interrelated:
  • They allow you a very detailed level of debugging
  • CDFs allow you to see how Essbase calculation engine works
  • You can learn about sometimes unexpected essbase behavior
  • You can eliminate inefficiencies by monitoring your code with CDFs


Configuring CDFs Step-By-Step



So, I never wrote a single line of Java code. Where do I start? Probably from the sample code. I would start with String functions. They are extremely useful in many situations, including debugging, monitoring, performance optimization, etc. Also, after this workshop you’ll be able to apply the same process to changing other sample functions.
From the Oracle page of sample CDFs download string.zip. First, lets register existing CDFs and verify they works. Here are the steps we would need to do:
  • Extract String.zip file and copy CDF_String.jar to your products folder, not in user_projects. If udf folder doesn’t exist, create it.
/home/oracle/Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/java/udf
  • Security file needs to be updated:
/home/oracle/Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/java/udf.policy


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


  • String.zip package comes with RegisterStringFunctions.msh script that registers the functions. Update the username/password/server and run the script.
  • Restart Essbase server (service)
  • Verify the newly registered CDFs work. The Zip file you just downloaded contains sample calculation script (_String.csc) that uses @JechoString(@LIST("Level 1: Equal ",@NAME(@CURRMBR("Product")))); example.  If you open StringFunctions.java, you can see some extra info on how to call the function. The post important part of that _String.csc is below. Obviously you need to change it so it reflects your outline:


Fix (@CHILD("Product"),"New York","Jan","Sales")
 "Actual" (
   @JechoString(@LIST("Current Product: ",@NAME(@CURRMBR("Product"))));
)
EndFix


  • There are a few things to keep in mind:
    • The function needs to be inside a calculation block (same as IF...ENDIF statement)
    • In the example of @JechoString, the function writes into essbase log. Not application log. That is D:\Hyperion\user_projects\epmsystem4\diagnostics\logs\essbase\essbase_0\ESSBASE.LOG or whatever you have setup in your environment.

Changing @JechoString to output into file of my choice

If you done all those steps, and with a little bit of luck,  you should be able now  to write out from your calc script into essbase log. I was lucky for quite some time, until at one of my clients that @JechoString did not output into essbase log.
After researching the problem for several hours i still had no idea why the function didn’t work. It was something about the folder structure of how essbase was installed, or security on that ESSBASE.LOG file, or something else with that file. So i thought: “Maybe i shouldn’t output my debug statements into ESSBASE.LOG in the first place”. It takes forever to open that file, it grows pretty fast, in short - it would be nice if i could write into a different file. So i decided to change that @JechoString so that it writes into the file that i provide as a parameter.


Here i have to make a disclosure: i am not a hardcore Java developer. So i’m sure every line of my code can be written more efficiently, with newer methods, cleaner, cooler, etc. So if you are a Java superstar, please feel free to make necessary changes, and leave the comment so that i can learn too.


So, the original code looks like this:
public static void echoString(String[] strArgs) {
StringBuffer output=new StringBuffer();
for (int i=0;i<strArgs.length ;i++) {
output.append(String.valueOf(strArgs[i]) + "\t") ;
}
System.out.println("***** Echo:\t" + output.toString() );
output=null;
}   
Now we want to do the following changes:
  • Add a parameter, that will tell whether the function should run in a default mode, i.e. output into ESSBASE.LOG, or run in an “enhanced mode”. Meaning it will accept a filename into which the function writes the array of strings.
  • For simplicity, lets say, if the first string in an array is "filename", then the function will expect the next string to be a path with a file name.


And here’s the code:


public static void echoString(String[] strArgs) {
StringBuffer output=new StringBuffer();
String outFileIndicator = strArgs[0];
String outputString="";
String fileName="";
PrintWriter out;
if(outFileIndicator.equalsIgnoreCase("filename")){
if(strArgs[2].length()>0){
fileName=strArgs[1];
if(strArgs.length>2){
for (int i=0;i<strArgs.length ;i++) {
outputString=outputString + String.valueOf(strArgs[i]) + "\t";
}
try {
out = new PrintWriter(new BufferedWriter(new FileWriter(fileName, true)));
out.println(outputString);
out.close();
} catch (IOException e) {
System.out.print(e.getMessage());
e.printStackTrace();
}
}
}
}else{
for (int i=0;i<strArgs.length ;i++) {
output.append(String.valueOf(strArgs[i]) + "\t") ;
}
System.out.println("***** Echo:\t" + output.toString() );
}
output=null;
}

Recompiling



Now i have a changed StringFunctions.java file. How do i recompile and redeploy it? You may use whatever IDE you like, but keep in mind you can run into issues if for compilation you use a newer version of JDK, than Essbase’s JDK. Assuming you don’t use any IDE, below are the steps to compile your class and jar file.
Install JDK 6 and use it for compilation


If the package name is com.oracle.essbase.cdf (that is the first line in your StringFunctions.java), the folder structure com\oracle\essbase\cdf should exist.


Put this folder structure in D:\CDF for example. Open CMD and mavigate to D:\CDF.




We first need to recompile the StringFunctions.class. Issue the following command:


D:\CDF>"C:\Program Files\Java\jdk1.6.0_45\bin\javac" -cp . com\oracle\essbase\cdf\StringFunctions.java


Now we have StringFunctions.class, and we need to add it to jar. Below is the command to create cdf_string.jar
D:\CDF>"C:\Program Files\Java\jdk1.6.0_45\bin\jar" cvf cdf_string.jar com\oracle\essbase\cdf\


Now we need to stop Essbase service, copy cdf_string.jar into /java/udf (same folder where we put the original file), and start essbase server.


Lets test that the function works. This is the test calc script:

SET CREATENONMISSINGBLK ON;
FIX (FY16, Q2_Outlook, Ver1, "HSP_Inputvalue", "Local", E_100,
    "GroupA",NoPCC,  C_7300,  "B_2301", "Feb"
,"NoAccount"
)

  
"CurEmp"(
CurEmp=100;
@JechoString(@LIST("Current EmpType: ",@NAME(@CURRMBR("EmpType"))));
@JechoString(@LIST("filename","D:\\Hyperion\\Automation\\Logs\\testJecho.txt","Current CostCenter: ",@NAME(@CURRMBR("CostCenter"))));

     );

ENDFIX
SET CREATENONMISSINGBLK OFF;

And this is what we find in D:\Hyperion\Automation\Logs\testJecho.txt on that essbase server:

filename D:\\Hyperion\\Automation\\Logs\\testJecho.txt Current CostCenter: C_7300


Probably we don’t want filename D:\\Hyperion\\Automation\\Logs\\testJecho.txt to be written into the file, since it’s the name of a file i just opened.
You don’t have to be a Java guru to realize that:

for (int i=0;i<strArgs.length ;i++) {
outputString=outputString + String.valueOf(strArgs[i]) + "\t";
}


Needs to be changed to:

for (int i=2;i<strArgs.length ;i++) {
outputString=outputString + String.valueOf(strArgs[i]) + "\t";
}


In the next post i’ll start showing some use cases, so stay tuned!