In the small database that contains about one million records the need for some reporting arose. Since the system will work for at least 5 more years I was reluctant to just “winging it with recapitulation tables and some SQL” approach.
The green pastures of OLAP have lured me long enough. My hopes where high, and my knowledge non existing. This is a tale of a man vs. the elements.
In particular free OLAP for the Masses
The “free” part complicated the issue because this is the only choice:
- Mondrian
- icCube
- Palo
I didn’t check out Palo since I was not able to see bigger picture of their system on the site. (I am shallow I know). For the other two it seems to me that name of the game is get-backend-for-free-and-pay-for-reporting. This is particularly true for icCube.
I understand and appreciate the fact that since OLAP is mostly big business it is fair for them to pay up. I didn’t like that there was no clear formula on pricing. All in all it felt like I am not in that category with my needs to justify buying.
Installation
icCube installer is a breeze. Downside is that what you get (community server) is really just a bait for buying the real deal. I was unable to figure out how could I use this in production when you have to manually reload scheme after each restart. That aside, their web based approach is great. If you are unfamiliar with how to build OLAP cubes it is really helpful to play with their examples.
Mondrian installation was terrible. At one point I wanted to jump out of the window and end my misery. But after you realize that you have Tomcat x and Mondrian y and Java runtime z you can figure out what part of original documentation changed in what version and get the thing going.
Biggest hurdle for me was just being unfamiliar with Java .jar system. And I’ll tell you, it one fine system(really? NO!)
Building the cube
icCube has a web based interface for running the server, building the cube and querying the system. For Mondrian you have to download “Schema Workbench”. Other then looking vintage it does what it is supposed to do. Sometimes if you push it too hard, the file that contains the cube definition will get messed up and you won’t be able to load it (not before manually fixing the .xml).
Fronted
- JPivot - looks like 1960
- Saiku - looks really good, it is basically a new JPivot. I didn’t want to use it because I don’t have a need for a BI solution (with drill downs and defining of a reports on the fly) and I wanted something that is fully integrated with my solution
- XMLA client + graphing library
Ruby support for XMLA client
To my dismay I was unable to find ruby gem that would fit my needs. There is only mondrain-olap. It looks like great stuff, you even get a wrapper for cube definition. Under the hood it is using olap4j so you have to use jRuby. The jRuby part was a deal breaker for me.
..!!!$%# nothing else that I could find
I was forced to slap just enough code to be able to send an MDX request to OLAP server and get the result. No Java dependencies since it is utilising XMLA provider that in theory every OLAP server has. Gem is called “cube” and it is available at github for your pleasure and profit.
Benefits for using OLAP
- peace of mind
- not clogging the transactional system with reports
- it is fast! (climbing on the shoulders of a giant always pays off)
- MDX is really sql-on-steroids-and-then-some made especially for reporting
- you build a multidimensional cube and effortlessly look at the data from every possible angle - brilliant stuff not to mess with SQL for this
Downsides
- Another dependency that can stop working
Verdict
Do the right thing, don’t turn a blind eye and hope not to mess up your transactional system with annoying mega SQL queries… Let the OLAP machinery roar in your application.
My reports turned out so much better than I’ve expected. The biggest benefit is (other than speed) that when you start to look at data from the multidimensional cube perspective, the sky is the limit.
If you are new to all this it might be a little overwhelming : OLAP, XMLA, MDX, multidimensional cube.... But trust me it is worth it.