Category Archives: pbj

PBCS Scripting with Groovy using the PBJ REST API Library

I was talking to a colleague the other day that wants to do some scripting with PBCS using Groovy. Of course, since PBCS has a REST API, we can do scripting with pretty much any modern language. There are even some excellent examples of scripting with PBCS using Groovy out there.

However, since Groovy runs on the JVM (Java Virtual Machine), we can actually leverage any existing Java library that we want to – including the already existing PBJ library that provides a super clean domain specific language for working with PBCS via its REST API. To make things nice and simple, PBJ can even be packaged as an “uber jar” – a self-contained JAR that contains all of its dependency JARs. This can make things a little simpler to manage, especially in cases where PBJ is used in places like ODI.

For this example I’m going to take the PBJ library uber jar, add it to a new Groovy project (in the IntelliJ IDE), then write some code to connect, fetch the list of applications, then iterate over those and print out the list of jobs in each application.

Continue Reading…

Milestone for PBJ (PBCS REST API Java Client) Project: first outside contribution

Just a quick note on a fun milestone for the PBJ project: the first code contribution from an outside developer has been merged into the codebase. This is one of the things I love about open source. The PBJ project has a very flexible license (Apache Software License 2.0) and as such it is quite business friendly.

Sometimes when an open source project doesn’t do what you need it to do at a given point in time you have to roll up your sleeves and add some code yourself. And that’s exactly what one of the users of the library needed when they added some new methods to download large files from PBCS. So there are a couple of new methods for handling that use-case – and now everyone gets to benefit from it. This is exactly what I had envisioned when I created this project: a high-quality codebase with complete documentation, unit tests, and support for some of the exciting REST APIs being provided by modern Oracle technologies, and a chance to enjoy living one of my favorite quotes: a rising tide lifts all boats.

Interesting Time Period Conversion with Drillbridge/PBCS

I recently helped a customer with their Drillbridge installation/configuration for PBCS that had an interesting time period conversion issue I wanted to write about.

Drillbridge helps convert a given POV into a SQL query, webpage link, MDX query, or whatever you want (such as with a custom plugin). Out of the box, Drillbridge contains a number of commonly-used convenience functions for easily converting months to numbers (as well as other functions). You can do this in SQL too but it seems to almost always be a little “cleaner” to let Drillbridge do it for you, especially when it comes to upper-level drill-through.

Interestingly enough, a client has an interesting but not incredibly uncommon fiscal calendar where February is actually period 1,  March is 2, and so on. In this calendar, January is actually period 12. But the Drillbridge calendar conversion functions usually return the common month numbers. What to do? Just adjust the expression a little to check for January specifically, otherwise convert the month and subtract one. For example:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN ({{"name":"Period","expression":"#Period == 'Jan' ? 12 : #monthAbbreviationToDigit(#Period) - 1","drillToBottom":true,"sampleValue":"Q1","quoteMembers":false,"suppressParentheses":true,"overflow":"","overflowAt":0,"flags":""}})

There are a few variant methods to handle this, but this one is pretty straightforward and clean. This token actually also handles upper level drill (such as from member Q1, Q2, and so on), so the query predicate to use is a SQL IN clause, to accommodate multiple values.

Now when we drill on member January, we get this test query:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN (12)

And if we drill on Q1, for example, we get this:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN (1, 2, 3)

You might have been expecting to see 12, 1, 2 there but it’s actually right since Q1 contains February, March, and April – so everything is mapping as expected.

I’ve been pretty happy over the years with how the original Drillbridge expression/token concept has been able to accommodate some tricky use cases, although this one is relatively straightforward. It’s also nice to be able to write a bit of a “pure” query that doesn’t have to join against a calendar table just to get the right dates. This is just one of the things that makes Drillbridge, in my opinion, a true turnkey drill-through solution.

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 pbcs-client.properties list-variables --application=Vision

And get a list back:

ALL,NextPd,FY15
ALL,CurrPd,FY14

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 pbcs-client.properties list-variables --application=Vision --format=%s|%s|%s%n
ALL|NextPd|FY15
ALL|CurrPd|FY14

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.

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!

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.

PBJ PBCS Client GitHub repository

For those of you wanting to play with PBJ, the full code-base is available over on GitHub in the PBJ repository. Just to be clear, this is for people that want or need to edit, view, or make contributions back to the code. You don’t need to do anything with Git/GitHub if you just want to consume the library out of Maven or otherwise include the JAR file in your projects.

That said, there are a few things missing from the library that are available in the PBCS REST API, such as some items having to do with Planning Units, and some new REST API methods that are going to be available soon (having to do with data slices). But in the meantime, enjoy!

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.