Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: [Mondrian] need to get SQL queries from MDX

  1. #1
    JS Ubei Guest

    Default [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

  2. #2
    Luc Boudreau Guest

    Default 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

  3. #3
    JS Ubei Guest

    Default Re : [Mondrian] need to get SQL queries from MDX

    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>

  4. #4
    Nicholas Goodman Guest

    Default Re: [Mondrian] need to get SQL queries from MDX

    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

  5. #5
    Luc Boudreau Guest

    Default Re: Re : [Mondrian] need to get SQL queries from MDX

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.