Tag Archives: java

Evolving the Essbase Java API with JALE

I work with the Essbase Java API quite a bit. In fact, it’s more or less the reason that I picked up Java oh so many years ago. If you’re writing an app to do custom queries against a cube, need to develop a middle tier for your mobile app, or want to pull some cube stats, it’s the place to be. For me it’s a lot more useable than the C or VB APIs.

That being said, working with it can be a little challenging sometimes. Not because of inherent complexity, but because it was designed quite some time ago (especially in technology years) and before a lot of Java niceties came to be. In particular, there are iterators and other constructs that require a fair bit of boilerplate code to deal with. Here’s an example of getting a count of connections from the server:

public int getConnectionCount2(String username, String password, String server) {
	IEssbase essbase = null;
	IEssOlapServer olapServer = null;
	try {
		essbase = IEssbase.Home.create(IEssbase.JAPI_VERSION);
		olapServer = essbase.signOn(username, password, false, null, "embedded", server);
		IEssIterator connections = olapServer.getConnections();
		IEssBaseObject[] connectionInfoObjects = connections.getAll();
		logger.info("Connection count: {}", connectionInfoObjects.length);
		return connectionInfoObjects.length;
	} catch (EssException e) {
		logger.error("Essbase error: {}", e.getMessage());
		return -1;
	} finally {
		try {
			olapServer.disconnect();
			essbase.signOff();
		} catch (EssException e) {
		}
	}
}

Wow, that’s a lot of code – just to do something that’s ostensibly simple. We can’t realistically expect Oracle to overhaul their API. While it’s technically possible to create a new API (from Oracle’s standpoint) that’s also unlikely to happen. Unwieldy though it may be, it’s battle-tested and thorough (although a few of you out there are miffed that it doesn’t have support for varying attributes).

So if we can’t expect the API to be refactored and offer us some syntactic sugar, and can’t write a new API, what can we do? There’s another approach: wrap the API with another library with the express intent of making the Essbase Java API easier to use, less verbose, and leverage modern language features where it makes sense.

To that end, a side project of mine has been to create such a library, called the Java Antikythera Layer for Essbase (JALE). Here’s an example of some new code that can iterate the current Essbase connections. Note that this isn’t a direct replacement for the above since they do slightly different things:

@Test
public void testGetConnections() throws Exception {
	try (OlapServer server = Ess.create().signOnEmbedded(SERVER, USER, PASS)) {
		for (OlapConnectionInfo connInfo : server.getConnections()) {
			System.out.println("Conn info: " + connInfo);
		}
	}
}

Note that this is taken from a unit test in one of the projects I have for this little experiment of a library. As the library evolves more or as more people are interested I’ll post more samples and open up a GitHub repository. Some things to note about the above code-snippet and the design of the wrapper layer in general:

  1. Adds a convenience signOn method (signOnEmbedded) to make the intent of the method instantly understandable versus passing in a parameter called “embedded” as the current API requires.
  2. Drops the IEss prefix on classes/interfaces. Modern IDEs have reduced the need for this quasi-Hungarian notation.
  3. Instead of returning an an array of objects that need to be iterated and cast to their real types, a List of the expected object is returned (server.getConnections() returns the list).
  4. Java 7’s new “try-with-resources” construct is used and the JALE server object implements AutoClosable, obviating the need for a finally block here.
  5. Also note that JALE wraps the EssException in disconnect/signoff methods so we don’t need a dummy try block inside our finally block.
  6. Ess.create() is a convenience method that auto passed in the IEssBase.JAPI_VERSION static field. We can use an overloaded method if we need to call it with that parameter.
  7. The more astute of you out there may notice a couple of other things. :)

Internally, JALE has a utility library designed to help ease the implementation of replacement classes and call the right methods. Of particular note is an “IEssIterator” unroll method that is a veritable Swiss army knife for easily replacing the boilerplate code around the Essbase iterator. For example, check this bad boy out:

public class IteratorUtil {
	@SuppressWarnings("unchecked")
	public static <E, F> List<F> iteratorToList(IEssIterator iterator, ConversionDelegate<E, F> delegate) throws EssException {
		List<F> objects = new ArrayList<F>();

		for (int index = 0; index < iterator.getCount(); index++) {
			E object = (E) iterator.getAt(index);
			F converted = delegate.convert(object);
			objects.add(converted);
		}

		return objects;
	}
}

&#91;/java&#93;

Then in our wrapper for the traditional Essbase server object, we can do this:

&#91;java&#93;
public List<OlapApplication> getApps() throws EssException {
	return IteratorUtil.iteratorToList(olapServer.getApplications(), new ConversionDelegate<IEssBaseObject, OlapApplication>() {
		public OlapApplication convert(IEssBaseObject from) {
			return new IEssOlapApplicationWrapper((IEssOlapApplication) from);
		}
	});
}

Note that conversion delegate is a one method interface that just supports the generified convert method. It’s designed so we can just supply a lambda-esque convertion method block in the new method (in this case, getApps()) which in this case contains our logic for going from an IEssbaseObject to the designed type of OlapApplication (a new object in JALE).

Future Directions on JALE/a Hyperion Essbase API wrapper

There’s no timeline on a public release of JALE. At this point it’s highly experimental as I evaluate its feasibility. I am just implementing method as I need them, so don’t expect to see me wrapping the MDX routines any time soon (not that I don’t use them but it’s not a section of the API that “needs” much fixing). I’m not worried about a performance hit from wrapping the API but I do have some concerns about performance if there’s something in the native API that loads lazily or otherwise needs an active connection to the server that would cause things to act up when I wrap them an enumerate on them, for example.

As with all of my little side projects (particularly the open source variety), please let me know if you have any feedback or want to contribute. Unlike the others at this point, this one is very pre-alpha quality so I don’t have immediate plans to make it available, but jut wanted to discuss some of the things I am thinking of an working on.

Announcing Jolo, a Java library for printing text-based tables

Well, I’m at it again releasing another open-source project. I work with Hyperion and the Essbase Java API extensively, so it’s not uncommon for me to be doing things with grids and working on a command-line. I frequently write lightweight command-line clients to test things out. I didn’t love the options I was able to find in Java that would help print/format a text-based table. The ones I did find were clunky and hard to use.

So, you guessed it, I rolled my own. Jolo is a small and lightweight Java library with two goals: print out text-based tables, and have a tight and clean, yet flexible API. So let’s say we want to print out names, cities, and states in a three column table. The code looks like this:

public void testPrintTableWithColumns() {

    // setup table definition with column names/widths
    TableColumnList tcl = new TableColumnList.Builder()
        .add("Name", 40)
        .add("City", 15)
        .add("State", 2)
        .build();

    // createRandomRows is a helper function in this case but would otherwise
    // be your data that is an Iterable<List<Something>>.
    Iterable<List<? extends Object>> data = createRandomRows(10, 3);

    // create the printer and print the data
    TablePrinter tp = new TablePrinter();
    tp.outputTable(tcl, data);
}

Having the TablePrinter separate from the concept of a TableColumnList is nice because we can plug-in different TablePrinter implementations if we want to. In the above example I have a helper method creating the data for me (createRandomRows()) but in your program this would be something that implements the Iterable interface and contains a List of something that extends Object. In Java parlance that means you can print anything that’s Iterable<List<? extends Object>> – note that the toString() method will be called, so you can pass anything in. If I get time I’ll enhance the API a bit to provide some convenience functions and syntactic sugar. Given some data, the above code generates this table:

+----------------------------------------+---------------+--+
|Name                                    |City           |St|
+----------------------------------------+---------------+--+
|Jason Jones                             |Seattle        |WA|
|Cameron Lackpour                        |Philadelphia   |PA|
|Tim Tow                                 |Huntsville     |AL|
+----------------------------------------+---------------+--+

Note that with a simple parameter in the builder we were able to constrain the width of a given column.

The Hyperion Connection

This isn’t ostensibly Hyperion- or Essbase-related (save for the names in my table, hehe), but if you work on the things that I work on then this might be up your alley. The Jolo page has more information including a link to the Github repository. I will likely push this to Maven Central as time permits to make its inclusion in anyone’s projects all the easier. Unlike many of my other projects, this one is not incredibly commented [yet], so that will be coming in the future weeks as time permits. The API is pretty clean though so you shouldn’t have a hard time using it. Licensed under the very business-friendly Apache Software License.

Install Essbase Java API (jar) files as a local Maven artifact

Maven is a comprehensive build system for Java projects. A lot of people, including myself, have a love/hate relationship with Maven. The reasons for this relationship can be discussed at another time. In any case, used judiciously, it can make managing dependencies in Java projects much easier than handling them by hand.

Eclipse has pretty good Maven integration. It’s possible to setup a new project and browse for dependencies and add them automatically to your project. Everything just works. I develop quite a few Java applications that rely on the Essbase Java API, so I have imported the Essbase jar files to my local repository (since they are not available from a central public repository) to make development a breeze.

Here’s how you can do the same. First, you need to go get your Essbase jar file. These are installed on the Hyperion server. You might have to search around a little bit since the directories seem to change from release to release, but in the case of this stock Hyperion 9.3.1 server (with Hyperion installed in C:\Hyperion) they can be found at C:\Hyperion\AnalyticProviderServices\lib.

Here’s what the directory looks like on one of my machines:

Hyperion Java lib folder screenshot

Hyperion Java lib folder screenshot

Right now we’re just interested in the ess_japi.jar file. We’re going to import this in to our local machine’s Maven repository. This assumes you have Maven installed locally, of course. If not it’s pretty straightforward. Just Google around and all will be clear.

Maven is very particular about the versions of everything. It allows you to store multiple versions of files. This means that our single repository can store the files for Essbase 9.3.1, 11.1.1.1.0, 11.1.1.3, and so on, all next to each other. Since we’re importing this resource manually we are going to tell it the version. First though, let’s rename this local file to something more consistent with Maven naming conventions. Let’s rename it from ess_japi.jar to essbase-japi-9.3.1.jar (since this is a file from a 9.3.1 server). Change it accordingly for other versions. If this were 11.1.1.3 then we would make it essbase-japi-11.1.1.3.jar. Note that Maven “prefers” a versioning scheme of major.minor.revision but not all software (particularly Essbase) adheres to this, so we’ll do our best.

So now we have essbase-japi-9.3.1.jar. A simple command line will import this. From a command prompt in the same folder as the jar file, execute this command:

mvn install:install-file -Dfile=essbase-japi-9.3.1.jar -DgroupId=com.essbase -DartifactId=essbase-japi -Dversion=9.3.1 -Dpackaging=jar

Each -D indicates a parameter we are filling out: the name of the file, a Maven group ID (which we’ll decide to make com.essbase), what the name of the artifact itself should be (essbase-japi), the version, and lastly that it is a jar file. You’d think Maven could infer some of this for us but we only have to do this once in a blue moon so it’s not so bad. Maven will copy the file to the local repository. To make it visible from Eclipse you will likely have to rebuild your Maven repository index which is no big deal.

Essbase Jar import success

Success importing Essbase jar file

Now when we are specifying the dependencies for our projects from Eclipse, we can easily browse it by name and add it in to our Maven POM file:

Eclipse Select Essbase Jar Dependency

Eclipse Select Essbase Jar Dependency

Now we’re good to go. We can easily include this artifact in future projects quickly and easily. This is particularly useful if you happen to download the source code for some of my Essbase-related open source projects, which as of late rely on Maven for dependency management.

A @JExport odyssey: installing, updating, and troubleshooting the JExport custom defined function

Beginning

I have seen various references to JExport on the Network54 forum over the years but I have never had a use for it — until now. I would like to use a cube to sort of ‘manage’ data, but the SQL backend for the data needs to be updated at the same time. I could just use a report script out of the cube and load that to the RDBMS, but I want to be able to dynamically update based on changes to the cube, rather than have to sweep the cube at certain intervals. This also plays well into the fact that Dodeca will be used as a front-end to update the data, so that when the new data is sent in (essentially as a Lock & Send operation), everything gets updated on the fly.

There doesn’t really seem to be a lot of JExport documentation out there, and I couldn’t find anything at all where people are using JExport with a SQL backend.  After stumbling through things with my feeble Java skills, lots of trial and error, and numerous cups of coffee, I was able to get things all setup and working.

First of all, I had to get the necessary files. The JExport documentation labels it as “shareware” (and it is not an officially supported piece of functionality from Oracle/Hyperion), so for the time being I will simply host a zip of the files here. If it turns out someday that I have to take this link down, then so be it, but for now, here you are.

Extracting the zip revels a PDF whitepaper and another zip file. Inside the other zip you will find the following files:

  • ExportCDF.jar — this is the jar file (a jar file is a zip file that contains a bunch of Java classes and some other things).  You can open this up in 7-zip to view the contents.
  • ExportCDF_Readme.htm — some information on installing and configuration stuff
  • exportRDB.mdb — this is only necessary for demo purposes, you don’t really need it
  • rdb.properties — you’ll want this for configuring the JDBC connection.
  • RegisterExportCDF.msh — this is convenient to install the functions to Essbase for you (or you can do it through EAS).
  • src folder — this has the source for all of the methods, you’ll need this in order to compile a new .jar yourself (like if you need to make changes to the implementation).
  • Other files — there are some sample calc scripts, and a gif file (yay!)

So now what?

Following the instructions that are included is fairly straightforward.  I can vouch for the fact that these work on Essbase 7.1.6 but I do not know how things may have changed under the hood for newer versions, so if it works at all, then you may need to modify them accordingly.

As the directions indicate, you need to put the ExportCDF.jar file in to $ARBORPATH/java/udf.  Also put the rdb.properties in the same folder.  Here’s a tip if you want to use JDBC for the exports: ALSO put the JDBC jar file in this folder.  For example, the SQL Server .jar I am using is sqljdbc.jar, so I put this file in the same folder as well.

Still following the included directions, you need make some changes to your udf.policy file in the $ARBORPATH/java directory.  Add the following lines:

grant codeBase "file:${essbase.java.home}/../java/udf/ExportCDF.jar" {
    permission java.security.AllPermission;
};

Here’s another part where the included directions may fail you, if you are planning on using a SQL backend: ALSO add a line for your JDBC jar file.  After getting some Access Denied errors during my troubleshooting process, it finally occurred to me that I needed to add a line for sqljdbc.jar too.  Here’s what it looks like:

grant codeBase "file:${essbase.java.home}/../java/udf/sqljdbc.jar" {
    permission java.security.AllPermission;
};

Again, change the names accordingly if you need to use different .jar files. If you have to use the DB2 trifecta of .jar files, then I imagine adding the three entries for these would work too.  If you can’t access the udf.policy file because it’s in use, you may need to stop your Essbase service, edit the file, and restart the service.  Now we should be all set to register the functions.  The included MaxL file was pretty handy.

Be sude to change admin and password to an appropriate user on the analytic server (unless of course, you use ‘admin’ and admin’s password is ‘password’…) and run it.  On a Windows server this would mean opening up the commandline, cd’ing to the directory with the MaxL script, and running ‘essmsh RegisterExportCDF.msh’.  If you can’t run this from the Essbase server itself, then change localhost to the name of the server and run it from some other machine with the MaxL interpreter on it.  If you go in to EAS and check out the “Functions” node under your analytic server, you should now see the three functions added there.  Due to the way they’ve been added, they are in the global scope — that is, any application can access them.  From here, the example calc script files included with JExport should help you find your way.  I would suggest trying to get the file export method working first, as that has the least potential to not work.  Here is the code (I have cleaned it up slightly for formatting issues):

/*
 *    Export to a text file
 *   arg 1:  specify "file" to export to a text file
 *   arg 2:  file name.  This file name must be used to close the file after the calculation completes
 *   arg 3:  delimiter.  Accepts "tab" for tab delimited
 *   arg 4:  leave blank when exporting to text files
 *   arg 5:  an array of member names
 *   arg 6:  an array of data
 */

/* Turn intelligent calc off */
SET UPDATECALC OFF

/*
 * Fix on Actual so that only one scenario is evaluated, otherwise a
 * record for each scenario will be written and duplicated in the export
 */
FIX ("Actual")	

  Sales (
    IF ("variance" < 0)
      @JExportTo("file","c:/flat.txt",",","",
         @LIST(
           @NAME(@CURRMBR(Market)),
           @NAME(@CURRMBR(Product)),
           @NAME(@CURRMBR(measures)),
           @NAME(@CURRMBR(year))),
         @LIST(actual,budget,Variance)
    );
    ENDIF;
  }

ENDFIX

/* Close the file */

RUNJAVA com.hyperion.essbase.cdf.export.CloseTarget "file" "c:/flat.txt" ;

Note that you have to anchor the JExport function to a member — so in this case, if you try to take out Sales and the parentheses that surround the IF/JExportTo/ENDIF, EAS will bark at you for invalid syntax.  The main thing going on here is that we are calling the function with the given set of parameters (hey, there actually is a use for @NAME!  I kid, I kid…).  For blog formatting issues, I broke the call to JExportTo up into multiple lines, but this is still syntactically correct.  In the case where we are exporting to a flat file, we also call a method to close the file (this part isn’t needed for JDBC).

Feeling brave?  Try and run it.  If everything works as planned, you are well on your way to CDF happiness.  In my case, I was aiming high and trying to get the JDBC stuff to work, but when I realized that was going nowhere, I decided to simplify things and go with the flat file approach.  Among other things, it showed me that I had to fix up my member combinations a bit before it would fit into a SQL table.

But I want to use JDBC!

This is where you get to benefit from my pain and experience.  As you saw above, you need to put the .jar file for your RDMBS in the folder I mentioned, AND you have to edit the udf.policy file for the jar(s) you add.  Now you need to shore up your rdb.properties file.  You can comment out everything in the file except the section you need, so in my case, I put a # in front of the DB2 entries and the Oracle entries, leaving just the TargetSQL entries.

# SQLServer entries:
TargetSQL.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
TargetSQL.url=jdbc:sqlserver://foosql.bar.com:1433;databaseName=EssUsers
TargetSQL.user=databaseuser
TargetSQL.password=databasepw

Notice that all of these entries start with “TargetSQL.” This can actually be anything you want it to be, but whatever it is, that’s how you will refer to it from your calc scripts.  This means if you just have one rdb.properties file but you want to do some JExport magic with multiple SQL backends, then you just put in another section like foo.driver, foo.url, and so on.  Note that the syntax I am using is explicitly calling out port 1433.  This is what my little SQL Server 2000 box is using — you may need to adjust yours.  Originally I did not specify my JDBC URL correctly, but make sure for SQL Server you put the databaseName parameter on the end.  You could perhaps get away with not specifying the database name here and instead prefixing it to your table name in the JExport command, but this works so let’s run with it.

So what does the code look like in the calc script now?  It’s the same, except we don’t need the RUNJAVA line at the end, and we change the JExportTo line to something like this:

@JExportTo("JDBC","TargetSQL","","TEST_TABLE",
  @List(@NAME(@CURRMBR(Product)),@NAME(@CURRMBR(Market))),
  @LIST(Actual));

The function is the same, but the parameters have changed a bit.  We now tell it through the first parameter that this is a JDBC connection, and we tell it “TargetSQL” in the second parameter.  This should look familiar because that’s essentially the prefix it’s looking for in the rdb.properties file.

The third parameter is blank (this was the delimiter field for text file exports).  We then tell it a table name in the RDBMS to put the data in to, then we give it a list of parameters and values.  In this case, the Java method quite literally creates a SQL INSERT statement that will look something like the following:

INSERT INTO TEST_TABLE VALUES ('<Product>', '<Market>', 2)

Of course, <Product> and <Market> will actually be the current member from those dimensions, and the 2 would be whatever the actual data cell is (based on the FIX statement you saw earlier, of course).  Assuming you did everything correctly (and the specification for TEST_TABLE in the given database is consistent with the data you are trying to insert), everything should be all hugs and puppy dogs now.

But it’s not all hugs and puppy dogs

If, like me, you got here, but things didn’t work, you are now wondering “how in the name of all that is holy do I figure out what is wrong?”  Let’s recap.  We did all these things:

  1. Copied the .jar file to the server in the proper folder
  2. Copied the rdb.properties file to the same folder
  3. Copied any necessary .jar files for our RDBMS to the same folder
  4. Edited udf.policy to add all of these .jar files
  5. Ran the .msh file or used EAS to add the functions to the analytic server
  6. Stopped and started the Essbase service as needed (and one more time, just for good measure)
  7. Added the example code to a calc script in one of our apps
  8. Verified the syntax and it verified for us (no “function not defined” type of errors)
  9. Ran it (duh)
  10. Verified that everything works, at least for the .txt file output

How do we troubleshoot?  If you’re like me, and don’t know Java inside and out, and know even less about how the custom defined functions are setup within Essbase, then you really have no idea how to go about this.  I tried looking at the server logs and the app logs and the Essbase console itself, but I just couldn’t find where, if anywhere, the output from the ExportCDF methods would go (you know, so I could see an actual error message about what might be wrong).

So I did what any normal Essbase developer would do and I dug in and brute forced it.  There are some System.out.println() commands at various places in the Export functions, so I know if I can get the output from these then I can see what the deal is.  At this point, I also knew that I was able to successfully write files on the Essbase server (with the Export function to a flat file method), so, lacking any other clear method, how about I output the error messages to a file instead?  This is actually pretty straightforward, but the tricky part (for me) was recreating the ExportCDF.jar file from the .java files I had.

First of all, before any code changes, let’s see if we can turn the .java files into a .jar that still works on the analytic server.  The source files are located in our original .zip file under the src folder.  Under src/ is the com/ folder and another series of folders that represent the Java package name.  Let’s start things off by putting a folder on the server somewhere so we have a place to work in.  I used the server for a couple of reasons: one, I’ll be using the JDK from the server to roll the .jar file, so keeping the versions consistent will help reduce a possible area that might not work, and two, when I need to revisit this, I have all the files I need location in a place that is easy to get to, and regularly backed up.  If I were a real Java master I would probably do it all on test and target the architecture to make sure they’re compatible (although just for kicks, I compiled with 1.4 and 1.6 and the output appears to be the same).

If you have some fancypants Java toolchain that can do all this for you, then you should probably do that.  If you just pretend to know Java, you can get by with the following directions.  After putting all of the files in a folder to work on, I ended up with a jexport/ folder containing the com/ folder hierarchy. Next, create a folder such as “test” under the jexport/ folder.  This is where we will put the compiled Java files so we don’t muddy up our com/ folder hierarchy.  Then we need a command to compile the .java files to .class files, and another command to roll the .class files up into a single jar.  You can use the following commands, and optionally stick them in a batch file for convenience:

javac -d test -classpath %ARBORPATH%\essbase.jar com\hyperion\essbase\cdf\export\*.java
jar cf ExportCDF.jar -C test com

Note that for the classpath we need to refer to the essbase.jar file that is in our Essbase folder somewhere.  Hopefully all compiles correctly and you end up with a shiny new ExportCDF.jar file.  Stop the Essbase service, copy the new file to the $ARBORPATH/java/udf folder, start things up, and test that it works.

It was at this point in my own tribulations that I was quite pleased with myself for having used Java source code to create a module that Essbase can use, but I still hadn’t solved the mystery of the not-working JDBC export.  And since I still don’t know how to get the output from Essbase as it executes the function, the next best thing seems to be just writing it to a file.  With a little Java trickery, I can actually just map the System.out.println commands to a different stream — namely, a file on the Essbase server.

You can add the following code to the ExportTo.java file in order to do so (replacing absolute file paths as necessary):

/* Yes, there are much better ways to do this
   No, I don't know what those are */

try {
    FileOutputStream out = new FileOutputStream("D:/test.txt");
    PrintStream ps = new PrintStream(out);
    System.setOut(ps);
} catch (FileNotFoundException e) {}

It was after doing this that I discovered that in my particular case, my JDBC URL was malformed, and I got my access denied error (which was fixed by putting the sqljdbc.jar references in the udf.policy file).  After all that, I fired up the JDBC test once more, and was thrilled to discover that the data that had been working fine getting exported to a flatfile, was now indeed being inserted to a SQL database!  And thus concluded my two days of working this thing backwards in order to get JExport working with a SQL backend.

And now there is some JExport documentation out on the web —  Happy cubing!