Configuring Drillbridge with Financial Reporting Web Studio

Drillbridge works perfectly with Financial Reporting Web Studio – the successor to the desktop-based version of Financial Reporting (also commonly called HFR, FR). FR was stuck with a very archaic client (let’s just say it’s from around the Clinton administration), but it has revamped for the future, with a completely web-based interface now. In retrospect, and based on my interactions with the interface, I think this product overall can be thought of as gap coverage for FR users. It’s not necessarily the place you want to do new development, especially given some of the other shifts/developments in the reporting ecosystem lately. My colleague Opal Alapat has posted some really great thoughts on FR and its place in this ever-changing world, which I encourage you to read.

In the meantime, there are countless current installs of FR that organizations need to support and perhaps transition to this newer incarnation of FR. As with before, Drillbridge works seamlessly to give you and your users advanced drill-through capabilities in Smart View, Hyperion Planning/PBCS, FR, and now FR web. I found that the UI had a few quirks to it, but I’ll walk through a simple example and try to point those out along the way.

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.

PBCS has a REST API, now what?

Note: this article was originally written for an ODTUG publication, but it never wound up getting published. So I thought I would just post it here instead.

Oracle’s Planning and Budgeting Cloud Service – PBCS – is the first Hyperion product to get the full cloud treatment. In addition to Planning’s move to the cloud, it has picked up a couple of new tricks. One of these new features is a REST API. This article will give a quick background on REST APIs, some integration opportunities now available to PBCS users, and information on how the PBCS REST API can be easily used from Java.

For those that aren’t familiar, a REST API typically means a few things to developers. First of all, an API is an application programming interface. From a developer standpoint, an API gives us the ability to write programs that interact with another system in a specific way. In the case of PBCS, the API provides access to functionality such as refreshing a cube, launching a business rule, uploading files, getting member information, and more.

Continue Reading…

New Drillbridge Plus feature: fetch attributes!

Drillbridge Plus has recently gained a new feature at the request of a customer. This one is kind of interesting and required a bit of deep thinking in terms of the best way to architect it. Here’s the deal: Smart View will let you drill-through on a data value where your grid is using attribute dimensions, but it won’t pass the attribute associations as part of the request. And as it turns out, there are instances where it’d be useful to have that attribute member so you can use it to dial in the SQL query that Drillbridge creates and executes.

What to do? Ask Drillbridge to go fetch those attribute member values for you anyway! In this post I’m going to walk through a use-case showing off the new feature, how to set it up, and I’m also going to show off some recent debugging enhancements that are really useful and have been around for awhile.

Let’s start. First, consider a normal Drillbridge report definition with a simple query:

A normal Drillbridge report definition (before adding attributes)

Continue Reading…

Small update to essbasepy

Thanks to a fix contributed by a power-user, the essbasepy library (Python MaxL wrapper for Essbase) has been enhanced slightly to include a fix for previous versions of Essbase. There were cases when the library would work out of the box for 11.1.2.4 but some functions didn’t work as expected with earlier versions due to the way a data structure was changed.

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.

Small update of Hyperion Parent Inferrer to 1.0.2

Over the years I have developed a fairly robust set of Java libraries that help me work with and enhance Essbase/Hyperion functionality. One of those seemingly trivial libraries is the “Hyperion Parent Inferrer”. It’s a Java library/command-line program that can quickly parse an indented file or String and infer what the parents of members are. In the world of programming it can be nice to just have a clean hierarchy instead of fiddling around with parents/children and other things.

While the library typically processes files, I added some new functionality that I needed helps process a String into a map that can easily lookup children of parents. The updated library is on the Hyperion Parent Inferrer GitHub page. You can also find some background on the library on the README file. You might also want to check an earlier blog post on the library, if that’s your thing.

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.

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…