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.

First of all, with the Thriller driver file having been placed in the /lib under the Drillbridge installation folder, we can restart Drillbridge and navigate to the Connections screen:

Overview of connections in Drillbridge

Recall that in Drillbridge, Connections represent the list of potential relational databases/schemas that we might want to use to execute a query against. Since the Thriller driver makes a relational database facade for us, we are going to treat it just like any other relational database connection, such as Microsoft SQL Server Oracle, MySQL, DB2, or whatever. You may notice in the above screenshot that there is already an entry for Sample/Basic using the Thriller driver. I already added it, but let’s go take a look at the connection configuration itself:

Editing a new Drillbridge connection with the Thriller MDX/JDBC driver

There’s nothing too special here – a name, credentials, and probably most importantly, a particular JDBC URL that is specific to Thriller. The general syntax for the Thriller JDBC URL is jdbc:essbase:thriller://server/Application.Database. Note that at the moment, Thriller assumes an “embedded” Essbase connection strategy (as opposed to going through APS).

As with a normal relational database connection, we should verify it to ensure connectivity and proper credentials:

Testing the Thriller MDX/JDBC connection

With the connection now created, we can move on to creating the Drillbridge report, which will reference that connection. Let’s create a new report and edit the essential fields:

Creating a Drillbridge Report with a Thriller MDX query

You can’t see the entire interface, but the essentials are shown: I have set the connection for this report to use the new Thriller connection to Sample/Basic, and I have dropped in a specifically crafted Thriller query. The comments with the column names/types are part of the Thriller mechanism for mapping MDX results to a flat result set. If you want crash course in how it works, please head over to the introductory blog post where I showed how to make George Spofford cry make it work.

So, we have our driver in place, a connection setup, and a new report created that specifies that connection and a specially crafted MDX query. That’s actually all we need for now to see this thing in action. Let’s go over to the Test tab:

Testing the Drillbridge report

There are no tokens in the query (yet), so there is nothing to specify, so we can bit the Build It! button, cross our fingers, and see what happens. And wouldn’t you know, data comes back, per our column mapping specification:

Thriller/Drillbridge query output

Nice.

Let’s go for extra credit by parameterizing one of the members in the query and use the new Drillbridge token editor interface. Back over in the query, let’s take out Cola and drop in a “short token”:

Adding a “short token” to the Thriller MDX/JDBC query

If you look carefully, you’ll notice that Cola from the original query is replaced with {{#Product}}. This is known as a “short” token in Drillbridge. This is mostly useful so you can quickly drop it in and then use the token editor to customize the token. With the token in place, let’s navigate over to the Tokens menu:

Navigating to the Token editing interface

This will show us all of the recognized tokens in our query:

The Edit Tokens interface displays the recognized tokens in the query

There’s just Product so we can click on it and bring up the options for this specific token:

Editing options for the Product token

We don’t really need to change anything here, but let’s drop in a Sample Value so that it will pre-fill the box when we go to test the report. Updating the token results in the corresponding query being updated:

Query updated with token options

Notice that the short token got replaced with a full token (JSON syntax). We can now go test the report again and see that we now have a token we can customize:

Testing the report again, now with a Product token

Although the screenshot shows Cola (since that came up by default, for testing purposes I actually put in the value of another product, Sasparilla). Press build, and then:

The built report (using a different product)

The eagle-eyed amongst you might notice that this data is different than before (as it should be, since it’s a different product). Just for fun and to finish things out, let’s click on the Excel button to download as an Excel (.xlsx) file, as we would with any other result set coming from a JDBC/relational database:

Drillbridge report downloaded to Excel

As with the normal Excel output generation, Drillbridge has inspected the data types of the columns (VARCHAR/String, DECIMAL, INTEGER, etc.) to try and find the best matching Excel column type. So, nicely enough, the values in columns B through D, corresponding to Jan, Feb, and March, are true Excel numeric values (as opposed to labels).

The Drillbridge/Thriller Use Case

Now, you might be thinking, wait, why would I need to drill in to Essbase data… from other Essbase data? In other words, if you’re navigating the cube using Smart View, why on earth would you need to drill in to data that’s already in the cube? There are some edge use cases where I think this could be useful, but actually what might be much more useful is that you could use this to drill into data that’s in another cube. So maybe you have some cubes with common but different dimensionality. There’s nothing stopping you from dropping in a drill-through definition with Drillbridge that let’s you drill from the cube, over to Drillbridge, where it then runs this query and displays the results. You could, of course, use the Drillable Columns feature in Drillbridge to drill from/between different Drillbridge reports based on the POV of a row in the column result set.

Interesting… and Useful

I have repeatedly referred to this driver as well as other flights of fancy (Vess) as being “interesting”. That’s kind of my way of saying, “Yeah, it works, technically… but is it the best solution?” I think this use case with Thriller tips the scales from just Interesting over to actually useful, as I can see some pretty useful opportunities where this could be dropped in and get quick win.

I’m still working out a few kinks with the driver, but to those of you that emailed to get an early copy to play with, I say thank you – and please hang on for a bit as I clean a few things up and make this more generally useable.