MDX OLAP on Cassandra

MDX and NoSQL? Heck yeah. With the Pentaho Analytics Suite, I was able to point Instaview to my Cassandra cluster, select a keyspace and issue MDX queries to slice & dice (OLAP-style) my Cassandra data.

I guess this means that the answer to my question my last year is “No”. In all fairness to myself, I guess … I was really referring to MPP in-analtyics databases and Hadoop MR with Mahout where analytics are “in-engine” instead of forming logical in-memory OLAP models. There, that’s out of the way. The intention of those systems is to store large data sets AND allow analytics directly against those distributed data stores.

But in the NoSQL world (Cassandra, MongoDB, etc.), simple queries can sometimes be a challenge for those of us who are data pros, not necessarily programmers. So if you like to ask questions of your data like show me sales using PARALLELPERIOD or slice the number of likes by DESCENDANTS, it would be awesome to issue those same MDX queries against NoSQL data stores, or to use classic BI tools that speak MDX regardless if your data is in Oracle, MySQL or Cassandra or MongoDB.

Here’s how to do it:

  1. Go to Pentaho.com and download the Pentaho Business Analytics Suite
  2. Run the installer and start up InstaviewImage
  3. Chose Cassandra and point it to your cluster and keyspace
  4. Most of the magic will happen automatically as Instaview takes the data from Cassandra, stages it in memory and generates an OLAP model for you Mondrian – the MDX engineImage
  5. On the Analyzer reporting screen, select Administration -> MDX
  6. In the MDX query screen, enter your MDX query. Here is mine using my small demo Cassandra data set:

    select [Measures].[Empsale] on 0, [Emplast].[Emplast].Members on 1 from [Untitled 9]

  7. And the results are shown on the next screen … super fast, from RAM:

Image

There ya have it … MDX OLAP queries from Cassandra. Very quick & easy. BTW, the cube name from my MDX query above (“Untitled 9”] can be found on the Model view within Instaview:

Image

Advertisements

5 comments

  1. Hi, how did you do to make monetdb step work? I’ve created the pentaho-instaview dbfarm but when the etl runs, the mclient crashes.

    • Hi Charly – I used the Enterprise Edition installer from the Pentaho.com website which you can use to set-up the platform for you, including the Instaview components. I’m running on Windows 7 and the MonetDB portion is pretty much functioning under the covers without any interaction from me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s