[Mondrian] need to get SQL queries from MDX

Dear mondrian community,

In a starting project I plan to use mondrian for MDX queries facilitiy. But in
another part of the project we need to have the SQL resulting queries to use
with an external tool (GIS).
How can I do with the API ?
Idealy, something like :

//********************
Query query = connection.parseQuery(...)
String sql_query = connection.getSQL(query)
//*******************

Is the SqlStatement.execute() a good starting point to explore ?

Best regards,
Norbie



_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Norbie,

The feature you are searching for doesn't exist yet. But good news for you,
it is on the roadmap for this year. We included it in a feature request
called "Explain Query Feature". The end result will do exactly what you are
looking for, and then more. Keep a watch on the following Jira case for
updates. Should you decide to contribute to parts of this feature, don't
hesitate to get in touch with us.

http://jira.pentaho.com/browse/MONDRIAN-856




On Mon, Feb 21, 2011 at 9:04 AM, JS Ubei <jsubei (AT) yahoo (DOT) fr> wrote:

> Dear mondrian community,
>
> In a starting project I plan to use mondrian for MDX queries facilitiy. But
> in
> another part of the project we need to have the SQL resulting queries to
> use
> with an external tool (GIS).
> How can I do with the API ?
> Idealy, something like :
>
> //********************
> Query query = connection.parseQuery(...)
> String sql_query = connection.getSQL(query)
> //*******************
>
> Is the SqlStatement.execute() a good starting point to explore ?
>
> Best regards,
> Norbie
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Hi Luc,

One more time, thank you.
Wonderfull this feature ... but I can't wait !

I will try to evaluate the difficulty to do this myself and if it's too hard or
"time consumer" I will do my multidimensional database exploration without
mondrian.

And what the problem if I just, to begin, add a flag to know if I really want
execute the query (i.e. fetch results) or if I just want the SQL query building
part ?




________________________________
De : Luc Boudreau <lucboudreau (AT) gmail (DOT) com>

You can also see this information in the SQL logs. Not programatically and
it's not a complete solution (for instance , SQL q no 3 changes based on
result of q 2) and caching has to be off to get full SQL.

Check out Mondrian.SQL log4j logging topic/configuration .

Sent from my iPhone

On Feb 21, 2011, at 8:40 AM, Luc Boudreau <lucboudreau (AT) gmail (DOT) com> wrote:

Norbie,

The feature you are searching for doesn't exist yet. But good news for you,
it is on the roadmap for this year. We included it in a feature request
called "Explain Query Feature". The end result will do exactly what you are
looking for, and then more. Keep a watch on the following Jira case for
updates. Should you decide to contribute to parts of this feature, don't
hesitate to get in touch with us.

http://jira.pentaho.com/browse/MONDRIAN-856




On Mon, Feb 21, 2011 at 9:04 AM, JS Ubei <jsubei (AT) yahoo (DOT) fr> wrote:

> Dear mondrian community,
>
> In a starting project I plan to use mondrian for MDX queries facilitiy. But
> in
> another part of the project we need to have the SQL resulting queries to
> use
> with an external tool (GIS).
> How can I do with the API ?
> Idealy, something like :
>
> //********************
> Query query = connection.parseQuery(...)
> String sql_query = connection.getSQL(query)
> //*******************
>
> Is the SqlStatement.execute() a good starting point to explore ?
>
> Best regards,
> Norbie
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

What you are trying to accomplish is more complex than you would expect.
Mondrian does not necessarily translate a given MDX query into a
deterministic list of SQL statements. Imagine the work load and poor
performance if this was the case. So for optimization reasons, Mondrian will
use a heuristic approach in order to figure out the most efficient way to
answer a given MDX query. The SQL queries will themselves be tailored to
take full advantage of the underlying RDBMS facilities. Mondrian will also
represent internally the data tuples / aggregations in a cross-query
reusable cache structure, so the order in which the MDX queries come in will
influence which SQL query get generated/executed.

As you can guess by now, it is a big requirement. There are also
architectural constraints we would require on possible contributions before
we accept to become the stewards of the code. Should you decide to go
forward, I strongly suggest we have an in-depth discussion about this
feature and how it fits in Mondrian as a whole.

Luc


On Mon, Feb 21, 2011 at 11:59 AM, JS Ubei <jsubei (AT) yahoo (DOT) fr> wrote:

> Hi Luc,
>
> One more time, thank you.
> Wonderfull this feature ... but I can't wait !
>
> I will try to evaluate the difficulty to do this myself and if it's too
> hard or "time consumer" I will do my multidimensional database exploration
> without mondrian.
>
> And what the problem if I just, to begin, add a flag to know if I really
> want execute the query (i.e. fetch results) or if I just want the SQL query
> building part ?
>
>
>
> ------------------------------
> *De :* Luc Boudreau <lucboudreau (AT) gmail (DOT) com>
> *À :* Mondrian developer mailing list <mondrian (AT) pentaho (DOT) org>
> *Cc :* JS Ubei <jsubei (AT) yahoo (DOT) fr>
> *Envoyé le :* Lun 21 février 2011, 13h 39min 31s
> *Objet :* Re: [Mondrian] need to get SQL queries from MDX
>
> Norbie,
>
> The feature you are searching for doesn't exist yet. But good news for you,
> it is on the roadmap for this year. We included it in a feature request
> called "Explain Query Feature". The end result will do exactly what you are
> looking for, and then more. Keep a watch on the following Jira case for
> updates. Should you decide to contribute to parts of this feature, don't
> hesitate to get in touch with us.
>
> http://jira.pentaho.com/browse/MONDRIAN-856
>
>
>
>
> On Mon, Feb 21, 2011 at 9:04 AM, JS Ubei <jsubei (AT) yahoo (DOT) fr> wrote:
>
>> Dear mondrian community,
>>
>> In a starting project I plan to use mondrian for MDX queries facilitiy.
>> But in
>> another part of the project we need to have the SQL resulting queries to
>> use
>> with an external tool (GIS).
>> How can I do with the API ?
>> Idealy, something like :
>>
>> //********************
>> Query query = connection.parseQuery(...)
>> String sql_query = connection.getSQL(query)
>> //*******************
>>
>> Is the SqlStatement.execute() a good starting point to explore ?
>>
>> Best regards,
>> Norbie
>>
>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian (AT) pentaho (DOT) org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>
>
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian