MDX OLAP on Cassandra

26 Sep

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 Responses to “MDX OLAP on Cassandra”

  1. Charly October 2, 2013 at 3:28 pm #

    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.

    • kromerbigdata October 2, 2013 at 5:06 pm #

      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.

Trackbacks/Pingbacks

  1. MDX on MongoDB | Big Data Analytics - September 30, 2013

    […] had a few queries following my posting on MDX for Cassandra using Pentaho’s Big Data Analytics, namely about if this same capability was available on […]

  2. More MDX Fun on NoSQL Databases | Big Data Analytics - October 2, 2013

    […] to do a few more interesting things with MDX OLAP models that we’ve put on top of MongoDB and Cassandra data […]

  3. OLAP Analytics on Cassandra Data | Big Data Analytics - October 4, 2013

    […] I showed you how easy it is to accomplish complex analytics with slice & dice on MongoDB and Cassandra. But in those posts, we wired up the Pentaho suite to Mongo & Cassandra for MDX queries and […]

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

cbailiss

Microsoft SQL/BI and other bits and pieces

TIME

Current & Breaking News | National & World Updates

Tech Ramblings

My Thoughts on Software

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Insight Extractor - Blog

Paras Doshi's Blog on Analytics, Data Science & Business Intelligence.

The SQL Herald

Databases et al...

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

Bill on BI

Info about Business Analytics and Pentaho

Big Data Analytics

Occasional observations from a vet of many database, Big Data and BI battles

Blog Home for MSSQLDUDE

The life of a data geek

%d bloggers like this: