jason w. jones

software from the trenches

Oracle Open World 2016 Recap

As I mentioned a week or so ago, I made a last minute appearance at Oracle Open World this year. It was my first time attending and presenting at OOW. I actually didn’t catch too much of the conference as I only flew in on Wednesday and flew out on Thursday. Nevertheless, I had a bit of a whirlwind experience, but a very good one. While I hadn’t planned on it (I’m more of a Kscope guy), I am now looking forward to attending Open World next year.

As for the presentation I was part of, I think it went pretty well. Many thanks to Gabby Rubin of Oracle for coming up with the idea for the presentation and facilitating it. The presentation was on “Essbase Tools and Toys” and was meant to highlight, at a high level, some of the interesting things that folks such as myself are doing that involve the Essbase APIs or otherwise work with Essbase. The presentation discussed items created by me, Tim Tow, and Harry Gates. Additionally, Kumar Ramaiyer (also from Oracle) talked a bit about what’s coming with Essbase Cloud Service (EssCS).

Continue reading Oracle Open World 2016 Recap →

TBC Files for Bankruptcy

After years of declining and inconsistent revenue, the multi-state beverage company TBC has filed for bankruptcy. TBC was known for carrying an eclectic, if antiquated, selection of products that didn’t seem to resonate with modern consumers.

A vice president at TBC for the last 15 years, Richard Doyle, found out that his last day would be at the end of September. “Our products just don’t seem to resonate with the young, hip crowd,” said the forlorn executive, with a wistful look on his face (they don’t seem to resonate with the older crowd either, but I digress). “Kids these days are drinking their fancy this and that, grande double pumpkin bullshit or whatever.”

Continue reading TBC Files for Bankruptcy →

Camshaft (Essbase MDX query tool) 1.0.2 released

Apparently I’m having quite the productive Friday, what with showing how easy it is to setup drill-through with Dodeca and that I’m heading to Oracle Open World 2017 to contribute to a presentation on cool Essbase tools.

To these articles I’ll add that I just released a Camshaft point release. This release has a couple of fixes and enhancements. Thanks to André Märki and others for providing feedback.

This version of Camshaft fixes an issue where some data with many digits after the decimal would be rendered in scientific notation. Along with this fix I have added a new command-line switch, --maximum-fraction-digits (used on the command-line such as --maximum-fraction-digits=2) to set the max number of digits to render after a decimal.

Continue reading Camshaft (Essbase MDX query tool) 1.0.2 released →

Oracle OpenWorld 2016 Presentation: Essbase Community Toys and Tools

I am very pleased to mention that I will be making a last minute appearance at Oracle OpenWorld this year! I am going to be presenting with Gabby Rubin, and Tim Tow about various free tools that exist in the Hyperion/Essbase ecosystem. The presentation is on Thursday, September 22nd, 2016, at 12:00pm at Moscone West. The presentation ID is CON6489.

For my part of the presentation, I am going to talk about a handful of tools I have personally created over the years. This will likely include Cubedata (a tool for generating large quantities of data to help test Essbase performance), Camshaft (a tool for running MDX queries and outputting the results to a text file), and Vess, a highly experimental/innovative JDBC driver for Essbase that provides a functional facade for Essbase servers/cubes including access to outline data, cube data, data loads, substitution variables, and more. The goal of the presentation will be to quickly inform intrepid Essbase/Hyperion administrators and developers about some of the interesting third-party functionality they might find useful and improve productivity.

Continue reading Oracle OpenWorld 2016 Presentation: Essbase Community Toys and Tools →

Simple Drill-through in Dodeca

Dodeca has robust support for drill-through. You can drill from Essbase data to relational data, from Essbase to Essbase, and SQL to SQL. You can have multiple drill-through definitions in a single view, so that a user can choose one of many drill destinations. Today I want to look at the simplest form of drill-through in Dodeca, which is to simply enable a couple of the Data Drillthrough options on a source view, tell it what the target view is, and be done with it. I call this the “simple” version of drill-through because it just gives us the ability to double click on a data cell and drill from it.

The less simple, or rather, more elaborate, version of drill-through can be configured with custom context menus, multiple drill targets, and more configuration options than you can shake a stick at. I’ll be looking at an example of that in an upcoming article. But for now, here’s how “simple” drill-through can be quickly and easily configured in Dodeca.

The example I’m going to look at today is one where we’ll let the user drill from one Essbase-based view to another. While many people think of Essbase drill-through in terms of going from OLAP/Essbase/consolidated data back to the original source OLTP/relational/transactional data, drill-through between Essbase views is an incredibly useful feature as well. It gives us the ability for a user to pull up data they are interested in, such as by time period and location, then very quickly jump to a different or expanded view of data based on those same intersections. Given the fluidity and seamlessness we can achieve in terms of going between different views with any data on them, drill-through becomes even more powerful. Instead of swimming upstream to more granular data, we can think of drill-through more as “intelligent navigation” – and drilling to details is just one type.

Continue reading Simple Drill-through in Dodeca →

Camshaft MDX tool updated and available

Some of you may recall a tool I released quite some time ago (seemingly to beta-testing purgatory) called Camshaft. Camshaft is a simple Java utility that executes a given MDX query against an Essbase cube and outputs the results. The original version of Camshaft came out around two years ago. This version is built on the same framework but includes various updates and new options. In the interim, the output abilities of the MaxL interpreter have been improved a bit, and with the right incantation it can now output pretty useable data.

The name Camshaft is actually a portmanteau of who the tool is named for, and the feeling that he gets when writing a load rule (especially one loading in MDX data). It’s not every day that a tool is named after a tool, but I digress (I kid, I kid!).

Continue reading Camshaft MDX tool updated and available →

Vess + Dodeca for Substitution Variable Management

I’m gonna go a little crazy today and combine two worlds, just for fun: the Vess “virtual” Essbase JDBC driver, and of course, Dodeca. I’ve written about Vess before, and even talked about it for a bit during Kscope16 earlier this year during a presentation with Tim Tow and Harry Gates on various interesting things we’re doing with Java and the Essbase Java API.

As a quick crash course on Vess, it’s a highly experimental Java JDBC driver that models an Essbase server’s applcations/cubes/properties into variable relational tables (I’ve written about Vess a few times before). At the moment this includes cube outline data, cube data, substitution variables, miscellaneous properties, and more. For example, when you connect Vess to, say, Sample/Basic, one of the tables you’ll get is SAMPLE.BASIC_VARS and it’ll contain four columns: the application, cube, variable name, and variable value. You might think you wouldn’t need to know the application and cube for this table but due to a nuance with Essbase variables (you can have the same variable name at both the cube, application, and server level) it’s actually needed.

Continue reading Vess + Dodeca for Substitution Variable Management →

Dodeca Dynamic Grouping with Relational Data

I am very pleased today to write about an incredibly awesome Dodeca capability: dynamically built groups based on relational data. This capability is interesting and useful for a variety of reasons. Using Dodeca’s spreadsheet/data/magic build paradigm, we can organize plain relational data into beautifully formatted, insightful, and dynamic views. Just to forecast where I’m headed with this, what we’re going to do is transform this plain relational data:

Some raw forecast related data

Some raw forecast related data

Into this dynamic, grouped, and formatted view:

Dodeca dynamic grouping opened up in Excel

Dodeca dynamic grouping opened up in Excel

And further, we’re going to do it without writing a single line of code (save for the simple SQL Select statement). This post will assume that you’re up and running already with Dodeca’s SQL Passthrough DataSets, which I have written about before, so head over for a refresher if you need it. Also, I’ll be recycling a simple SQL table with forecast data by employee that I also used in an earlier Dodeca relational database input article, so you can read that if you want to know more about the data in play and how it relates to Sample/Basic.

Continue reading Dodeca Dynamic Grouping with Relational Data →

Data Input with Dodeca, part 6 – SQL and Essbase Hybrid Input in one View

The last article on relational data input with Dodeca was a bit epic – I was planning on something a little shorter and sweeter for this next article, but it’s going to be another long (but awesome!) one that combines everything we’ve seen so far in this data input series, and more. To recap, the series so far has consisted of the following articles:

Let’s get crazy today with a soup to notes implementation where we’ll input relational data and then load it to Essbase automatically so that the data ties out. You might call this “home-brew hybrid”. As with before, it’ll be based on our favorite database in the whole wide world, Sample/Basic.

Consider the Sample/Basic dimensionality: Year (time periods), Scenario, Market, Measures, and Product. The use case that I’m going to look at today will cover the scenario where we want to prepare a budget, by product, by time period, by region, but have it be by employee. But this dimension doesn’t exist in the cube – no problem! Let’s further stipulate that for either architectural, performance, or other reasons, we absolutely do not or cannot put in an Employee dimension. So what we’re going to do is have Dodeca facilitate inputting data by employee and feed that into a relational database, then we’re going to use some simple Dodeca automation (workbook scripts) to take the sum of the data we input (for the given time period and market and so forth), send it up to Essbase, do a focused calculation on the cube, and then retrieve the updated data to show on the exact same sheet that we’re already on.

Continue reading Data Input with Dodeca, part 6 – SQL and Essbase Hybrid Input in one View →

Data Input with Dodeca, part 5 – Relational Database input

I have been really, really looking forward to writing this continuation in the Dodeca Data Input series, for a couple of reasons. For one, it’s a genuinely useful feature that Dodeca implements very well. But secondly, and perhaps more important, the ability to get and store this data from users is just an absolutely missing piece of functionality in the traditional Hyperion toolbox. So this is going to be a bit of a long article but will cover how relational data input in Dodeca works and why it’s so important.

As a quick recap, up to this point I’ve covered basic Essbase data input, cell/variance commentary, going under the hood to look at the audit log tables, and focused calc scripts that run after Essbase input. To this we will now add SQL/relational data input. To put it in context, relational database input is one of the tentpole Dodeca features, and stands next to other heavy hitter features such as Essbase input, comments, drill-through, and cascading reports. Now, all of the individual features of Dodeca are useful and interesting. And yet, I see relational data input as a feature that almost singlehandedly makes Dodeca greater than the sum of its parts.

Continue reading Data Input with Dodeca, part 5 – Relational Database input →