I have a long experience with SQL, but recently began working with datawarehouse and OLAP technologies: building fact and dimension tables, that then are queried using MDX (MultiDimensional eXpressions).

The problem is that MDX works with a completely different logic compared to SQL, and it's a whole new learning curve even for someone with a strong SQL background.

Yes, MDX allows you to do things that would be hard or almost impossible with plain SQL. But sometimes it's frustrating to be hours around an MDX to do something you know you could achieve in minutes using SQL (ok, you can tell me to RTFM ...).

But why go on to the trouble of creating a new completely different language when you could build on SQL, extend it to add the features needed by OLAP applications?

  • 1
    MDX is just one way to do the job. You have a choice. The problem you are facing is typical in IT. Each vendor does things their own way and developers are supposed to have experience in everything! – NoChance Nov 7 '12 at 16:34
  • I prefer a star over the Cube/MDX. It's basically just data stored in an aggregated format for performance. eg Sum, Avg per City, State, Zip. It's no problem to further aggregate the pre-aggregated data with SQL. – mike30 Nov 8 '12 at 14:33

Each database vendor has their own set of proprietary SQL extensions to achieve certain piecemeal goals. But none of these extensions have achieved widespread adoption like SQL has.

If your goal is to create enhanced functionality in a standards-based way, you have two options. You can either go to the SQL standards committee and make your case (which could take years), or you can create your own standard and hope the community adopts it. Vendor extensions on the SQL standard have not succeeded in this way.

So Microsoft, instead of taking a piecemeal approach, decided to go all in and create a comprehensive solution. And it worked; the industry adopted it as the go-to solution for OLAP querying. Ergo, a defacto standard, without the committee pain.

MDX is a de-facto standard, created by a single vendor. Assuming you can get broad buy-in, you have eliminated the need for design by committee, and saved yourself a lot of time, effort and money.

  • Don't all major SQL vendors have a whole bunch of nonstandard extensions already? Not everything has to be written down in a standard before being implemented. – user7043 Nov 7 '12 at 16:41
  • Yes, for the same reasons I've stated here. The point is, if you can get something to be adopted by the user community at large, you get the benefits of standardization without the pain. – Robert Harvey Nov 7 '12 at 16:42
  • I'm sorry, perhaps I'm communicating badly. Let me get this straight. The question asks why the vendor invented a whole new language, instead of re-using parts of an existing standard (or equivalently extending their notion of SQL). You say they'd have to extend the existing standard. I find this beside the point: They can just start with standard SQL and add stuff as needed, without any standard, and get precisely that: A non-standard extension that is nevertheless SQL-like (as OP wants). – user7043 Nov 7 '12 at 16:43
  • OK. Let's take your example: non-standard extensions. All of the database vendors have them. Are they compatible with each other? Nope. If, however, you create a side-technology that gains wide acceptance, you can use it with all database vendors. – Robert Harvey Nov 7 '12 at 16:46
  • From the Wikipedia article on MDX: "While it is possible to translate some of these into traditional SQL, it would frequently require the synthesis of clumsy SQL expressions even for very simple MDX expressions. MDX has been embraced by a wide majority of OLAP vendors and has become the standard for OLAP systems." – Robert Harvey Nov 7 '12 at 16:48

I think the reason is that OLAP databases (which are what MDX seems to be intended for use with) are really NOT just relational databases. They are somewhat different (I've no experience with them, so the details escape me at present), and a different mental model is useful for interacting with them (even if it's really a relational DB underneath).

The problem you are running into is that you're seeing the OLAP database as a relational DB, and so you want to use the appropriate language (SQL) to query the relational database. But it's NOT a relational DB, it's something similar, but not quite the same. It's structured for a different mental model, and the MDX query language fits that model better than SQL.

  • this looks like a reasonable quess. Wikipedia article mentions that OLAP databases... "borrow aspects of navigational databases, hierarchical databases and relational databases" which could indeed make one want a mental model other than offered by SQL. – gnat Nov 7 '12 at 18:06

why go on to the trouble of creating a new completely different language when you could build on SQL, extend it to add the features needed by OLAP applications?

I share your pain in the MDX learning curve; having used SQL for some time, my company decided to adopt Analysis Services and create OLAP cubes to provide the ability to do rapid data analysis. I've been heavily involved in this and have had some trouble picking up certain bits of MDX.

That said, I think it's unfair to brand MDX as unnecessary. It does provide benefits, but only if you don't try to use it like you would SQL. Indeed, the reason it's tricky to pick up is because often there isn't a direct equivalent in SQL, so you're trying to learn the idea as well as the syntax.

There's an unhelpful vein of thought among some team members when it comes to retrieving data: OLAP cubes are an "upgrade" over traditional SQL tables. This isn't true, OLAP/MDX are good at certain things but traditional tables/SQL are better for others. Seeing the two as equivalent or different versions of the same thing isn't helpful, at least conceptually in my experience.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.