The bread and butter at work is all the magic that happens in OLAP, Microsoft Analysis Services in particular. Alot of people assume OLAP is going the way of the Buffalo, they probably also like buzzwords such as “nosql”, “bigdata”. I won’t argue that OLAP has a pretty high barrier to entry, as well as limited resources compared to a lot of other data technologies. And, you probably shouldn’t paint yourself into a corner with it and/or put all your eggs in one basket. I say shouldn’t because its pretty hard to avoid in BI solutions, and most places probably end up like us – asking the question of “how do I turn this into a cube?”.
My main complaint is that people often treat OLAP, especially SSAS as a giant “black box”. If it’s not in/done/accessible in the (Visual Studio) solution then its a dead end… It only takes a second to realize every solution file is XML, and that every item in a Cube or OLAP db can be expressed in XML (from management studio) -> turns out the XML for objects in the solution is about the same as when looked at in the deployed cube/olap db. What does this mean? VS is just a giant XML editor… Not that we’re surprised.
I was forced to learn this via necessity, mostly for the need to integrate Microsoft Analysis Services into an open source ETL tool. First it was just figuring out simple processing and synching, then into dynamic cube modification. I had to rely on the ascmd.exe command line utility that was part of the Developer Toolkit as a huge crutch, but I didn’t have to use SSIS anymore. Maybe I’ll go into more detail some day, when someone else cares.
The holy grail for shops tied to SSAS is integrating or decoupling as much as possible from the MS stack, into open source technology. All while maintaining the sweet XMLA/MDX support for things like ADOMD etc…
In some recent play projects at work we’ve used SQLite as a DB, and I began searching for possible open source OLAP projects that would allow me to build a “cube” on it. I found Cubulus:
http://cubulus.sourceforge.net/
http://sourceforge.net/projects/cubulus/
It’s pretty cool, built in Python, but its built with a drill-down UI tied right on top of the MDX parser. Probably the best starting point, but there’s still a lot of fun left to try and open it up to parse XMLA requests.
Obviously for enterprise-scale items there’s Mondrian, which everyone seems pretty shy of so far.
http://mondrian.pentaho.com/
However, CHECK THIS GNAR OUT (Xmla4js):
http://wiki.pentaho.com/display/COM/January+13,+2010+-+Roland+Bouman+-+OLAP+and+Analysis+for+web+applications+using+XMLA
It’s a javascript library for XMLA. Do I need to say more? I haven’t looked into it very far yet, but how could it NOT be badass.