Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
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! –  Emmad Kareem 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
add comment

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer
    
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. –  delnan 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). –  delnan 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
show 2 more comments

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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