Occasionally there is something that comes up where if only you could do 'X' then Essbase/Planning would be able to solve a problem you have.

In order to add functionality to essbase a common approach would be to create a Custom Defined Function (CDF) using Java. One of the interesting things about Groovy is that at the end of the day you can compile it to a jar as if it were java and you just need to distribute your jar along with the Groovy All jar.

To showcase creating a CDF with groovy we will attempt to create an extremly simple example. 

Our exercise will be to create a CDF that doubles a number. Hardly a task that merits a CDF or Groovy but it's a simple example just to open the door and show that it is possible and here is now to do it.

I'd like to start that most of this information is available on the essbase DBAG and you'll end up refering to that at some point if you take this upon yourself. Also I can't remember where on the net I picked up some of the knowledge but there are great webpages out there and if I remember which ones I learned this from I'll add the credits.

To start you'll need to create a folder structure.

I used the following:

FullName
--------
E:\Groovy_Projects\MultiplyNumberByTwo\bin
E:\Groovy_Projects\MultiplyNumberByTwo\lib
E:\Groovy_Projects\MultiplyNumberByTwo\src
E:\Groovy_Projects\MultiplyNumberByTwo\tgt
E:\Groovy_Projects\MultiplyNumberByTwo\bin\compile.bat
E:\Groovy_Projects\MultiplyNumberByTwo\bin\register.msh
E:\Groovy_Projects\MultiplyNumberByTwo\lib\groovy-all-2.4.6.jar
E:\Groovy_Projects\MultiplyNumberByTwo\src\com
E:\Groovy_Projects\MultiplyNumberByTwo\src\com\charlestorry
E:\Groovy_Projects\MultiplyNumberByTwo\src\com\charlestorry\Hyperion
E:\Groovy_Projects\MultiplyNumberByTwo\src\com\charlestorry\Hyperion\Planning
E:\Groovy_Projects\MultiplyNumberByTwo\src\com\charlestorry\Hyperion\Planning\HypFunctions.groovy
E:\Groovy_Projects\MultiplyNumberByTwo\tgt\com
E:\Groovy_Projects\MultiplyNumberByTwo\tgt\HypFunctions.jar
E:\Groovy_Projects\MultiplyNumberByTwo\tgt\com\charlestorry
E:\Groovy_Projects\MultiplyNumberByTwo\tgt\com\charlestorry\Hyperion
E:\Groovy_Projects\MultiplyNumberByTwo\tgt\com\charlestorry\Hyperion\Planning
E:\Groovy_Projects\MultiplyNumberByTwo\tgt\com\charlestorry\Hyperion\Planning\HypFunctions.class

​
You'll also need to define a few variables:
GROOVY_HOME=C:\PROGRA~1\Groovy\groovy-2.4.5
JAVA_HOME=E:\Apps\Java\jdk1.6.0_45

And add both to the path
PATH=%PATH%;%GROOVY_HOME%\bin;%JAVA_HOME%\bin

The groovy code is extremely simple.

Once you've got the groovy typed out you can run the following compile.bat file to compile the script to a jar file.

Next we'll copy the jar files groovy-all-2.4.6.jar and HypFunctions.jar to the essbase application folder:

PS F:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\app\DEMO1> dir


    Directory: F:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\app\DEMO1


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----        9/14/2016   2:58 PM                Plan1
-a----        9/14/2016   3:22 PM            189 DEMO1.apb
-a----        9/14/2016   3:22 PM            189 DEMO1.app
-a----        9/14/2016   3:22 PM           7404 essfunc.xml
-a----        5/23/2016  10:59 AM        7024433 groovy-all-2.4.6.jar
-a----        9/14/2016   2:23 PM          11129 HspCDFUt.cdf.jar
-a----        9/14/2016   3:08 PM           2429 HypFunctions.jar


Note the two new Jars.


Once that's done we'll need to register the function.

We can run the MaxL Shell Script:
​

We'll need to restart essbase at this point (Or at least the application.)

You'll want to look out for this entry in the application logs to confirm the CDF loaded successfully:

External [LOCAL] function [@JMultiplyNumberByTwo] registered OK

Once we see that, we're in good hands.

We'll now move onto Calc Manager (or EAS) to get a script working.

​This is the script I elected to put together for this example:

Once thats saved and deployed you can attach it to a form as a run on save

Hopefully at this point you can now test the form and everything is working.

Hope this helps, if anyone wants to see something more complex done drop some suggestions below and i'll see what we can do to make it happen.

This will be a small mini guide on the end to end installation of EPM 11.1.2.4 on a single VM with Oracle Linux. Hopefully if it's not too painful I'll attempt to put something up on how to do the same in Amazon Linux.

For the curious this vm currently only has 11GB of Memory assigned so we'll be deploying to a single app server and we won't be tuning the JVM options to crank it up any further from the defaults.


The plan for this is going to be 3 parts.
1 - Installing Oracle Linux
2 - Installing Oracle Database 11g and creating the Schemas for EPM
3 - Installing EPM 11.1.2.4


Without much ado let's begin.


Boot up on the Oracle Linux dvd. (I used version 6.7 which is freely available on eDelivery)

Click Next

Select the Language and click Next

Select the keyboard and click Next

Select the type of device you want to install on. In my case it's a VM with two disks.
I'm going with two disks to illustrate how to create virtual groups which we will use later on.

Select Fresh Installation and click Next

Click Configure Network. If you omit this step we'll have to configure TCP/IP later which isn't as much fun as it sounds.

This is a very simple environment so I always set it to 192.168.1.160/24 with a gateway of 192.168.1.1

Click Next

Select your time zone and click Next

Here you get to enter your root password. I tend to randomly generate this to something really long.

This is where we create the virtual groups for our server. Select Create Custom Layout and click Next

I tend to chose this layout. I'm sure it's not all 100% necessary as I'm not much of a Linux guru. (This is actually my first Linux project so I'm learning as we go along.)

The only take-home here is that I like to build the u01 mount point as it's going to be used later as the location for the app folder where we will be storing the oracle directory.
See this link for the method to my madness: Optimal Flexible Architecture

Oracle really doesn't want you to lose your data. Click Write changes to disk to say you are really really sure you are ok with it.

Click Format to accept that you will be destroying the existing layout (if any exists).

Click Next

I'd welcome feedback if this isn't the best option but I've had success with choosing Database Server and clicking Next

It'll take some time but soon you'll see this window. Click Reboot.

Congrats you now have a working Oracle Linux Server running OEL 6.7.

Stay tuned for our next installment.