Category Archives: projects

PBJ (PBCS REST API Library for Java) Updates

I have recently gotten quite a bit of feedback from people using the PBJ library to consume and work with the PBCS REST API. This has resulted in a few fixes and improvements. First of all, file uploads and downloads are now working. The code has been in for awhile but wasn’t merged to the master branch.

Second, I have been working with the very talented Francisco Amores to help integrate the PBJ library with FDMEE so that it can be used there in various integration scenarios. So this is a really cool usage of PBJ where the library is being dropped in to FDMEE/ODI and utilized with a very simple Jython script.

To help with this scenario, I added a new compile option to PBJ that allows it to be packaged up into a single “uber JAR” – meaning a Java JAR file that contains all of its dependencies rolled into a single library. This makes it a little easier to integrate and drop-in to other systems, instead of having to worry out additional JAR files.

We had to make a couple of other tweaks to the way the library is packaged in order to make it specifically work in FDMEE/ODI, due to a conflicting underlying library. This is kind of a classic Java class loader problem, because what happens is that the two different versions of the class are both available to be loaded, and the older version of the class gets loaded but that class doesn’t have a required method, so a “no such method found” exception is thrown. But by renaming the package/method when it’s compiled, we can get around it and make it really simple. The PBJ GitHub page has some more info on how to compile this.

I think in the near future Fransisco will be blogging out this really interesting integration scenario, so stay tuned!

Showing off the power of Drillbridge query translation

Lately I have been working on new materials and demo ware to help show off the power, flexibility, and sophistication of both the Dodeca Spreadsheet Management System and Drillbridge/Drillbridge Plus. I came across a really great Drillbridge mapping example today that I hadn’t specifically solved before, but with a little creativity I was able to write the proper Drillbridge query and get exactly what I wanted.

Consider an Essbase cube with the following dimensions:

  • Years: FY15, FY16, etc
  • Periods: Periods/Quarters/Months
  • Scenario: Actual, Budget
  • Departments: balanced hierarchy with four levels
  • Location: Total/Division/Store
  • Measures: Ragged hierarchy with accounts at level-0

For this post I am going to design a Drillbridge query that maps from this cube back to its related relational data, with the additional wrinkle that we want upper-level drill in several dimensions, including one where the dimension in the cube is represented by two different columns in the source data.

Continue Reading…

Deleting multiple files from PBCS using PBJ client

Earlier in the week, my archnemesis colleague Cameron Lackpour hosted a guest blog article by Chris Rothermel with a trick for deleting multiple files from PBCS using the epmautomate tool. The basic idea is that you can use the listfiles command to export the list of files to a temporary file, then use some batch scripting to iterate over every line in that file, then call epmautomate to delete the specific file. It’s a good example that will undoubtedly come in useful for many people.

Upon reading the article I thought it would interesting to do the same thing, but using the PBJ library. The PBJ library is an open source, 100% Java library for interacting with PBCS via its REST API. It can easily be dropped in to enterprise Java projects by including its dependency in your Maven configuration file (if you’re so inclined). The PBJ library is also used in at least one major piece of software: it’s the library that allows Drillbridge to perform upper level drill from PBCS to a relational database.

That all said, one of the nice things about having a domain specific library in a high-level language such as Java is that it is sometimes very easy and straightforward to implement functionality that doesn’t come out of the box. This doesn’t make it better than the batch scripting technique, just different.

Here’s the whole code file:

package com.jasonwjones.pbcs.misc;

import com.jasonwjones.pbcs.PbcsClient;
import com.jasonwjones.pbcs.PbcsClientFactory;
import com.jasonwjones.pbcs.TestHarness;
import com.jasonwjones.pbcs.interop.impl.ApplicationSnapshot;

public class DeleteAllFiles extends TestHarness {

	public static void main(String[] args) {
		PbcsClient client = new PbcsClientFactory().createClient(createConnection());
		
		for (ApplicationSnapshot snapshot : client.listFiles()) {
			client.deleteFile(snapshot.getName());
		}
	}

}

Most of this is pretty standard boilerplate Java. You can that after we setup a PbcsClient instance, we can very easily get a list of files (using listFiles()), and then iterate over those. To delete a file, we can use the deleteFile(String filename) method and pass in the actual file name from the “snapshot” object (which also contains a little bit of other information).

That all said, as I’ve written before, the best tool for the job is the one that fits in to your environment the best and is easiest to maintain. If you’re doing much in the way of batch scripting, then epmautomate is probably going to fit into your environment the best. If you’re writing an enterprise app in Java, you’re going to absolutely want to use the PBJ library rather than try and execute shell commands.

Top Posts of the Year 2016

Well, 2016 is almost behind us. I haven’t done this before but given that I’ve been doing a fair bit of blogging this year, I wanted to point out the “top posts of the year” on ye olde Jason’s Hyperion Blog. The subjects are diverse (as far as a Hyperion blog goes I suppose) and I think are an interesting reflection of what things people are interested in. Starting with the most popular:

Running MDX queries through a JDBC driver (for fun?): I got a lot of feedback on the MDX over JDBC franken-driver in JDBC. In retrospect, I think this goes to show how rich, diverse, and challenging the world of data integration around Essbase can be. People – developers, consultants, users, whoever – are constantly spending time, energy, and money getting data in and out of their EPM systems. The Thriller MDX-over-JDBC driver hit a real chord with some people that see it as a way to bridge the gap between EPM and other systems.

Drillbridge acquired by Applied OLAP: Probably the biggest news for me this year. Applied OLAP acquired all of Drillbridge (as well as myself) and added it to their portfolio of products, including the Dodeca Spreadsheet Management System, Dodeca Excel Add-In for Essbase, and the Next Generation Outline Extractor. Recently I announced that the enterprise/supported version of Drillbridge was officially named Drillbridge Plus and offers many compelling features, such as upper-level drill support from PBCS.

Kscope16 sessions I’m looking forward to: Interestingly, people were very curious as to what sessions I planned on attending at Kscope16. I’ll be sure to post thoughts on Kscope17 sessions when the time is right. I’ll have a single presentation at Kscope17, which will focus on “demystifying the PBCS REST API”. I hope it’s a crowd-pleaser that people will find useful.

Dependent Selectors in Dodeca: I blogged extensively about Dodeca this year, and apparently this was one the most popular article. Dependent selectors are a great feature in Dodeca that allow for narrowing down or otherwise dynamically generating the selection values for a user. For example, choosing a state could cause another selector to narrow its list of cities to just those in the given state. I’m both surprised and not surprised that this is the most popular Dodeca article. I think it’s cool because this is the type of feature that really enhances the user experience by respecting their time and making a system easier to use.

Data Input with Dodeca, part 1: Dodeca is great for providing a structured way to input data into a cube that is incredibly more robust than “we do lock and sends”. This was the first part in my data input series (six articles!) that covered inputting to Essbase, relational datasources, both at the same time, commentary, and more.

Camshaft MDX tool updated and available: Again with the MDX/data integration theme, people were very curious to find out more about a command-line tool that helps convert MDX queries to useable data files.

Essbasepy updated for Python 3: Surprisingly (to me), people the article on the Essbasepy library caught a lot of people’s attention. A lot of people are using Python to do integration/automation, and Essbase is definitely a part of the picture.

TBC Files for Bankruptcy: My tongue-in-cheek look at the woeful situation at everyone’s favorite beverage company!

Drillable Columns in Drillbridge: Lastly (but not least), one of my favorite features in Drillbridge and I think one of the standout features that you get when it comes to drilling into a web browser instead of a tab in your workbook: the ability to drill from a drill. With drillable columns, you can specify a subsequent view to drill to and the POV of the row (the global POV plus the key/values from that row) will be used to execute it. Many organizations are using this to drill into further/related journal detail, PDF files of invoices, and more. It’s a great feature!

Well, that’s the highlights from 2016. I’ll be looking forward to another productive blogging year with all sorts of exciting things regarding Dodeca, Drillbridge, the Next Generation Outline Extractor, Kscope17, and even a few secret projects I have been working on. Happy new year!

 

Drillbridge Update: Officially Announcing Drillbridge Plus

It has been awhile since an official post on Drillbridge, so today I am happy to say that there has been a lot going on with Drillbridge behind the scenes!

For those of you not familiar, Drillbridge is an innovative software application that runs as a service and makes it very easy to implement drill-through on Essbase cubes from Smart View, Hyperion Planning (including PBCS, including drilling from upper level members!), and Hyperion Financial Reporting. It accomplishes this by offering a robust and flexible way to translate a given cell’s point of view into a SQL query that it then executes and presents to the user. I have blogged about it extensively and presented on it at multiple conferences. In fact, during both of my Kscope presentations on Drillbridge I did a live demo starting with literally nothing but an Essbase server and relational table and then proceeded to download the zip file containing Drillbridge, install it, configure it, and use it to perform an actual drill-through request from Smart View in less than 15 minutes.

Over the past few years, Drillbridge has been a really great solution for many companies because it’s non-invasive (keep your existing cube and automation), flexible (drill to bottom, drill between columns, automatic hyperlinks, formatting, and more), offers an “insanely fast development time”, and works with most relational database technologies. The number of companies that have installed and deployed Drillbridge is absolutely staggering to me. I get emails almost every week from people about how easy it is to use. Many of the emails mention that they downloaded Drillbridge earlier in the day and go it working in a very short period of time. I never get tired of hearing that.

Versions:  Drillbridge Community Edition & Drillbridge Plus

Drillbridge started off life as a totally free piece of software, and to this day there it is still available in a free form. This edition is now called Drillbridge Community Edition and it can be downloaded from the Applied OLAP website. Later on, a licensed version of Drillbridge was offered for companies that wanted additional features, and usually more importantly, came with official software support/maintenance. This version was called Drillbridge Enterprise; this version has been renamed to Drillbridge Plus. Besides being officially supported by Applied OLAP, Drillbridge Plus has numerous features that the free version doesn’t have. This includes advanced paging/caching options, automation integration, PBCS support, custom plugins, and more. It’s a really great piece of software with some really powerful capabilities.

Future of Drillbridge

Drillbridge has an exciting roadmap (that I’m looking forward to blogging about more in the future) along with its sibling software applications at Applied OLAP, including the Dodeca Spreadsheet Management System, Dodeca Excel Add-In for Essbase, and the venerable, completely free Essbase Outline Extractor. We are dedicated to making Essbase (and the lives of people in the greater Essbase community) better. Please do not hesitate to contact us for additional information.

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…

Update to Thriller MDX over JDBC driver

I made a few adjustments and fixes to the experimental Thriller MDX over JDBC driver I have been playing with off and on. As a quick recap, Thriller is a normal JDBC driver that essentially passes MDX queries straight through to an Essbase server, and then maps the results into a normal JDBC ResultSet using a set of provided “hints” that tell it how to make George Spofford cry flatten the results.

There were a couple of issues related to how queries with various CrossJoins were handled that should now be fixed. Additionally, there are now a couple of new options to provide more configurability over how tuples are split or joined together. Things are definitely getting interesting for this concept.

Continue Reading…

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…

New Indenting Options in Next Generation Outline Extractor writer

Besides talking about and working on all things Dodeca Spreadsheet Management System, Dodeca Excel Add-In, and Drillbridge, one of the other things I am helping with these days at Applied OLAP is continuing work on the Next Generation Outline Extractor.

Most readers of this blog are probably familiar with the outline extractor. Almost everyone I know in the EPM world uses it or has used it in the past. It is an incredibly popular tool and I am very proud to contribute my efforts to making it even better.

Continue Reading…