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.

Vess + Camshaft = ?

So, what if we write a custom JDBC driver that takes a normal MDX query and some hints about how to convert the output to a regular table, run it against an Essbase cube using the Essbase Java API, and then process the results so they look like a normal table? Could we drop that into any old JDBC tool or software (Dodeca, Data Visualization Desktop, Java programs) and have it work?

Well, yeah. Now, that doesn’t mean that it’s a good idea. But it most certainly works. Here’s an example:

/**
* Pull first quarter measures for Cola for total market
*
* -- column.1.name = MEASURE
* -- column.1.type = VARCHAR
* -- column.2.name = JAN
* -- column.2.type = DECIMAL
* -- column.3.name = FEB
* -- column.3.type = DECIMAL
* -- column.4.name = APR
* -- column.4.type = DECIMAL
*
*/

SELECT CROSSJOIN({[Jan], [Feb], [Apr]}, {[Cola]}) ON COLUMNS,
{[Measures].Levels(0).members} ON ROWS FROM [Sample].[Basic]

As you can see, I have a normal MDX comment block and an MDX query. Inside the comment block is where we can “stash” some parameters or hints to the query parser so that it knows how to map the output. To some extent the driver could try and infer what the column names, types, and other attributes should be, but at least for prototyping purposes, I think explicitly defining them is the way to go.

Upon executing the query against our favorite database in the world (Sample/Basic!), we get the following results (printed to text on the console in this case):

+--------------------+--------------------+--------------------+--------------------+
|MEASURE             |JAN                 |FEB                 |APR                 |
+--------------------+--------------------+--------------------+--------------------+
|Sales               |4860.0              |4821.0              |5048.0              |
|COGS                |1888.0              |1886.0              |1949.0              |
|Marketing           |667.0               |673.0               |699.0               |
|Payroll             |586.0               |586.0               |596.0               |
|Misc                |9.0                 |10.0                |11.0                |
|Opening Inventory   |14587.0             |14039.0             |13660.0             |
|Additions           |4312.0              |4348.0              |5560.0              |
|Ending Inventory    |14039.0             |13566.0             |14172.0             |
|Margin %            |61.152263           |60.879486           |61.39065            |
|Profit %            |35.185185           |34.557146           |35.519017           |
|Profit per Ounce    |142.5               |138.833333          |149.416667          |
+--------------------+--------------------+--------------------+--------------------+

Of course, being MDX we aren’t limited to just data from the cube itself, we can even ask for dimension properties and information. For example, consider the following MDX query that runs against Sample/Basic and asks for the member name, alias, and level of members in the Product dimension:

/**
* Pull alias and level informatin for products
*
* -- column.1.name = MEMBER_NAME
* -- column.1.type = VARCHAR
* -- column.2.name = ALIAS
* -- column.2.type = VARCHAR
* -- column.3.name = LEVEL
* -- column.3.type = INTEGER
*
*/

SELECT {} ON AXIS(0),
Product.Members DIMENSION PROPERTIES
PROPERTY_EXPR (Product, MEMBER_ALIAS, CurrentAxisMember(), "Alias"),
PROPERTY_EXPR (Product, LEVEL_NUMBER, CurrentAxisMember(), "Level")
ON ROWS
FROM Sample.Basic

After executing, we get the following data:

+--------------------+--------------------+--------------------+
|MEMBER_NAME         |ALIAS               |LEVEL               |
+--------------------+--------------------+--------------------+
|Product             |                    |2                   |
|100                 |Colas               |1                   |
|100-10              |Cola                |0                   |
|100-20              |Diet Cola           |0                   |
|100-30              |Caffeine Free Cola  |0                   |
|200                 |Root Beer           |1                   |
|200-10              |Old Fashioned       |0                   |
|200-20              |Diet Root Beer      |0                   |
|200-30              |Sasparilla          |0                   |
|200-40              |Birch Beer          |0                   |
|300                 |Cream Soda          |1                   |
|300-10              |Dark Cream          |0                   |
|300-20              |Vanilla Cream       |0                   |
|300-30              |Diet Cream          |0                   |
|400                 |Fruit Soda          |1                   |
|400-10              |Grape               |0                   |
|400-20              |Orange              |0                   |
|400-30              |Strawberry          |0                   |
|Diet                |Diet Drinks         |1                   |
|100-20              |Diet Cola           |0                   |
|200-20              |Diet Root Beer      |0                   |
|300-30              |Diet Cream          |0                   |
+--------------------+--------------------+--------------------+

From a SQL/JDBC perspective, the columns all have the types that we specified in the “hint” section: the MEMBER_NAME and ALIAS fields are VARCHAR columns, and the level is a plain INTEGER.

Use Cases

There are a number of places I think this driver could be applied usefully:

• Extracting metadata with a query tool/engine
• Using MDX data in a Dodeca SQL Passthrough DataSet
• Drilling to MDX query data in a tool that understands JDBC (Drillbridge et al)

Extracting Data/Metadata

Conceptually, this use case is approximately the same as just using MDX to extract data using MaxL or with Camshaft, but you could potentially save yourself the step of having to then load that text file into a database. This could potentially come in handy for some use cases where you don’t want to deal with the file system.

Using in a Dodeca SQL Passthrough DataSet

Dodeca already supports MDX in various ways and due to its inherent power and flexibility, MDX is being creatively used in a rapidly growing number of use cases. This is provided by way of various BuildRangeFromScript Workbook Script methods. That said, as is true for other tools out in the world, JDBC data/connectivity is already a first-class citizen in Dodeca, so this driver opens up the interesting possibility where we could treat MDX data/metadata as just any other normal table/view.

This approach wouldn’t necessarily be better, but it could open up some very, very interesting possibilities where the MDX data is loaded up in a DataTable range on a view and, for example, Dodeca’s declarative dynamic grouping/sorting/filtering is applied on the fly. I’ll likely show an example of this in the near future.

Drilling to MDX Data (Drillbridge!)

In addition to the classic “JDBC Drillthrough Report” that is the workhorse of Drillbridge, the enhanced version of Drillbridge ships with two other report types (in addition to supporting custom plugins): link-based reports and MDX query reports. In Drillbridge, the MDX query report is used to generate and execute an MDX query instead of a normal SQL query, then the grid-based results are displayed to the user.

Given that all versions of Drillbridge support third-party JDBC drivers, this driver could be particularly interesting to drop in to Drillbridge, then run a normal MDX query and display the results in a completely columnar format. I’ll also probably show an example of this in the near future as I think it could be really cool.

Availability & Next Steps

I’ll use the same word to describe this software as I did for Vess: It’s interesting. This driver is a bit more “unholy” than Vess in some ways since it’s trying to pretend one thing (OLAP data) is another thing (OLTP data). Just for the record, I fully, fully, fully realize that among other things, MDX was invented because OLAP databases are altogether a different animal than relational databases. But it’s quite likely that JDBC is much more common than MDX (or rather, there are more tools that inherently work with JDBC than its equivalent for OLAP databases).

Just email me if you’d like to take the driver (Java JAR file for a spin). As with Vess, it’s incredibly experimental but it works surprisingly well. You’re free to play around with the JAR file and hopefully provide feedback on any kinks you run into (oh, and blog about it, that’d be cool too).