Groovy and Essbase Cloud APIs - Part 3

 In my previous post, I showed you how to setup Essbase API working directory and create a simple Groovy script. In this post, I will explain what the script is doing and how to run it.

First, I'm defining an Essbase connection and a cube to connect to

def essServer  = IEssbase.Home.create(IEssbase.JAPI_VERSION

                  .signOn(user,pwd, false, null, "http://localhost/essbase/japi", "localhost")

             

def cube = essServer.getApplication("Sample").getCube("Sample"))

Define a member selection query on Cost Centre dimension and get all members.

def mbrSelection = cube.openMemberSelection("About to perform member selection"

    mbrSelection.executeQuery("Cost Centre", IEssMemberSelection.QUERY_TYPE_DESCENDANTS, IEssMemberSelection.QUERY_OPTION_MEMBERSONLY,"Cost Centre", "", ""))

Get the members returned from the query, find all members with aliases that match the passed argument and print them all.

def mbrs = mbrSelection.getMembers()

def mbr = mbrs.getAll()

def searchResult = mbr.findAll{(it.getAlias(null) == null) ? "" : it.getAlias(null).toLowerCase().contains((args.length == 0 ) ? "" : args[0])}.each{println it.name+" : "+it.getAlias(null)})

Now I just have create a .groovy file in the same working directory and save the script.

To run the script, I just need to run it and pass the alias I want to search for.

For example, search for all cost centres with aliases that contain "ope".

groovy helloEssbaseCloud.groovy "ope"

As you can see, the script works without any issues. You can now use Groovy to work with Essbase Cloud APIs instead of Java.

Groovy and Essbase Cloud APIs - Part 2

 In my previous blog, I showed you how to install Groovy on Linux using SDKMAN.

The second step is to create a working directory to put all of Essbase API files and groovy scripts in one place. By default, you need oracle user to access the API files, so I’m going to copy them into a directory under opc user which I’m going to use to run my Groovy scripts.

The API files are located under /u01/oracle/essbase/common/EssbaseJavaAPI/*

I’m going to need three jar files

1.      ess_japi.jar

2.      ess_es_server,jar

3.      ojdl.jar

To copy the files, I will run the following commands:

sudo cp /u01/oracle/essbase/common/EssbaseJavaAPI/lib/ess_japi.jar /home/opc/EssbaseJAPI/ess_japi.jar

sudo cp  /u01/oracle/essbase/common/EssbaseJavaAPI/lib/ess_es_server.jar /home/opc/EssbaseJAPI/ess_es_server.jar

sudo cp  /u01/oracle/essbase/common/Essbase/jlib/ojdl.jar  /home/opc/EssbaseJAPI/ojdl.jar

 The next step is to define CLASSPATH variable.

export CLASSPATH=”/home/opc/EssbaseJAPI/ess_japi.jar: /home/opc/EssbaseJAPI/ess_es_server.jar: /home/opc/EssbaseJAPI/ojdl.jar”

 Create a Groovy Script

The final step is to create the Groovy script, run the script and test it but first I need a use case.

I want to search the Sample application outline for members based on alias descriptions, not member names. The standard outline search feature available via the simplified interface is not going to do the job for me as shown next:

So, I’m going to write a simple Groovy script to search the outline based on member aliases. Here is the code:

I've intentionally used "def" to define untyped variables just to highlight the Groovy nature of the code, I personally like to declare the data type for better readability. For example, instead of "def cube" I would type "IEssCube cube

In my next post, I will explain the code and show you how to run it.

Thanks

Groovy and Essbase Cloud APIs - Part 1

 This post is a quick tip about using Groovy to work with Essbase Cloud Java APIs for those who prefer to code in Groovy instead of Java for whatever reason (in my case I just prefer the simplified Groovy syntax). Everything I’m about to demonstrate here applies to any Java API, be it Essbase on-premise or OCI etc.

For this demo purposes, I will be using OCI marketplace Essbase 21c deployment.

Install Groovy

The first step is to set up Groovy on the Essbase instance, there are many ways you can install Groovy but I prefer the Software Development Kit Manager (SDKMAN). To install using SDKMAN do the following:

1. Login to Essbase instance using opc user via SSH (using PuTTy, VSCode, PS etc)
2. Initialise and prepare the environment by typing

Finally, install Groovy

Groovy is now installed and GROOVY_HOME is set.

In the second part we will configure Essbase working directory and show how to use Groovy with Essbase Java APIs.