CHARLES TORRY
  • Main
  • Oracle - Hyperion EPM
  • Links
    • Powershell >
      • List Services & Dependency's
      • Creating an Object / Table Variable
    • ASP.NET >
      • ASP.NET Forms Based Authentication with Active Directory
    • SQL
    • Oracle EPM Doc Links
    • Reset admin password to default

Oracle Hyperion EPM

Writing Essbase CDFs with Groovy

9/14/2016

0 Comments

 
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.

​E:\Groovy_Projects\MultiplyNumberByTwo\src\com\charlestorry\Hyperion\Planning\HypFunctions.groovy

    
Once you've got the groovy typed out you can run the following compile.bat file to compile the script to a jar file.
​E:\Groovy_Projects\MultiplyNumberByTwo\bin\compile.bat

    


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:
​

​E:\Groovy_Projects\MultiplyNumberByTwo\bin\register.msh

    
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:


DoubleAge (CalcManager)

    
Once thats saved and deployed you can attach it to a form as a run on save
Picture
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.
Picture
0 Comments

Updating Essbase text lists with the Java API

10/5/2015

0 Comments

 
One of the features I've started working with in Essbase are members that store text.

The limitations for this feature are that you can only store 1024 different strings of text and there is no easy way to automate the maintenance of the members you want in this text list.

Since there seems to be no way to increase the limit (and so far the solutions I'm working on don't depend on more than 1000 different options for a single text list) I decided to work on coming up with a way to automate the maintenance of the list using the Java API.




The general goals are that the solution will connect to a database to pull the list from a SQL table and then load that into Essbase.




In order to generate an incremental list of IDs with text well be using an olap function most versions of sql come with--row_number().


UpdateTextList.groovy

    
0 Comments

    Author

    Charles Torry

    View my profile on LinkedIn

    Essbase Groovy CDFs

    EPM on Linux - Part 3

    ​EPM on Linux - Part 2

    EPM on Linux - Part 1

    Archives

    September 2016
    November 2015
    October 2015
    April 2012

    Categories

    All
    11.1.2.4
    Application
    Create
    Database
    EPM
    Essbase
    Groovy
    How-To
    Java API
    Linux
    OEL
    Oracle Linux 6
    Planning
    Smart List
    Text List

    RSS Feed

  • Main
  • Oracle - Hyperion EPM
  • Links
    • Powershell >
      • List Services & Dependency's
      • Creating an Object / Table Variable
    • ASP.NET >
      • ASP.NET Forms Based Authentication with Active Directory
    • SQL
    • Oracle EPM Doc Links
    • Reset admin password to default