jason w. jones

software from the trenches

Update to Thriller MDX over JDBC driver

Update, October 2026: Thriller is now open source and runs over the Essbase REST API. The thriller.print-tuple-member-separately and thriller.header-tuple-separator hints below are no longer needed: each dimension on the rows is always its own column, and the members over a data column are joined with a space. See the announcement and the project page.

I made a few adjustments and fixes to the experimental Thriller MDX over JDBC driver I have been playing with off and on. As a quick recap, Thriller is a normal JDBC driver that essentially passes MDX queries straight through to an Essbase server, and then maps the results into a normal JDBC ResultSet using a set of provided “hints” that tell it how to make George Spofford cry flatten the results.

There were a couple of issues related to how queries with various CrossJoins were handled that should now be fixed. Additionally, there are now a couple of new options to provide more configurability over how tuples are split or joined together. Things are definitely getting interesting for this concept.

Continue reading Update to Thriller MDX over JDBC driver →

Want to see a Dodeca Excel Add-In demo? Webinar on Tuesday!

Tomorrow (Tuesday, November 22nd) I am doing a webinar on Applied OLAP’s Dodeca Excel Add-In. The Dodeca Excel Add-In is an Excel plugin that works with Essbase to provide a modern, fast, and streamlined ad hoc experience. The add-in provides a thoughtfully designed native ribbon UI, many convenient UI enhancements, and a VBA compatibility layer that makes it straightforward to convert existing Excel solutions that use the old VBA API.

On the webinar I’m going to talk about why and your organization might want to use the add-in, how it compares to the Dodeca Spreadsheet Management System in terms of functionality, and then do a live demo to show various features off. At the end I’ll take questions from the audience

Continue reading Want to see a Dodeca Excel Add-In demo? Webinar on Tuesday! →

Understanding Dodeca Tenants

Last week I introduced the concept of different applications in Dodeca. In short, a Dodeca application can be thought of as a completely configurable end-user application with its own authentication mechanism, view selector, and settings. The fact that we can literally spin up a new Dodeca application without having to setup a new database, a new server, or a new anything really is very powerful.

One thing to consider with the application is that the Dodeca objects that we can reference and use within it are limited to those objects that have the same tenant as that application. So let’s talk today about what a tenant is, exactly.

All objects in Dodeca, be they views, selectors, connections, variance commentary, and more, are stored in the Dodeca repository. This is a relational database that is configured when Dodeca is deployed in an organization. This database actually has a deceptively simple table structure, as shown here:

Continue reading Understanding Dodeca Tenants →

Multiple Applications in Dodeca

I want to talk about one of the more interesting aspects of Dodeca and its extensibility: applications. In some of my past posts, I’ve alluded to this notion of applications (and also something called a tenant) in Dodeca but haven’t really had a chance to dive into much detail. I’ll get into the notion of tenants some other day, so today’s focus is on what an application is.

In our world (the EPM world, that is), the term application has a lot of meanings. In Essbase-land, it’s a container for cubes or plan types. In the world of software, it usually means a single program. In the world of Dodeca, an application defines the views, behavior, look, and other properties that a user will see when they launch Dodeca.

Continue reading Multiple Applications in Dodeca →

Greet your Dodeca users with a custom landing page

Today’s Dodeca post will be on the lighter side, but is nonetheless a pretty useful feature. One of the interesting aspects of Dodeca is its support for multiple tenants and applications. This means that within just a single Dodeca repository (created when the Dodeca service is installed), we can have multiple distinct applications. This allows for creating different applications for different groups of users. It’s really quite powerful and something I will explore more in the coming weeks.

That said, since we can organize Dodeca into multiple distinct applications, that also means we can configure the properties of each application however we want. And one of those settings we can configure is the default view. The default view is a normal Dodeca view that is opened automatically when the application is launched.

Continue reading Greet your Dodeca users with a custom landing page →

Playing with the Thriller MDX/JDBC Driver in Drillbridge

Update, October 2026: Thriller is now open source and runs over the Essbase REST API, so the driver needs no Oracle JARs, and its URL is now the server’s address, as in jdbc:essbase:thriller:http://localhost:9000/essbase. See the announcement and the project page.

Last week I talked about a new side project, which is a JDBC driver called Thriller for executing MDX queries against Essbase and mapping the results back into a normal relational database. And at the time, I said that this driver had some really interesting use cases, such as in Dodeca, Drillbridge, ODI, and other tools that work with JDBC drivers.

Speaking of Drillbridge – in the very near future I will be sharing Drillbridge’s official future direction, which I think is really exciting, but more on that later. In the meantime, let’s drop this baby into Drillbridge and see what happens!

The following walkthrough of using Thriller with Drillbridge will show off some features that are only available in the licensed version of Drillbridge, although this should in theory work with Drillbridge Community Edition (the free edition of Drillbridge), assuming you have the Thriller driver JAR file.

Continue reading Playing with the Thriller MDX/JDBC Driver in Drillbridge →

Running MDX queries through a JDBC driver (for fun?)

Update, October 2026: ten years later, Thriller is finally open source, so there’s no need to email me. It now talks to Essbase over the REST API, which means no Oracle JARs, and 1.1 is on Maven Central. See Thriller 1.1: MDX Over JDBC, Now Open Source and the project page.

So there I am, sitting in front of the Alaska Airlines gate at Boston Logan airport, waiting for my flight home to Seattle. It’s not a particularly glamorous terminal – the divorce from Delta hasn’t been too kind to Alaska at BOS; Delta seems to have kept the house and kids while Alaska microwaves Lean Cuisine on a futon in its bachelor pad…

As I’m pondering why there are white rocking chairs in the terminal, my phone rings with a familiar name: Mr. Brian Marshall. We catch up and exchange pleasantries before pivoting over to more important matters (all things EPM of course!).

Brian: “So… Vess.”

Jason: “Oh boy…”

So we get to talking about accessing Essbase data through a Java database driver, á la Vess. And we get to talking about running MDX queries and dumping the output – á la Camshaft.

And as the talk goes on I end up saying something stupid like this: “You know what might work? Jjust pass an MDX query through the driver over to Essbase and map the output to a fake table… It’d be like an unholy combination of Vess and Camshaft. You could probably knock it out in a day or two.”

And at that moment I knew I wouldn’t be able to resist opening my laptop for the five plus hour flight home.

Continue reading Running MDX queries through a JDBC driver (for fun?) →

New Indenting Options in Next Generation Outline Extractor writer

Besides talking about and working on all things Dodeca Spreadsheet Management System, Dodeca Excel Add-In, and Drillbridge, one of the other things I am helping with these days at Applied OLAP is continuing work on the Next Generation Outline Extractor.

Most readers of this blog are probably familiar with the outline extractor. Almost everyone I know in the EPM world uses it or has used it in the past. It is an incredibly popular tool and I am very proud to contribute my efforts to making it even better.

Continue reading New Indenting Options in Next Generation Outline Extractor writer →

Dodeca Techniques – Auto Load Windows User Name During Relational Input

Today I want to look at a practical example in Dodeca that came up while I was at a Dodeca training workshop for a client the other week. We know that Dodeca can update data in a relational database using its robust SQL Passthrough DataSet functionality. And we know that it can automatically use values from the selectors to update columns as needed (for example, if the user has selected a Scenario of Actual, we could use a token such as [T.Scenario] in the SQL query).

Now let’s say that we have a table with data, such as headcount forecast data, and for whatever reason (auditing), we want to record the username of the user that is inputting the data. To achieve this, we can use a single Workbook Script method and a function to dynamically insert the Windows username of the current user into the view, and then use that token just as we would use any other token. In this case, we’ll use that token in the INSERT/UPDATE statements on the SQL Passthrough DataSet. Note that none of this configuration has anything to do with security, per se, it’s purely to have a column in the table to make it easy to see who updated/inserted a given piece of data.

Continue reading Dodeca Techniques – Auto Load Windows User Name During Relational Input →

Dodeca Techniques: Dynamic Rolling Quarters

It’s very common to want to perform reporting with a rolling time period. For example, given a particular month chosen by the user, we’d like to display the 4 or 12 or however many previous months to it. We might be given a particular quarter and want to show the previous 4 quarters.

Achieving such dynamic reporting can be tricky in some tools or lead to a less than ideal user experience, but this type of layout can be achieved easily in Dodeca, without having to write any code (although we will use some Excel formulas).

The technique itself is pretty simple, as I will describe in a moment. But I also want to comment on the general technique, since it applies to so many different Dodeca reports. The way that we can easily build this report type has to do with the nature of using a spreadsheet as the basis of the report in the first place.

Continue reading Dodeca Techniques: Dynamic Rolling Quarters →