The Complete Guide to Essbase 21 on Docker

This post will be something of a followup to my Kscope22 presentation on creating an Essbase 21 Docker image. Back in June during the presentation, I mentioned that while the images I had created prior to the conference were working fine, when I went to rebuild them for purposes of confirming the presentation details and creating new screenshots, I was inexplicably running into issues with the Essbase server. It would seem to start up fine, but I couldn’t actually login. I now know what that issue was and in the steps that I’ll describe in this post, I’ll call out what the fix is.

Unlike my previous efforts to build an Essbase Docker image based on the EPM 11.1.2.4 product line, the Essbase 21 Docker image actually relies on Oracle’s own scripts that they provide. This post will completely walk you through everything you need to do to create your own Essbase 21 Docker image, including how to patch it to a later point release such as 21.3, how to fix the bug I was running into in June, how to run it, load some sample data, share it with others, and different URLs for using the REST API, XMLA, and more.

Continue Reading…

Simple Docker Compose file for Essbase

I’m working on a few things and some future blog posts and as with many of the things I am doing these days, I am using a Dockerized Essbase server. If you or your company has successfully followed the steps as laid out in the docker-essbase GitHub repository, then you have a Docker image for an Essbase server. There are a few ways to actually run this image. Generally you will want to run the Docker Essbase server from its “compose” file. The compose file is a YAML syntax file that specifies how to tie multiple docker images into a server. In this case those multiple Docker images are the Essbase image/container itself and a Microsoft SQL Server image/container that houses the relational databases that Essbase requires.

The compose file that is part of the docker-essbase project works just fine, but there is a slightly different you can run the server that might be a good option for you or your coworkers. If you push your Essbase image up to a Docker image registry (such as Docker Hub although there are others such as on AWS or Azure), then you can make it available to your colleagues/coworkers to use without them having to clone the docker-essbase repository or build the image themselves. The compose file looks like this:

As noted in the comments for the file, this example assumes you have a .env file or have otherwise defined the variables for SQL_PW, EPM_PW, and REGISTRY. Registry is just your organization or user name on Docker Hub.

This compose file is just a little from the one in the docker-essbase project. One, it doesn’t bother to try and build the Essbase Docker image, it simply references one that you have pushed up to a registry. This example also assumes that the image name is essbase and it has been tagged as latest (at the time of this writing my latest tag corresponds to a Docker Essbase image that is patched to version 11.1.2.4.033).

The rest of the comments in the gist/code example indicate how you can run the server in detached state, follow the logs, pull up the EAS launcher, and run/login to a MaxL session.

One way you can use this file would be to push up your built Essbase Docker image to a private registry, then give access to that image to your employees and give them this compose file (or put it in a GitHub repository or whatever) and they can then start an entire Essbase server on their machine (assuming Docker is installed) with a single command.

As I’ve mentioned in previous blog posts and various presentations, this ability has been an incredibly useful development tool for me. Part of the reason that it has been useful is that I treat these servers as completely throwaway objects, which is to say, when I’m done using it, I just stop it and it goes away. The next time I start it up, Sample/Basic and friends are loaded anew as if nothing happened. So this let’s me make cube copies, mess with outlines and other things and not have to worry about messing anything up.

That said, you may want to treat your Docker Essbase server as something that’s slightly less ephemeral. You can do this (so you’re treating the container essentially like a VM), although it’s not a use case that I have a ton of experience with. If you treat your Docker Essbase container more like a VM, I’d be curious to hear what your workflow looks like and any other thoughts you may have.

Addendum

Variables

There are multiple ways with Docker Compose to specify variable values such as those used in the above file. A simple way to do so is with a .env file in the same folder. It might look like the following:

REGISTRY=myorganization
SQL_PW=AAbb11##
EPM_PW=password1

In the above and in my case, the value of REGISTRY is my organization’s Docker Hub ID. This value might be more detailed if you have a registry on another provider and you therefore have to specify a ‘fully qualified’ registry path. Additionally, the value of SQL_PW is one that I have gotten in the habit of using for Microsoft SQL Server because it requires a complex password with uppercase and lowercase letters, numbers, symbols, and length. EPM is less stringent, although you still can’t get by with just ‘password’ as WebLogic needs something ever so slightly more secure (note that the password ‘password’ is fine for Essbase itself, but the Docker Essbase image just uses the same password for both Essbase and EPM for simplicity).

Ports

The above Compose file maps only port 9000. This is the “compact deployment” port for an EPM server, when you have EAS, APS/Smart View Provider Services, Workspace and Shared Services all running in WebLogic on this one port. This means that connections to Smart View are over port 9000, Java API connections through APS are on port 9000, and more. As presently configured, you cannot connect to Essbase via embedded mode. You would need to add in port 1423 and the port range starting at 32768. You could, if you wanted to, also add in port 7001 and start up the WebLogic container and then login to manage WebLogic. Just for the sake of simplicity I have omitted those ports here (but the whole list can be found in the docker-essbase GitHub project).

Dodeca: Create and use a new Essbase MDX Selector List

The most recent version of Dodeca brought several exciting enhancements for MDX-related functionality. One of these is a new selector list based on a reusable MDX script object. Although MDX queries are probably most often associated with queries that return numerical data from a cube, they also have incredibly useful metadata capabilities that can be employed for various purposes. In Dodeca, it’s common to use a report script or member query specification to return members from an outline. For example, you might want to provide your users with a selector such that they can choose a particular product (or products) from your Product dimension in order to customize a report that they will build dynamically.

I see MDX scripts as being a natural, clean, and flexible way to populate these selectors, and moving forward I will recommend them whenever possible over the more arcane report scripts that have been around for years.

That all said, what I want to show today is the following: I’m going to edit an existing Dodeca view so as to replace one of its existing selector lists with a new list based on an MDX query.

Continue Reading…

Two of my favorite things together at last: Essbase and reddit

Just quick post today to turn your attention to the new Essbase reddit. For those of you unfamiliar, reddit is a community site for posting and discussing cool, interesting, or whatever links. I’ve had this up for a little while now and have been slowly putting in some links as time allows. A couple of you have stumbled on to it already.

I thought this might be a cool way for us Hyperion bloggers/followers/enthusiasts out new content, discuss Essbase/EPM news, and post relevant links. This is my first time moderating/managing a reddit community. If you want to help out and/or be a moderator, please message me and we can figure it out.

The idea here is not to try and suck away traffic from the technical discussion forums such as OTN and Network 54, but rather to to complement them and our blogs by having a general area for discussion along the lines of “hey, check this out!” and “I wonder what other people have posted?”

For the least part, I’ll try and post something interesting on a regular (if not incredibly frequent basis by reddit standards) to keep things fresh and exciting!

Please enjoy and if you have a cool link to share, send it to /r/essbase!

WSDL XML tweak for Essbase Web Services for C#/Service Util

I just thought I’d make a small post on an issue I have encountered with Essbase Web Services. I also mention this during my webinar on the matter. If you use the svcutil.exe program to generate a set of proxy classes based on the WSDL file for Essbase Web Services, you’ll get an error.

At least as it pertains to the DatasourceService file, you need to make a small change. Here’s the problem line:

<xs:element default="DBALL" minOccurs="0" name="cubeAccess" type="tns:CubeAccess"/>

The problem is that it’s trying to set a default value for the CubeAccess property, but DBALL isn’t defined in the enumeration for this type. Here it is from later in the file:

<xs:simpleType name="CubeAccess">
 <xs:restriction base="xs:string">
 <xs:enumeration value="NONE"/>
 <xs:enumeration value="FILTER"/>
 <xs:enumeration value="READ"/>
 <xs:enumeration value="WRITE"/>
 <xs:enumeration value="CALC"/>
 <xs:enumeration value="DESIGN"/>
 </xs:restriction>
 </xs:simpleType>

There’s no DBALL in the above. We can actually just comment out the above line in the file or delete it all together to remove the reference to a non-existing enumeration value. I haven’t seen any problems from doing this. I’m hesitant at this point to try and introduce DBALL as an enumeration value since that doesn’t technically exist in the Oracle definition of the server, and I’m not sure what the right way to go in terms of setting a default value might be (i.e., should it be NONE or READ or other?).

In any case, for the purposes of proxy generation from the Visual Studio tool, just removing the reference seems to do the trick. Here’s what the commented line looks like, just for the sake of completeness:

<xs:element default="DBALL" minOccurs="0" name="cubeAccess" type="tns:CubeAccess"/>

Just tonight I verified that this issue seems to still exist in EPM 11.1.2.3.

That all being said, it’s possible that I’m just totally missing something there – perhaps an option on the proxy generation I need to turn on or something else, but I do know that commenting the line gets me to where I can successfully use the tool. If you’re more familiar with this than I am then please let me know if you have some advice.

Essbase Java API jars in Maven

This is a bit of a repost with respect to content as I’ve already talked about installing Essbase Java API JAR files into a local Maven repo. The other day I installed the newest JAR files for 11.1.2.3 and thought I’d do a quick post. There doesn’t seem to be a “CPLD” JAR anymore – I’m not totally sure what it did but some googling awhile back suggested we don’t need it anymore. So that leaves the main JAPI JAR and the server JAR:

ess_es_server.jar
ess_japi.jar

I’ve renamed them as thus:

essbase-es-server-11.1.2.3.jar
essbase-japi-11.1.2.3.jar

Then the following two Maven commands:

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

A whole ton of output will show – hopefully some version of a sucesss message. And then this:

mvn install:install-file -Dfile=essbase-es-server-11.1.2.3.jar -DgroupId=com.essbase -DartifactId=essbase-es-server -Dversion=11.1.2.3 -Dpackaging=jar

And again a bunch of output. If you use Eclipse like I do then you might want/need to rebuild your local Maven repository index so they are picked up there, then you can add them via the GUI if you’re so inclined by just filtering on “essbase”. Add them both to your project and then go from there. You might need some other dependencies for the new Oracle Diagnostic Logging (ODL) which seems to have been implemented. I haven’t played with this much but it seems to depend on the version of the server you use. I typically use SLF4J and bridge the Essbase JAPI logger, but with ODL things might have changed a bit. I’ll post an article at some point hopefully discussing a resolution. If you bump into any issues or solutions please let me know.

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.

Hyperion Essbase rejected record automation one-liner

I’m just cleaning up some old files here and came across a post from quite some time ago that never got published. Whoops. This was before I officially released the Rejected Record Summary Java routine for analyzing/summarizing a Hyperion data load rejected record file.

So imagine row after row of something like the following:

\\ Member Ac.0170001 Not Found In Database
09 0170001 900 11 .00

\\ Member Ac.0170001 Not Found In Database
09 0170001 904 11 .00

\\ Member Ac.0170001 Not Found In Database
09 0170001 905 11 .00

\\ Member Ac.0170001 Not Found In Database
09 0170001 906 11 .00

You could run the following one-liner on it:

grep \\\\ sample1.txt | sed -e 's/\\\\ Member //' -e 's/Not Found In Database//' | uniq -c | sort -nr

And get something like the following:

24 Ac.0453902
24 Ac.0397511
24 Ac.0171026
24 Ac.0170926
24 Ac.0170126
23 Ac.0909100
23 Ac.0901100
23 Ac.0201220
23 Ac.0170326

Now, hopefully you aren’t getting any rejected records (and certainly not this many, but then again, it’s just test data), but no matter what, your Hyperion automation practices should include regularly inspecting your rejected records, if any, and this might help if you happen to work it in to some automation.

This example of course presumes your Hyperion automation server is running Unix command tools, so alternatively you could install Cygwin or something similar on Windows if you script there. And for complete platform independence, check out my Java library!

SSD Upgrade (MOAR SPACE!) – for laptops and Essbase servers

Samsung 840 Pro 512GB SSD Upgrade for my MacBook Pro

Samsung 840 Pro 512GB SSD Upgrade for my MacBook Pro

This isn’t ostensibly Hyperion related, but I’m going to find a way to tie it back to Hyperion and Essbase. My main machine these days is a MacBook Pro. I love this thing. It’s not even the newest retina model (soon…) but it’s still a beast. Originally it came with a 128GB SSD. I quickly outgrew it – mostly owing to the VMs I store locally. I bumped up to a 256GB Samsung 830 SSD and enjoyed breathing room for awhile longer. Recently, though, I have even outgrown this, owing to various VMs and other files I need on a day to day basis.

I could run my VMs and store files on an external drive, of course. It wouldn’t be too hard to just plug a USB drive. Now, I’m not lazy, but it is just one more thing to deal with (I’m sure performance would be reasonable, but SATA beats USB). This machine has Thunderbolt, but again that’s just one more external thing to plug in and the Thunderbolt enclosures for drives are pretty expensive (i.e., they make more sense for an array of drives rather than just one drive). I could even replace the optical bay (a DVD-RW drive) with a hard drive. This is very tempting – to pop in a 1 TB laptop drive in place of the optical drive. I’m not ready to go there, just yet. After some deliberation, I decided to bump up this baby to a 512 GB Samsung 840 Pro SSD. It’s a little pricey but I’m not ready to commit to a new laptop just yet, gets me the space I need right now, doesn’t entail me having to worry about an external drive, and it is fast. I cloned my existing drive over so this isn’t even new install of the OS or anything. This thing just screams.

How does this relate to Essbase? Well, let me tell you.

Disk performance affects almost all major aspects of Essbase solutions: retrieval times, calc times, data load times, and more. We [database geeks] spend countless hours optimizing solutions or designing them around performance issues. Almost every client I go to with an existing solution has a performance issue somewhere.

Now, there is definitely an art and a science to getting those dense/sparse configurations just right, optimizing load rules, calcs, and so forth. I have spent countless hours investigating, researching, and testing these settings – understanding them, talking about them, presenting on them, and most importantly, comprehensively optimizing solutions to run faster.

That all being said (and this is hardly a new or insightful thought), SSD works extremely well with Essbase. SSD speeds up Essbase for the same reasons that an SSD speeds up using a laptop or computer.

That being said, faster hard drives and faster hardware should never be used to try and paper over a fundamental design or architecture problem. SSDs are very affordable now and will continue to get more affordable. So, to sort of complement and add a tiny bit of my own insight, such as it is, to the notion of bringing in SSDs for your organization, think about it from a simple business math perspective: A is the cost of new hard drives,  B is the benefit of the increased performance, C is the cost of someone (you or a consultant) performance tuning your system, and lastly D is the benefit of that tuning.

Now, quantifying B and D is subjective. The value in your system running processes faster can be based on the aggregate improved query response times for users plus some sort of benefit from being able to load up numbers faster (or perhaps more importantly, reload numbers when things don’t tie out). Let’s be very, very optimistic and say that the benefit of D can be equal to the benefit of B. In other words, I’m going to say that you can tune an Essbase solution so well on rotational media (traditional hard drives) that it rivals SSD performance. This is, I think, being quite generous, but let’s go with it. The cost to achieve this performance benefit from tuning  (C), particularly when done by a reputable consultant, can very easily exceed the cost of the SSDs, A. Obviously there are many other factors to take into account and this is a gross simplification. But the beauty of going to SSDs is that you leave the option to do deeper performance tuning on the table.

So anyway, think about it. A lot of us just have to deal with the environment we have and be thankful we even have what we do, but in my opinion, this paradigm shift in storage is an absolute no brainer from a time and money standpoint.

Thoughts on my Kscope Session ‘Practical Essbase Web Services’

My session went alright. I asked the audience to please be gentle on account of my first time presenting the deck and the material. They were quite gentle. I’d like to give a big huge shout out to my favoritest Essbase peeps in the world on Team Kroger for all coming and cheering me on.

My big takeaway from the audience was that a demo would have been helpful. Duly noted. I didn’t think there’d be time but the session actually came in a bit under time so there’s definitely a way to retool it and include a demo with real code and a real working example. So if I run this deck again I’ll be sure to incorporate that.

Many people were also very thankful for my apparent candor about the technology. I start off with a high-level overview of the available technologies for practically getting data out of of Essbase and come to the conclusion that while Essbase Web Services have a use case, it’s not one that I will personally be likely to use. This is largely due to my large investment in the infrastructure for Saxbi Server, which is based on the Essbase Java API.

One of my more recent thoughts about EWS is that it’s probably something that is largely intended to be used internally by Oracle and was something they knew they could clean up a bit and toss over the fence for other people to leverage. As I mentioned in the presentation, I hit some bumps in my research and will hope to see them cleaned up in future releases. Now that I have names with faces of a few more Oracle folks from the conference I’ll be looking forward to providing actual feedback to them instead of just bitching writing about it on my blog.

Anyway, as promised, here is the presentation for those of you that want to skim it. The bullets might be too high-level to be incredibly insightful but if you have any questions please feel free to mail me! I am happy to help and for extensive development needs I am available to consult through my firm.  I have some related code I will clean up and get into a GitHun repository as time permits.