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

Installing EPM 11.1.2.4 on Oracle Linux - Part 3 - Installing EPM

11/1/2015

1 Comment

 
We now have a working server with Oracle DB running. All we need now is to finish prepping the system to begin the installation of EPM.

One of the first things that needs to be done is make sure the profile for the oracle user has the right ORACLE_HOME and PATH variables.
Picture
Next we'll create the users which we'll use. To get into this you'll need to use SQLPLUS. You should be able to just type sqlplus at the command line provided the profile changes above were done. If you have an issue you can always log out and back in.

​In this example we created a user for all components of the application to use and one for the Planning application we'll be using later.

EPM System Database: epm
Planning Database: Budget
Picture
Here we unpack all the epm zip files we downloaded from the edelivery website. 
Picture
Picture
Navigate to the folder where you unpacked all the zip files and run the installTool.sh script.
Picture
Click through the first few screens.
Picture
Picture
I used the same folder where our database was stored and put Middleware alongside the database folder.
Picture
Select New installation and Click Next
Picture
Since this is pretty much a simple build, with Essbase, Planning, Calc Manager and Financial Reporting. We'll make the following installation options. 

From there click through until the installation is complete.
Picture
Picture
Once the install is done we'll click the Configure. If you accidentally close before you configure you can always run configtool.sh as described here.

epm online help
Picture
Here you can choose the name of the instance. Unless you intend to co-locate multiple environments here, you'll probably be best served by sticking to the default.
Picture
Enter the details for the database to create it.
Picture
Here we can pretty much select everything and click next. 

Note, if you are putting different pieces of the environment on different servers you'll be better off installing sections at a time. Follow the online documentation for the order of configuration.
Picture
Make sure you remember the location for import_export. This is where LCM stores the files and it always pays to know where this is.
Picture
Select all and click Next
Picture
This is important if you ever plan to have Reporting Analysis on multiple servers. Otherwise it's not such a big deal. If you do intend to have multiple Reporting Analysis folders make sure it's in a shared location and all nodes have full access to this location.
Picture
This section is where ewe configure the domain. This is also important for any tuning efforts you intend to do later.
Picture
Since we're running a tight install with limited memory we'll be deploying to a single managed server. Otherwise you can always deploy each product to its own server. This provides much finer control on the memory each piece can use.

​Also note the port this is very important.
Picture
Here we can chose the cluster name for Essbase. I prefer to stick to the standard unless there is a requirement otherwise. It helps to be default when it's time to troubleshoot potential issues.
Picture
We'll be keeping the defaults here so, Click Next
Picture
We'll be keeping the defaults here so, Click Next
Picture
We'll be keeping the defaults here so, Click Next
Picture
The port used by Oracle HTTP Server is important. Note it well.

Click Next
Picture
Choose your admin username and password. It doesn't hurt to change this to something obscure.
Picture
Almost there, Click Next to configure.
Picture
Picture
To startup the server you'll want to run the start.sh in the user_projects/epmsystem1/bin folder.
Picture
If everything went to plan you should now be able to log into the website from your client machine.
Picture
Thanks for following along the way.
Picture
1 Comment

Installing EPM 11.1.2.4 on Oracle Linux - Part 2 - Installing Oracle Database 11g

10/14/2015

0 Comments

 
The continuation of the project. Installing EPM on OEL.

Today we're going to be getting the database up and running. The version we're using is available on the Oracle Support site 11g Release 2 (11.2.0.4), if you don't have access to this version don't worry. Version 12 should work fine and the steps shouldn't be that much different.

Begin by logging into the console.
Picture
We'll start by creating a shell script to run all the updates we'll need. This list of updates will allow the installation of the database and epm on OEL 6.7
Picture
Picture
Next we can make the script executable and run it. This will prep the system for installation.
Picture
Picture
The next step will be to update the /etc/hosts file. We'll be adding the hostname for our server to this file.
Picture
Next we'll be adding the following entries to the end of the /etc/sysctl.conf file
Picture
We'll now add the groups and user account we'll be using to do the installs. I took these from the Oracle DB guide online.
Picture
We'll have to add the entries for EPM and Oracle to the firewall.
Picture
Next we add the user account we created to the sudoers group.
Picture
You'll want to set the password to something very secure since this account has super user rights.
Picture
From here we'll want to create the folders we'll be using for the install process.
Picture
We'll need to ad some entries to the /etc/security/limits.conf file. This is required for both the EPM and Oracle installs.
Picture
We'll need to modify the .bash_profile file in the home folder for our install-user. (In our case oracle
Picture
You'll need to upload the installs for Oracle Database now. Once you have them uploaded you can unpack them with the following command: unzip -o -d /u01/installs/oracledb11/ /u01/installs/oracledb11/\*.zip

Before you begin the install you'll want to make sure you have set putty to enable X11 forwarding (If you're running windows like me)
Picture
Run the program runInstaller now in the database folder and if X11 forwarding is working for you, you'll now be presented with the following screens. You can click Next to continue and fill in the relevant details as shown.
Picture
Picture
Picture
Picture
Picture
Picture
Picture
Picture
Picture
Picture
At this screen you'll need to open a terminal window logged in as root and run the following two scripts.
Picture
Picture
Picture
Picture
Picture
Picture
Congratulations. We now have a functional installation of Oracle Database.

In our next step we will begin the installation of EPM 11.1.2.4.
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

Installing EPM 11.1.2.4 on Oracle Linux - Part 1 - Installing OEL 6.7

10/5/2015

0 Comments

 
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)
Picture
Click Next
Picture
Select the Language and click Next
Picture
Select the keyboard and click Next
Picture
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.
Picture
Select Fresh Installation and click Next
Picture
Enter the hostname of your choice. Mine is oel01.local
Picture
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.
Picture
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
Picture
Click Next
Picture
Select your time zone and click Next
Picture
Here you get to enter your root password. I tend to randomly generate this to something really long.
Picture
This is where we create the virtual groups for our server. Select Create Custom Layout and click Next
Picture
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
Picture
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.
Picture
Click Format to accept that you will be destroying the existing layout (if any exists).
Picture
Click Next
Picture
I'd welcome feedback if this isn't the best option but I've had success with choosing Database Server and clicking Next
Picture
It'll take some time but soon you'll see this window. Click Reboot.
Picture
Congrats you now have a working Oracle Linux Server running OEL 6.7.
Picture
Stay tuned for our next installment.
0 Comments

Creating a Database in an Essbase Application

4/20/2012

0 Comments

 
Start by navigating to the EAS Console Application
Picture
Log in with credentials that have administrative rights. (or at least rights to create databases)
Picture
Right Click the Application where the Database will reside and select Create database...
Picture
Enter a name for the Database. Don't worry about the other options yet, just click OK
Picture
Congratulations, you have created an Essbase Database.
Picture
0 Comments

Creating a Application in Essbase Administration Services

4/12/2012

0 Comments

 
Start by navigating to the EAS Console Application
Picture
Log in with credentials that have administrative rights. (or at least rights to create applications)
Picture
Right-Click Applications and select Create Application Using block storage (More on storage types for later)
Picture
Enter the name of the application and click ok
Picture
Congratulations, you have created an Essbase Application.
Picture
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