If there’s one big religious divide within the BI world, greater than the differences between vendors, then the question of whether to use an OLAP database or to query the relational database directly is it. Standing in the pro-OLAP camp as I do I probably have more in common with an Essbase guy than someone who wants to do BI with the SQL Server 2005 relational database exclusively. Anyway, here’s an article by Ralph Kimball in Intelligent Enterprise that sums up the arguments on both sides pretty well:
I’m pleased to see that he makes much of MDX as a plus-point on the side of OLAP; even Oracle’s supposedly all-SQL approach begins to look a bit MDXy when you see the details (see for example Mark Rittmann’s post, also from today: http://www.rittmanmead.com/2007/04/30/obi-ee-time-dimensions-and-time-series-calculations/) although from what I’ve seen MDX still has some important advantages. It’s just a pity that as a language it seems to be fragmenting into different vendor-specific implementations so quickly – for example, see this post from the Panorama blog on SAP BW MDX: http://www.panorama.com/blog/?p=44.
Wonder what Ralph means when he says: "..MDX in its full glory may be too complex for IT personnel to write by hand, or understand a complex application.". Does he mean that it\’s too complex for typical inhouse staff to handle, IT or otherwise (read: external consultant opportunity!); or that it\’s inherently too complex to be hand-crafted at all, so should only be generated via a tool?
– Deepak
You\’re \’inhouse staff\’ aren\’t you? And you\’re pretty good at MDX! I think he\’s making the oft-heard claim that MDX is inherently complex whereas I\’m not sure it is. It\’s different from SQL, and for people that know a lot of SQL it can be very confusing initially, but I don\’t think it\’s more complex than SQL or C# or anything else that IT people deal with on a daily basis. Certainly there\’s a lack of MDX knowledge out there though which also adds to the impression that MDX is \’difficult\’.
In that case, maybe the fact that MDX started out as a Microsoft-only language limited its visibility to a wider audience? At any rate, next week\’s BI Conference might be a good place to hear opinions on the accessibility of MDX, since there should be a broader audience profile than found at typical technical conferences.
– Deepak
I have worked in a reporting shop that was strictly relational and I ended up writing stored procedures that were way more complex than any MDX that I have seen in my days. Not to mention, warehousing is somewhat designed for OLAP reporting and visa-versa. I have not seen many source systems that lend themselves to relational reporting. You still have to build a db for reporting and if you do not follow a methodology in building your tables and your reports you will never be able to get that theoretical 360 degree view of your business. In my experience, unless you are talking to a person who has used MDX and has experienced the flexability and advantages that it gives you when doing analysis it is like talking to a wall sometimes. But once they see the light they never go back.
OLAP or relational, I\’m not going to make up my mind (both are ok). What I would like to see is some real examples what you can do with MDX not possible or easy by using other methods? I mean examples like I have posted in my blog http://jpbi.blogspot.com. Please remember, some of your readers are not yet beyond doubt convinced about MDX. 🙂
It would be a good topic for a future blog entry to compare the strengths and weaknesses of SQL vs MDX. Of course nothing is ever absolutely impossible…
I am looking for an application or tool which makes OLAP and Relational databases seamless. There are lot of things that you need in OLAP application which more relational in nature, especially budgeting and planning apps. These apps need a good front end which has robust realtional features but also needs OLAP components of aggregations, formulas. Are there any tools which are like this.
That would be the Holy Grail! Unfortunately no such tool exists…