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.
1 2 3 |
## Import libraries from com.essbase.api.session import * from com.essbase.api.domain import * |
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:
- Establish a connection.
- Query the connection for the application names.
- Write the results of the query to a file.
- Close the connection.
Establishing a connection
This is done by first creating an instance. Then, passing along your login credentials to that instance.
1 2 3 |
## Set up instance and login essInst = IEssbase.Home.create(IEssbase.JAPI_VERSION) essLogin = essInst.signOn("unlkadmin","inCorrigibleDelinquent",0,None, "embedded", "esb.unlk.net:1423") |
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.
1 2 3 |
## Get list of applications lst = essLogin.getApplications() q = lst.getAll() |
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.
1 2 3 4 |
## Write to file filename = open("APP.txt",'w') for item in q: filename.write(str(item) + "\n") |
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.
1 2 3 |
## Close connections essInst.signOff() filename.close() |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
## Import libraries from com.essbase.api.session import * from com.essbase.api.domain import * ## Set up instance and login essInst = IEssbase.Home.create(IEssbase.JAPI_VERSION) essLogin = essInst.signOn("unlkadmin","inCorrigibleDelinquent", 0, None, "embedded", "esb.unlk.net:1423") ## Get list of applications lst = essLogin.getApplications() q = lst.getAll() ## Write to file filename = open("APP.txt",'w') for item in q: filename.write(str(item) + "\n") ## Close connections essInst.signOff() filename.close() |