Webinar Tomorrow: One Stop Data Shop with Dodeca

I just wanted to plug a webinar that I am conducting tomorrow on Dodeca. I’m excited to do this webinar for a few reasons. Usually on our monthly webinar series we look at a specific feature and do a technical walkthrough. The focus of this webinar is a little different, though. This is more of a case study looking at how a business has a lot of existing processes and reports built around Essbase and the Excel add-in, but needs something more sophisticated to handle their needs, including:

  • Using Dodeca to build views based on existing Excel sheets that need to fetch data for multiple tabs and multiple data sources in one fell swoop
  • Facilitate user input directly to Essbase with commentary
  • Combine Essbase and relational data in a single view (and save user’s a trip to a tertiary system!)
  • Drill-through in Dodeca: not just from Essbase to relational but Essbase to Essbase or relational to relational
  • Batch reporting

Having consulted in the Hyperion/Essbase world for many years I can easily say that there were countless organizations with sophisticated and complicated (but tried and true) Excel/Essbase-based processes that would greatly benefit from a tool that helped automate, manage, and control things. So that’s exactly what I hope to convey tomorrow during the webinar. If you’d like to attend, please register and I’ll look forward to showing you Dodeca and taking your questions!

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…

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…

You Should Rethink Your Essbase/VBA Spreadsheets

Essbase is often described as getting its start in finance departments. It was finance’s secret weapon. There are a number of ways that Essbase tends to have evolved organically from this foothold. One common evolution is for the tool to gain critical importance to a company, and need to be more robustly managed by the IT department (in theory, anyway, I’ll save my thoughts on this common tragedy for another time).

One of the other common developments for Essbase in the finance department is that certain books/sheets evolve more and more functionality over time. They get augmented with automation, macros, VBA code modules, and more. This is all often on top of complicated formula references across sheets, and sometimes even across different books.

Continue Reading…