New webpage for Essbase Java API evolution

A fair bit of my job is dealing with and building solutions around the Essbase Java API. For many years, the Java API has been the premier way to programmatically work with Essbase (compared to say, the C and VB APIs, which have fallen out of favor). As part of this development work, it’s often important to see when (in terms of version) a certain class, method, interface, or other object has been added, modified, removed, or deprecated.

As a bit of a side project, I have been working with a library for comparing Java JARs to each other (japicmp). By processing and interpreting the results of just about every single Essbase Java JAR from 7.0.1, through the 9.x series, multiple 11.x’s, and finally to version 12.2.x, I have come up with something of a master table that shows all of these changes. You can view the initial results of the Essbase JAPI JAR evolution analysis. I’ll probably refresh this and enhance the output as new library versions become available or as I determine that additional insights become useful.

Screenshot from the Essbase Java API evolution analyzer

Continue Reading…

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…

People really hate VBA

Stack Overflow did a really great article on the most disliked programming languages that I thought was really interesting, with my own little Essbase twist on it. On Stack’s jobs site, they let people specify which technologies they’d rather not work with. Tops on the list are Perl, Delphi, and VBA. PHP and Objective-C are next but they don’t have near as much dislike as the top three.

So, why all the hate? To me, these all have intuitive explanations, and at least in the case of Perl and VBA, I think it has to do with the context in which “projects” with these technologies occur. Perl and VBA are frequently used as quick and dirty scripting languages to automate some process. And often these one-off automation solutions tend to grow. I could be charitable and say that these solutions grow “organically” but it’s probably more often the case that they grow haphazardly – which is perhaps also being charitable.

I think people are indicating they dislike these languages because they just don’t want to work on them and don’t have good experiences working with their sprawling, complicated, hard to debug, and incredibly sparsely documented codebases. Briefly, and skipping down the list to Objective-C, I think that’s indicated as disliked mostly because it is “out of favor” with respect to the Swift programming language. The iOS ecosystem is incredibly fast-paced and people seem to need to aggressively keep their skills fresh.

As it pertains to Essbase, I couldn’t help but think of my own corporate and consulting experience when it comes to VBA solutions. I can’t tell you how many times I got a call because there was a problem with some workbook that had some VBA code on it. It can be incredibly frustrating (and in the case of bringing in a consultant, expensive) to debug, fix, and enhance these solutions.

Quite simply, while the VBA-based solutions provide something valuable (automation, saved time, etc.) they become an absolute albatross in finance departments and make upgrades/migrations just that much more difficult. That’s one of the reasons that I think Dodeca is such an effective solution in numerous cases where VBA was used, because many of the things you needed VBA code for are just inherent features of the product, with zero code at all.

For example, without a single line of code, Dodeca can connect to Essbase, pull members from an outline and present them as dynamic selections to users, retrieve multiple cubes into multiple ranges on multiple tabs in a workbook, send data back to Essbase, and run calculation scripts. These are features that typically make up pages and pages of low-quality, low-value VBA code and often necessitates the continued usage of the old, unsupported classic add-in.

Dodeca 7.3 Workbook Scripts Enhancements Overview

Today I’d like to continue on the theme of new functionality in the latest release of Dodeca, version 7.3 that came out some time ago. I talked about the new Outline Viewer functionality and MDX enhancements already, today I wanted to give a brief overview of some of the new functionality in Workbook Scripts (WBS).

If you’re not familiar, Workbook Scripts are optional scripts that can be added to views that provide advanced functionality inside of views. You can think of it as a modern, more maintainable analog to VBA, optimized to work in a world of Essbase-based data on spreadsheets. Dodeca 7.3 included some new methods and functions. There’s also updated Dodeca 7.3 WBS documentation available on the web.

New & Updated Procedures

CopyFromBinaryArtifact

The new CopyFromBinaryArtifact method is potentially the most interesting new method to show up. There are overloads for copying a whole sheet or copying a range from a particular binary artifact (i.e. a completely separate Excel sheet than the current view). So this potentially allows organizations to centralize some information, formatting, data, or other stuff into a central sheet, then refer to it. I could see people using this to store small amounts of data that they want to reuse in a view (thereby obviating the need to put the data in a bespoke relational table/cube), or copying some common formatting elements into a new view. So, this new method potentially opens up some significant maintenance savings and other possibilities in a lot of cases

OpenApplication

OpenApplication can be used to launch a different application instance from the current one. This can be useful for user convenience, where instead of telling the user that they need to go to a separate app, you can launch it for them.

SaveWorkbookAsCsv

The SaveWorkbookAsCsv method is pretty much what it sounds like. In addition to being able to save the workbook as a CSV file using the UI, you can perform this via WBS. I see Dodeca used in many cases providing some quasi-ETL functionality, or being used for large data dumps, so this could be handy. For example, maybe you want to allow users to drill into relational data and then automatically download it to their desktop.

AddDataCache: EssbaseMDXScript

There’s a new EssbaseMDXScript overload available as part of AddDataCache. The Data Cache functionality is really handy in many instances, especially where you want to execute an “expensive” (in terms of query time) query but then be able to do multiple things with the data without having to re-run the query. Now you can cache data based on an MDX query in addition to the normal options with report scripts, SQL, and more.

SendEmail (new Zip options)

SendEmail gained a couple of new properties to zip attachments. This method is already used quite heavily to have Dodeca generate and email large, complex books of reports (Excel and PDF) and mail them around. This is quite frequently used as part of batch reporting. This functionality is used so extensively by so many organizations that it is now useful to compress the output into a single file and attach that to the email. Now with a single option you can do exactly that.

Miscellaneous

There are a few other tidbits here and there like a new attributes property on the EssbaseReport method, some new convenience functions like getting the first column letter, last column letter, checking if a binary artifact exists, if a view is shared, and more.

Read the Release Notes!

The release notes that ship with the Dodeca distribution contain pretty detailed notes on all of the new functionality and features, so be sure to peruse those for other new functionality you might be interested in. If you are planning to use some of these new procedures/methods, please let me know if you do and how useful you find them, I’d love to hear about it.

 

Dodeca: Turn on Auto Build button by default or hide it entirely

A customer request came in the other day asking if it’s possible to turn the Auto build button on by default. You can see this button next to the green build button in this screenshot:

A Dodeca view with Auto on turned on by default

Validating in our view that the Auto on button was indeed on by default

Given a default set of toolbars that ship with Dodeca, this button typically starts in the “off” state. In case you’re not familiar, the effect when it’s on is that any time you change an item in a selector, the current view will be rebuilt. This can make it really convenient to browse through different views of data without having to press the build button (you can see this in the video at the end of this article). It’s just one of those usability things that makes interacting with data just that much more fluid.

Continue Reading…

Dodeca: Create and use a new Essbase MDX Selector List

The most recent version of Dodeca brought several exciting enhancements for MDX-related functionality. One of these is a new selector list based on a reusable MDX script object. Although MDX queries are probably most often associated with queries that return numerical data from a cube, they also have incredibly useful metadata capabilities that can be employed for various purposes. In Dodeca, it’s common to use a report script or member query specification to return members from an outline. For example, you might want to provide your users with a selector such that they can choose a particular product (or products) from your Product dimension in order to customize a report that they will build dynamically.

I see MDX scripts as being a natural, clean, and flexible way to populate these selectors, and moving forward I will recommend them whenever possible over the more arcane report scripts that have been around for years.

That all said, what I want to show today is the following: I’m going to edit an existing Dodeca view so as to replace one of its existing selector lists with a new list based on an MDX query.

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.