Category Archives: java

Essbase Renegade Members Revisited

For some reason the other day I was thinking “Whatever happened to that renegade members feature?” So I did some digging.

Renegade members, by the way, refers to this concept where instead of a data record being rejected, you can map it to some other member. Other names for this feature might have been “shovel members”, but renegade members sounds cooler. That said, it’s a feature with a cool name but an apparently terrible publicist.

Renegade members were blogged about as early as a few years ago, such as on Cameron’s blog (during the 2013 OpenWorld), in Russian (apparently), and even over at Rittman Mead’s blog (before Mark spent his days trying to get tea kettles to work with the internet, but I digress).

But there’s a a curious lack of information on renegade members since then. There is, however, just enough information on the internet to piece this together. There’s a little documentation about renegade members over on the official documentation. Just as important (for my purposes), there are two methods relating to renegade members that are in the Essbase JAPI Javadoc.

Continue Reading…

Vess + ODI to extract Essbase metadata

Well, apparently it’s Friday Fun Day over here in Seattle. I was going to head up to the mountains for the weekend but plans changed. So how about a little frankendriver Vess update with some ODI goodness thrown in?

Vess has some really interesting data integration possibilities that I’ve mentioned before, one of which is being able to drop it into Oracle Data Integrator and use it as you would any other JDBC driver. I hadn’t really tested this out though, until yesterday. It turns out that it works surprisingly well.

Continue Reading…

Creating Windows symlinks for Java installation

This is a note about configuring Java on Windows that is mostly for reference later. Usually when I set Java up on a Windows machine, one of the first things I need to do manually is make sure that JAVA_HOME is set and that the PATH variable contains the folder with the Java executables. The variable on PATH is typically a “bin” folder, whereas the JAVA_HOME variable is up one from that, or up a few more directory levels if it’s a JDK.

Recent versions of Java on Windows have changed the way that Java is installed. Now it creates a C:\ProgramData\Oracle\Java\javapath folder that is added to the PATH, then this folder contains symlinks to for each of java.exe, javaw.exe, and javaws.exe (the main Java executables) over to wherever they are installed on the file system (typically in C:\Program Files or perhaps C:\Java). This is a nice idea so that people are constantly fiddling with their PATH to configure it to a specific versioned Java install (complete with major, minor, and build numbers).

Yes, Windows supports symlinks. While more commonly seen or known in Linux filesystems, Windows’ NTFS filesystem has had support for file links for quite some time. They’re just not used a whole lot by end users themselves.

In any case, unfortunately, this install/configuration process doesn’t always seem to work in terms of creating/updating the symlinks. But they can be fixed pretty easily. Just navigate to the javapath subfolder:

Viewing symlinks in Java folder on Windows

Viewing symlinks in Java folder on Windows

Then run these three commands, altered of course to match up with your actual version of Java:

mklink java.exe "C:\Program Files\Java\jdk1.8.0_102\bin\java.exe"
mklink javaw.exe "C:\Program Files\Java\jdk1.8.0_102\bin\javaw.exe"
mklink javaws.exe "C:\Program Files\Java\jdk1.8.0_102\bin\javaws.exe"

Playing with the Thriller MDX/JDBC Driver in Drillbridge

Last week I talked about a new side project, which is a JDBC driver called Thriller for executing MDX queries against Essbase and mapping the results back into a normal relational database. And at the time, I said that this driver had some really interesting use cases, such as in Dodeca, Drillbridge, ODI, and other tools that work with JDBC drivers.

Speaking of Drillbridge – in the very near future I will be sharing Drillbridge’s official future direction, which I think is really exciting, but more on that later. In the meantime, let’s drop this baby into Drillbridge and see what happens!

The following walkthrough of using Thriller with Drillbridge will show off some features that are only available in the licensed version of Drillbridge, although this should in theory work with Drillbridge Community Edition (the free edition of Drillbridge), assuming you have the Thriller driver JAR file.

Continue Reading…

Running MDX queries through a JDBC driver (for fun?)

So there I am, sitting in front of the Alaska Airlines gate at Boston Logan airport, waiting for my flight home to Seattle. It’s not a particularly glamorous terminal – the divorce from Delta hasn’t been too kind to Alaska at BOS; Delta seems to have kept the house and kids while Alaska microwaves Lean Cuisine on a futon in its bachelor pad…

As I’m pondering why there are white rocking chairs in the terminal, my phone rings with a familiar name: Mr. Brian Marshall. We catch up and exchange pleasantries before pivoting over to more important matters (all things EPM of course!).

Brian: “So… Vess.”

Jason: “Oh boy…”

So we get to talking about accessing Essbase data through a Java database driver, á la Vess. And we get to talking about running MDX queries and dumping the output – á la Camshaft.

And as the talk goes on I end up saying something stupid like this: “You know what might work? Jjust pass an MDX query through the driver over to Essbase and map the output to a fake table… It’d be like an unholy combination of Vess and Camshaft. You could probably knock it out in a day or two.”

And at that moment I knew I wouldn’t be able to resist opening my laptop for the five plus hour flight home. Continue Reading…

Oracle Open World 2016 Recap

As I mentioned a week or so ago, I made a last minute appearance at Oracle Open World this year. It was my first time attending and presenting at OOW. I actually didn’t catch too much of the conference as I only flew in on Wednesday and flew out on Thursday. Nevertheless, I had a bit of a whirlwind experience, but a very good one. While I hadn’t planned on it (I’m more of a Kscope guy), I am now looking forward to attending Open World next year.

As for the presentation I was part of, I think it went pretty well. Many thanks to Gabby Rubin of Oracle for coming up with the idea for the presentation and facilitating it. The presentation was on “Essbase Tools and Toys” and was meant to highlight, at a high level, some of the interesting things that folks such as myself are doing that involve the Essbase APIs or otherwise work with Essbase. The presentation discussed items created by me, Tim Tow, and Harry Gates. Additionally, Kumar Ramaiyer (also from Oracle) talked a bit about what’s coming with Essbase Cloud Service (EssCS).

Continue Reading…

Oracle OpenWorld 2016 Presentation: Essbase Community Toys and Tools

I am very pleased to mention that I will be making a last minute appearance at Oracle OpenWorld this year! I am going to be presenting with Gabby Rubin, and Tim Tow about various free tools that exist in the Hyperion/Essbase ecosystem. The presentation is on Thursday, September 22nd, 2016, at 12:00pm at Moscone West. The presentation ID is CON6489.

For my part of the presentation, I am going to talk about a handful of tools I have personally created over the years. This will likely include Cubedata (a tool for generating large quantities of data to help test Essbase performance), Camshaft (a tool for running MDX queries and outputting the results to a text file), and Vess, a highly experimental/innovative JDBC driver for Essbase that provides a functional facade for Essbase servers/cubes including access to outline data, cube data, data loads, substitution variables, and more. The goal of the presentation will be to quickly inform intrepid Essbase/Hyperion administrators and developers about some of the interesting third-party functionality they might find useful and improve productivity.

 

Advanced integration with PBJ Java PBCS REST API library

This week’s blog posts are all about the upcoming Kscope16 conference and relate to the presentations I’m part of. This year I am co-presenting with Cameron Lackpour on on-premise Planning versus PBCS and talking about some different use cases. My particular focus for this presentation is how you might use the PBCS REST API with Java.

Over the last year I have put together a Java library that works with the PBCS REST API. It has the following characteristics:

  • Open Source (Apache Software License version 2.0)
  • Doesn’t depend on any Oracle libraries or code
  • High-quality, readable, fluent API

In my opinion, all of these goals have been met. Additionally, as of today, PBJ (PBCS Java Client) is available in Maven Central. What this means is that if you or your team programs in Java and use Maven for dependency management, you are just a few clicks away from being able to use this library.

For an overview of why you might want to use PBJ and how it compares to other scripting languages you might want to use instead (such as Groovy, Python, and more), come check out the presentation!

Today, however, I want to show how easy it is to incorporate PBJ into a Java program and do something quasi-practical. So the rest of this article will be oriented towards programmers, but for those of you that have employees or teammates that would be more likely to do the programming aspects of things, keep them in mind and send them a link.

First, let’s assume we’ve already created a new empty Maven project in Eclipse. Your experience will vary if you use IntelliJ IDEA or some other IDE. In this example I happen to be using Springsource Tool Suite (STS), which is pretty much the same as Eclipse.

pbj-up-and-running-01-empty-project

Next we need to open up our pom.xml (project definition file) to add a new dependency. You can do this manually by editing the XML file itself, but there’s a nice enough GUI in Eclipse that makes things even easier:

pbj-up-and-running-02-pom-editor

Next we need to go find the PBJ library. As I mentioned earlier, PBJ is available in the global Maven Central repository. If you have Maven set to update its index periodically or upon startup of your IDE, then you should be up to date and can find the PBJ library. As of right now the version of PBJ is just 1.0.1.

pbj-up-and-running-03-pbj-maven-dependency

Select the library, click OK, and then save the file. The PBJ library and its dependencies are added to your project.

pbj-up-and-running-04-pbj-added

Now we can create a new main class to test things out. At this point it’s just life as normal for the Java developer:

pbj-up-and-running-05-new-main-class

Just for good measure (and tradition!) let’s put in a simple code to print out “Hello world”, and run it. Note the output in the bottom middle pane:
pbj-up-and-running-06-hello-world

At this point we have project setup, we have all of the necessary PBJ files (and some additional transitive dependencies), and we are ready to write some Java code that uses methods in the PBJ library. The code to write is shown below:

pbj-up-and-running-07-refresh-cube

In the code you can see the following happen:

  1. A connection details object is created
  2. A connection to PBCS is made
  3. Ask for the list of available apps
  4. Print out the list of available apps
  5. Get a reference to a particular app (“Vision”)
  6. Call the refreshCube method on the app reference to refresh the cube
  7. Print message after cube refresh

It’s hard to imagine this code being much simpler. If might look like greek if you’re not familiar with programming or Java, but to a Java programmer, this will be readily comprehensible and its intent obvious. PBJ supports most of the REST API – importing data, metadata, business rules, and more.

To see a specific use-case and hear wry commentary from myself and Cameron, please swing by our presentation. We’ll cover an example of PBJ (don’t worry, it’s higher level than this!) but more generally some facets of administration of on-prem versus PBCS will be discussed as well. I think the presentation will really appeal to many different user groups.

Introducing PBJ: The PBCS Java Client Project

I’d like to introduce the forthcoming availability of a new open source project called PBJ (or PB&J if you want): A Java client for PBCS (Planning and Budgeting Cloud Service) that serves as a clean, robust, and rapid way to work with the PBCS REST API in Java.

The PBCS REST API provides similar functionality to the EPM Automate tool and can perform such actions as exporting data, adding members to an outline, running business rules, and more. The REST API is an exciting development as Planning continues its march to the cloud, because it will allow integration from many languages and tools.

Consuming a REST API isn’t necessarily hard, but there are a lot of moving parts – connecting, managing credentials, invoking the right method, parsing the output, and then deciding what to do with it. Most of this is pretty boilerplate code that can get verbose pretty quickly.

Therefore, I thought it would be nice if there were a high-quality library that could easily be used and reused. This is where the PBCS Java Client – PBJ – comes in: it is a freely available library that is released under the Apache Software License. The ASL is a very generous license that essentially means you can do anything you want with the code. My hope is that this library becomes the de facto client for using Java to work with the PBCS API.

As an example of how PBJ makes things simple, consider the following example of connecting to a server and running a business rule:

PbcsClient client = new PbcsClientImpl(server, identityDomain, username, password);
PbcsApplication app = client.getApplication("Vision");
app.launchBusinessRule("AggAll");

Here’s a small example of checking the status of a job:

PbcsClient client = new PbcsClientImpl(server, identityDomain, username, password);
PbcsApplication app = client.getApplication(appName);
PbcsJobStatus jobStatus = app.getJobStatus(558);
System.out.println("Job status: " + jobStatus);

Three lines of code and we have connected to our PBCS server, grabbed a particular application, and then invoked a business rule on it. At the moment the API supports running business rules, exporting data, refreshing cubes, downloading files, and more.

PBJ is under active development and will be released in the near future. Contributions from others are entirely welcome. Not every PBCS feature will be supported on day one. Please let me know if you’d like to contribute to or test out the API.

Database stats and outline viewing with Vess

I had a little bit of time this weekend and dusted off Vess for some updates. I was able to greatly enhance the functionality and perhaps more important, widen the number of use cases that it could be used in.

I already wrote about a use case previously: using SQL to create a substitution variable. Vess supports fully symmetric substitution variable updates. That is to say, you can create, read, update, and delete substitution variables on your Essbase server just by changing the data in the proper table. So just to elaborate on my thoughts on the previous post, I think there are some nice use cases here. Of course, there’s nothing wrong with MaxL, but if you have an instance where you are dumping substitution variables via MaxL and scraping the ASCII art off of them in order to get to the real variable values, Vess offers a cleaner approach.

As of now, Vess also provides a window in to a whole slew of properties on various Essbase objects: server, application, and database. Here’s a view of some database properties (as shown in a generic JDBC GUI with Vess configured as a driver):

Using Vess to view Essbase database properties for Sample.Basic

There’s a use case here too. I know of more than a few people that are dumping database stats using MaxL (such as to monitor a database/server stats over time).  So instead of running a script, dumping to a file, parsing that out (or whatever), you could slam the data directly into SQL (using ODI or just some tool that can execute SQL statements).

Next up, Vess models tables for the various dimensions in a cube. This is not unlike how ODI would create a model of a cube: one table per dimension. Here’s a view of some information from Sample/Basic:

Using Vess to view outline information

Outline functionality in Vess is not symmetric: you can read but updates are disabled for now. The use case here is that you could pull outline information without needing to use ODI (or something else) and possibly if you are using the Essbase Outline Extractor in your automation. Since Vess is already a fully functional SQL database, you could pretty easily do some interesting things like just grabbing all of the level-0 members in an outline (“SELECT * FROM SAMPLE.BASIC_DIM_MARKET WHERE LEVEL = 0”).

For now, Vess supports data loads to a cube (look mom, no load rules!). I wrote about this before too. Data reads are turned off for now (as in, you can INSERT but SELECT always returns nothing). It’s a bit of an interesting issue when you want to map this into a relational model.

Lastly, I started writing up the ODI technology for Vess. I think this is an absolute win for Vess because it would mean that you could build a nice clean technology/Knowledge Module that doesn’t have to use a bunch of Jython to glue things together: you can treat everything as a vanilla JDBC database model. No idea when this could be ready but you can rest assured that I’ll blog about it.