New Substitution Variable Methods/CLI in PBJ

Just a few additions to the PBJ (PBCS REST API Java Library) regarding substitution variables. All of the new functionality is added to the PbcsApplication interface, for now. Since variables can exist in a specific plan type, it may make sense in the future to add a new interface/implementation that models a specific plan type. Anyway, here are the four new methods for now:

	 * Gets all substitution variables in the application
	 * @return a list of the substitution variables, an empty list if there are
	 *         none
	public Set<SubstitutionVariable> getSubstitutionVariables();

	 * Fetch a substitution variable with a particular name from this
	 * application
	 * @param name the name of the variable to fetch
	 * @return the variable object, if it exists
	 * @throws PbcsNoSuchVariableException if the variable does not exist
	public SubstitutionVariable getSubstitutionVariable(String name);

	 * Update a set of substitution variables. This does not replace all of the
	 * variables in the application, it just updates the ones that have been
	 * specified in the collection (contrary to what the REST API docs seem to
	 * imply)
	 * @param variables the variables to update
	public void updateSubstitutionVariables(Collection<SubstitutionVariable> variables);

	 * Convenience method to update a single substitution variable value.
	 * @param name the name of the variable
	 * @param value the value of the variable
	public void updateSubstitutionVariable(String name, String value);

A few things to note:

  • The getSubstitutionVariables method returns a Set<SubstitutionVariable>, as opposed to a List. Since a variable should be unique with respect to its combination of plan type, name, and value, a Set makes a little more sense here because the ordering implied by a List is irrelevant
  • All methods work with/return a SubstitutionVariable object. This is a new POJO class with three fields: planType, name, and value.
  • You can fetch just a single substitution variable by name as a convenience method. Although there is a technically a specific REST API endpoint for doing so, right now it just calls the other method and filters it.
  • You can update a set of variables
  • As a convenience, you can update a single variable/value for all plan types using the updateSubstitutionVariable(String name, String value) method.

The PBJ CLI (an “über” JAR that is runnable an implements a basic CLI to PBCS) has also gotten a couple of updates to reflect the new capabilities in the library. For example, you can quickly list all variables in an app:

java -jar pbj-pbcs-client-1.0.4-jar --conn-properties list-variables --application=Vision

And get a list back:


And as an added bonus, you can even provide your own format string if you want. This might help for people doing automation and need to get the data into a particular format with having to do some weird batch/shell string tweaks:

java -jar pbj-pbcs-client-1.0.4-jar --conn-properties list-variables --application=Vision --format=%s|%s|%s%n

All For Now

These latest updates are in the 1.0.4 branch of the PBJ GitHub repository. You can clone it and build your own copy of the library and runnable CLI JAR (if you’re so inclined) by checking that out. Eventually this branch will be merged into the master, pending more testing.

Understanding the Outline Extractor Relational Extraction Tables

I was going to do a nice in-depth post to follow up on my discussion of the relational cache outline extraction method/improvements on the Next Generation Outline Extractor, but someone already beat me to the punch. It turns out that the tool’s primary author, Tim Tow, blogged about the technique and the tables for ODTUG a couple of years back.

I’ll just add on one thing that I wanted to highlight, though: the general technique behind most relational extractions from an Essbase outline is to generate a single table, with such common columns as PARENT, CHILD, ALIAS, UDA, STORAGE, CONSOLIDATION, and so on. If you think about this, it tends to imply a number of limitations that might make this technique unfeasible for you:

  1. The table can only hold one extraction at a time
  2. You can only get the member name and a certain alias table alias at a time
  3. The columns in the table are variable based on the attribute dimensions that may be associated with the dimension
  4. More than one UDA: ¯\_(ツ)_/¯

Continue Reading…

Essterm: Terminal-based ad hoc client for Essbase

Remember the last time you thought, “You know, Excel is just a little too modern, I wish I could do multi-dimensional analysis using my keyboard, in a terminal, the way the Pilgrims did it.”

Me neither.

Yet, here we are.

I was going to originally throw this over the fence release this as a bit of an April Fool’s joke, but I didn’t have quite enough time. I actually showed this off to the fine folks at my Collaborate session last month, and believe it or not, some of the people there thought it had some interesting use-cases. Continue Reading…

Next Generation Outline Extractor version 2.1.3: Relational extraction enhancements

We’ve made some enhancements to the Next Generation Outline Extractor to incorporate user feedback and requests. The main improvement to this newest release, version 2.1.3, is with the way that relational database extractions are handled. More specifically, the storing of relational credentials has been improved so that they are no longer stored in cleartext. This will lead to improved security for organizations using this functionality in their automation. Additionally, the configuration for relational extractions has been simplified a bit. There is now no longer a need to edit the persistence.xml file, rather, everything is stored in the main properties file.

As part of this  post, I want to go over how the new functionality works, including a full “soup to nuts” use case. I think a lot of people use the outline extractor for “one-off” extractions, although a lot of people might be unaware that it can just as easily be used to quite easily automate extractions.

Continue Reading…

PBJ 1.0.4 – New password options and start of CLI

The PBJ library has been getting a lot of attention lately from various developers using it to integrate with their own software and projects. Francisco Amores did a great blog post about using PBJ to help with data loading in an FDMEE project. Probably the coolest thing about his efforts is that it’s  use-case I never imagined: using PBJ in Jython to access PBCS.

One of the things that has been so great about collaborating with Francisco is getting targeted, useful, and practical comments on how he’s using the library and how it can be made better. And I have found time to make various improvements, enhancements, and fix bugs to address his feedback. This is one of the greatest things about open source software.

Continue Reading…

My Top 10 Favorite Drillbridge Features

Drillbridge is a tool with an ostensibly narrow focus – drill from Essbase/Hyperion data to somewhere else. Typically that “somewhere else” is the relational data that has been summarized to load into the cube. While the concept of drill-through is very simple in principle, Drillbridge has been extensively engineered to make take this simple process and augment it with dozens of features that enhance its usefulness.

That said, in no particular order, I thought it might be fun to point out my ten favorite Drillbridge features. Continue Reading…

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()) {


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!