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?
2
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.
7
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.
1
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.