ODI, the Essbase API and Jython I

Through this blog, we’ve been trying to advertise the fact that the tools we have at our disposal go a long way towards creating user-friendly solutions. Today, I thought I’d delve into the world of the Essbase API. Further, I thought we’d explore how the API can work, with Jython, and by extension, ODI. There are other excellent folks who have written extensively, and far more lucidly than I ever could (Tim Tow and Jason Jones have done sterling work in this regard) on the subject of the API. This is just my attempt exploring the API with Jython, through a simple example.

In past posts, I’ve gone over use cases where we’ve shown how a bit of scripting can extend your ODI integrations. In the same vein, today, we’ll look at a simple example of using ODI procedures, blended with the API, and garnished with some Jython. All we are trying to do here, is to get a list of all applications on a server. To illustrate, if we set up a simple procedure:

We’ll provide a name, and select “Jython” as the associated technology.

From the Details tab, let’s add a new step.

Let’s now get to adding some code. We’ll add code to the Target. Now the first thing to realize, is that though Jython will do a lot of the work, we still need Essbase libraries to be explicitly imported into the script. For this example, we will need the following snippets. The good thing about ODI, is that the driver files needed for this type of procedure are already available, if you are running procedures locally. I.e., you do not need to download any jar files to make this work.

If we now think about the things we will need to do, mechanically, to get a list of applications, the following steps will be involved:

  1. Establish a connection.
  2. Query the connection for the application names.
  3. Write the results of the query to a file.
  4. Close the connection.

Establishing a connection

This is done by first creating an instance. Then, passing along your login credentials to that instance.

In my simple example, I am hard-coding the username, password, whether to use a CSS token, choosing not to use a provider services connection, i.e., “embedded” mode, and the server name respectively. Of course, you can make it more dynamic (and respectable) with ODI variables.

Query for names

The next step is to ask the connection for available application names.

I initially thought that all we needed was the first line of code. But, it turns out, from the documentation, that since “GetApplications()”, is an iterator, you need a bit of help from the second line, “GetAll()”. I might be completely off on this, but that’s what worked for me. Also, notice how simple J(P)ython variables are?

The results are returned from Essbase, as an “array”.

Write to file

We now need to take the contents of the last step, loop through, and write those to a file.

The first line, opens a writeable file. The second line looks at each “item” within the iterable variable, and adds it to the opened file, separated by a carriage return. It should be noted that I had to explicitly convert the “item” to a string.

Closing the connection

Both the Essbase connection and the file connection now need to be closed.

So, anyways…

If we now run the procedure, we can see a list of applications.

We can add error trapping and more sophistication, as needed. As you can see, there are some benefits to using this type of integration. Jython is pretty simple to read, and ODI already provides a lot of structure needed. I will add more content on using ODI and the API in future posts. I hope this one serves as a humble introduction to this sort of integration.

The code in its entirety.



About Vijay Kurian

Known as the Clem Fandango of EPM consulting, Vijay Kurian has been developing enterprise solutions for companies for the last 12 years (increment years if reading post-2015). Having worked with Essbase, Planning, DRM and other assorted technologies during that time, he’s made the frankly, average decision, to write about them. He is, surprisingly, an Oracle ACE Associate. He hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

Leave a Reply

Your email address will not be published. Required fields are marked *