Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
An MDX (a language dealing with multidimensional data, in particularly dealing with the cube metaphor) statement can be translated to an SQL (a well-known language for the relational model) statement - Mondrian is one example solution which provides such translation.
I was wondering if there are any available solutions to provide a translation in the other direction, i.e. from SQL to MDX. If not, what would be involved in creating such a functionality?
Mondrian is indeed a good example, in that it's a ROLAP engine (in which MDX statements are translated to SQL which are then fired to the underlying database (other than it caches results, so ends up being a Hybrid approach instead of pure ROLAP).
However, the "translation" between MDX and SQL isn't a straightforward one. A lot of MDX statements require multiple SQL statements, not just one. You can see that from Mondrian's logs (if you have them enabled), where for the same MDX query sometimes several dozen SQL statements are fired, and their results used to feed the next batch of SQL statements (e.g., a first SQL statement gets all customer keys from the customer dimension, a second one creates an IN statement with the customer keys from the previous query).
