jason w. jones

software from the trenches

Thriller 1.1: MDX Over JDBC, Now Open Source

Ten years ago this month, I wrote about running MDX queries through a JDBC driver, and the “(for fun?)” in that title was doing a lot of work. It started with a phone call from Brian Marshall while I was waiting at Boston Logan for my flight home, during which I said something like “you could probably knock it out in a day or two.” Then I had a laptop and a five hour flight to Seattle, and the outcome was never really in doubt. It was a fever dream of a side project, and I loved it.

I called it Thriller. MDX over JDBC: MJ. Michael Jackson. Thriller. That is just how my brain works, and I have made my peace with it.

That post ended with “Just email me if you’d like to take the driver (Java JAR file for a spin).” Reader, a decade is a long time to wait for an email. So here is the better answer: Thriller is on GitHub under the Apache 2.0 license, 1.1.0 is on Maven Central, and you do not have to email anybody.

IntelliJ IDEA running MDX through Thriller. The console holds a query on Sample.Basic, with hints after SELECT asking for a long shape, the point of view as columns, upper-case names, aliases and no missing values. The results grid has MARKET, PRODUCT, MEASURES, SCENARIO, YEAR and VALUE columns, starting with East, Cola, Sales, Actual, Qtr1 and 5371. The Database tree shows the Sample application with its Basic cube, whose ten dimensions are listed as columns, and 155 MDX functions as routines.

A round peg for a square cube

Picture the clip art. A relational database is drawn as a cylinder. Essbase is, famously, a cube. Thriller lets you plug the round thing into the square thing, which is about as close to literally fitting a round peg into a square hole as software gets.

The reason to want to is tooling. Those of us who also live in SQL are spoiled by JDBC: IntelliJ and DataGrip, DBeaver, RazorSQL (which I used a lot), sqlline in a terminal, and every reporting and integration tool that takes a driver. Write a query, run it, get a table, sort it, export it. MDX was never invited to that party. Thriller is a JDBC driver that takes MDX, runs it against Essbase, and flattens what comes back into an ordinary result set, so all of those tools can work with a cube without knowing it is one:

SELECT {[Jan], [Feb], [Mar]} ON COLUMNS,
CROSSJOIN({[East], [West]}, [Colas].Children) ON ROWS
FROM Sample.Basic
WHERE ([Sales], [Actual])

+--------+---------+--------+--------+--------+
| Market | Product | Jan    | Feb    | Mar    |
+--------+---------+--------+--------+--------+
| East   | 100-10  | 1812.0 | 1754.0 | 1805.0 |
| East   | 100-20  | 200.0  | 206.0  | 214.0  |
| East   | 100-30  | 93.0   | 101.0  | 107.0  |
| West   | 100-10  | 1174.0 | 1146.0 | 1173.0 |
| West   | 100-20  | 700.0  | 726.0  | 727.0  |
| West   | 100-30  | 465.0  | 426.0  | 413.0  |
+--------+---------+--------+--------+--------+

The two dimensions on the rows became two text columns, each month became a numeric column, and the names came from the result itself. No configuration, just MDX.

No more Oracle JARs

The 2016 Thriller sat on top of the Essbase Java API, which was the right tool at the time and also the whole problem. Those JARs are Oracle’s: proprietary, not on Maven Central, not mine to redistribute, and matched to your server’s version. So “try my driver” really meant “find the right Oracle JARs for your server, get them onto the class path, and hope.” That is a big part of why it never got past “email me.”

Fast forward. Essbase has a decent REST API now, and it runs MDX. There is also essbase-rest-client, the open source Java library for that API that I wrote and that we maintain at Applied OLAP. So Thriller swapped the plumbing. Same idea, same MDX, same hints, but every query is a REST call through the library. No Oracle software anywhere, and the whole thing is one jar.

As with Essterm, the library got better for it. Thriller wants to know which dimensions sit on each axis, and the library’s MDX method had been quietly throwing that away, so essbase-rest-client 2.1.0 now hands it over, along with the point of view. It also actually sends the MDX options it had been accepting and ignoring all along, and no longer falls over on a query that comes back empty.

And Essbase taught me something along the way: it stops reading an MDX query at the first semicolon, even one inside a comment, and then complains that your comment never ends. That one came to light because a cookbook example had a semicolon in its description. Thriller now blanks out comments before sending a query, so yours can say whatever they like.

What it does now

The original idea is still there: a comment block of hints that names and types the columns. Hints are optional now, and you can give just the ones you want, so -- column.4.scale = 1 rounds the fourth column and leaves the rest alone. On top of that:

  • Aliases. members = alias turns 100-10 into Cola, and alias.table = Long Names turns Jan into January.
  • A fact table. shape = long gives one row per value with a column for each dimension, pov = columns adds the point of view, and names = upper makes the column names identifiers. That is the shape BI tools and dataframes want.
  • A browsable tree. In your IDE’s database tree, each application is a schema, each cube a table, and each dimension a column, commented with whether it is dense or sparse and how many members it has. The server’s MDX functions show up as routines.
  • Good manners with tools. It answers the keep-alive queries tools send (IntelliJ’s turns out to be select 'keep alive'), fills in ? parameters in prepared statements, honors query timeouts, comes with a DataSource for Spring and connection pools, and can log in with a token or an existing session for servers behind an identity provider.

Here is the fact table version of a query, members named by alias:

SELECT
/*
 -- shape = long
 -- pov = columns
 -- names = upper
 -- members = alias
 */
{[Jan], [Feb]} ON COLUMNS,
CROSSJOIN({[East], [West]}, {[Cola], [Root Beer]}) ON ROWS
FROM Sample.Basic
WHERE ([Sales], [Actual])

+--------+-----------+----------+----------+------+--------+
| MARKET | PRODUCT   | MEASURES | SCENARIO | YEAR | VALUE  |
+--------+-----------+----------+----------+------+--------+
| East   | Cola      | Sales    | Actual   | Jan  | 1812.0 |
| East   | Cola      | Sales    | Actual   | Feb  | 1754.0 |
| East   | Root Beer | Sales    | Actual   | Jan  | 1853.0 |
| East   | Root Beer | Sales    | Actual   | Feb  | 1966.0 |
| West   | Cola      | Sales    | Actual   | Jan  | 1174.0 |
| West   | Cola      | Sales    | Actual   | Feb  | 1146.0 |
| West   | Root Beer | Sales    | Actual   | Jan  | 2723.0 |
| West   | Root Beer | Sales    | Actual   | Feb  | 2759.0 |
+--------+-----------+----------+----------+------+--------+

Notice the hints sit just after SELECT. Some tools, IntelliJ among them, run only the statement under your cursor, and a comment above it does not count as part of the statement.

The cookbook has twelve queries like these, each with its output, and several are adapted from my old MDX examples page. Whenever the tests run against a server, they check every output, so the cookbook cannot quietly drift out of date.

Getting started

You need Java 17 or newer and an Essbase server with its REST API. If you do not have one handy, the Essbase 21 on Docker guide will get you one. Thriller is tested against Essbase 26.1, and its first release was tested against 21.1 too.

  1. Download thriller-mdx-jdbc-1.1.0-all.jar from the releases page or from Maven Central. It is a single jar with everything Thriller needs.

  2. Add it to your tool as a driver, with the class com.jasonwjones.hyperion.thriller.ThrillerDriver.

  3. Connect with a URL that is your server’s address, as you would open it in a browser, and your Essbase user and password:

    jdbc:essbase:thriller:http://localhost:9000/essbase

  4. Run some MDX.

To use it from code, it is on Maven Central as com.jasonwjones.hyperion.thriller:thriller-mdx-jdbc:1.1.0, and then it is JDBC as usual. The README covers the rest, including setting it up in IntelliJ.

Okay, but should you?

I said it in 2016 and it is still true: MDX exists because cubes are not tables, and pretending one is the other is a little bit unholy. But JDBC is everywhere and MDX is not, and sometimes you just want the cube in the tool you already have open. A few places where that earns its keep:

  • A query window next to your SQL. Check a number in the cube without leaving the IDE where you are writing the load that feeds it.
  • Feeding things that speak JDBC. BI tools, dataframes, ETL, and reports can take a cube’s data as a table, and with shape = long it arrives as a fact table instead of a grid.
  • Extracts without the file shuffle. Run the query where the data is going, rather than exporting to a file and loading it back in.
  • Reading an outline as a table. With nothing on the columns and a few dimension properties, a query gives you members, aliases and levels as rows.

Try it

It took ten years and a REST API, but the answer to “email me” is finally a jar on Maven Central. Grab it, point it at a cube, and see what you think. It is a personal project, so issues and pull requests are very welcome, and if you plug it into a tool I have not thought of, I would love to hear about it. Round pegs, square holes. It fits better than it has any right to.