Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: MDX query as parameter ${myMdx} in CDE DataSource

  1. #1

    Question MDX query as parameter ${myMdx} in CDE DataSource

    Dear Experst/Members,

    Short:
    I'd like to use "MultiSelect" component which on "Post Change" fills up a "Simple parameter" with a tested (MDX Query Editor) MDX Query and a component using Listener gives this fully-specified MDX Query as a singe parameter to DataSource.

    May I do it ?

    Detailed:
    I'd like to use "MultiSelect" component which on "Post Change" fills up a "Simple parameter" with a tested (MDX Query Editor) MDX Query.
    Next component is tableComponent which listens an accept this parameter and its DataSource is an "mdx mindrian ocer Jndi" type and get the parameter as "String" from parent tableComponent.

    I set default values like this:
    SELECT NON EMPTY {Hierarchize({[tipus.default].[tipus].[MSFAPN]})} ON COLUMNS,NON EMPTY {Hierarchize({[deptNa.default].[hosp].Members})} ON ROWS FROM [BSz]

    Yesterday evening I could see an example which convert parameter to something. Today I spent more than 5 hours to find again, but it was unsuccessfull.
    I tried to get information from here: http://msdn.microsoft.com/en-us/library/ms145970.aspx
    What I tried:

    StrToMember(),StrToSet()
    In two terminals I follow the tail of "catalina.out" and "pentaho.log" and they say:
    2012-06-27 15:29:35,572 ERROR [pt.webdetails.cda.CdaContentGenerator] Failed to execute: [ pt.webdetails.cda.dataaccess.QueryException ] - Found an unhandled exception: .( Cause [ pt.webdetails.cda.dataaccess.QueryException ] ReportDataFactoryException : Failed to create datasource:Mondrian Error:Failed to parse query 'StrToMember(SELECT NON EMPTY {Hierarchize({[tipus.default].[tipus].[MSFAPN]})} ON COLUMNS,NON EMPTY {Hierarchize({[deptNa.default].[hosp].Members})} ON ROWS FROM [BSz])'; Parent exception: Mondrian Error:Failed to parse query 'StrToMember(SELECT NON EMPTY {Hierarchize({[tipus.default].[tipus].[MSFAPN]})} ON COLUMNS,NON EMPTY {Hierarchize({[deptNa.default].[hosp].Members})} ON ROWS FROM [BSz])'
    Mondrian Error:Internal error: While parsing StrToMember(SELECT NON EMPTY {Hierarchize({[tipus.default].[tipus].[MSFAPN]})} ON COLUMNS,NON EMPTY {Hierarchize({[deptNa.default].[hosp].Members})} ON ROWS FROM [BSz])
    .( Parent [ org.pentaho.reporting.engine.classic.core.ReportDataFactoryException ] Failed to create datasource:Mondrian Error:Failed to parse query 'StrToMember(SELECT NON EMPTY {Hierarchize({[tipus.default].[tipus].[MSFAPN]})} ON COLUMNS,NON EMPTY {Hierarchize({[deptNa.default].[hosp].Members})} ON ROWS FROM [BSz])'
    pt.webdetails.cda.dataaccess.QueryException: Found an unhandled exception:

    Can Somebody send me instructions or a working example?

  2. #2
    Join Date
    Dec 2010
    Posts
    304

    Default

    Hi ltorok,

    - "myMdx" is your simple parameter
    - MultiSelect Component update it on postChange
    - in Datasource "mdx over mondrinaJndi":
    Parameters -> myMdx (name), String (type)
    Query -> ${myMdx}
    - in Table Component:
    Listeners -> myMdx
    Parameters -> myMdx (Arg), myMdx (Value)

  3. #3

    Default

    Dear lukolap,
    Thank you for your answer!
    My idea is to change (I'm going to learn deeply the mdx technology, but at first I've to present something to the leaders) the number of columns when the user selects more than one option from a component. In my simple presentation a "multivalue select" gives back one or comma separated list what I can split by "," -as and build the complex MDX query or a part of it. I would like to build the whole query because stoling mdx samples from MDX Qurey editor or Saiku Editor I can give a possibility to rotate(swap) the table 90 degreee (this is an example only).

    So, at the first round I builded the full mdx query on the Post Change of the multvalus select component and gave a Dashboards.fireChange('myMdx',builtMdx);
    My presentation component was a tableComponent which listened to myMdx and it has also the input parameter.

    The myMdx was defined as Simple parameter component and had a default value :
    SELECT
    NON EMPTY {Hierarchize({[tipus.default].[tipus].[MSFAPN]})}
    ON COLUMNS,NON EMPTY {Hierarchize({[deptNa.default].[hosp].Members})}
    ON ROWS
    FROM [BSz]


    At DataSource (mondrian mdx over Jndi) also defined parameter myMdx and dave the same default value for it.
    Tha DataSource Query : ${myMdx}

    At tbaleComponent I took a hook (Pre Execution) to alert(myMdx); and all of the cases was correct (tested in MDX Query Editor), but in Preview mode was nothing on the screen and the catalina.out and pentaho.log said the same: Cannot parse string or something.....

    I tried to change from string to other of the DataSource input parameter because the log said 'Select Non empty....' .
    I thought the system puts ' around my query and that is the source of the problem.

    -----------------------
    Yesterday evening I changed the mdx build startegy to give the parameters to the head of the query and use the StrToSet(..,hierarchy) mdx function and changed the presentation component( CCC Stacked Area Chart).
    SELECT
    NON EMPTY {Hierarchize(StrToSet('${ptypemdx}',tipus))}
    ON COLUMNS,NON EMPTY {Hierarchize({[deptNa.default].[hosp].Members})}
    ON ROWS
    FROM [BSz]


    When the user select more than one option the chart change what I desired, but this is the half result for me.
    The original is to specify the full mdx query (other cases SQL query shuld be, but I accept if that is unpossuble because of SQL Innections).

    Also an other problem from yesterday evening is (whith this new mdx query ) that the tablecomponent cannot change the number of columns dynamically when the mdx query gives back more columns.

    I hope, I could write more clearly my problem today!


    Than you for your attention, again!
    Have a nice day!

    bye,
    László

  4. #4
    Join Date
    Dec 2010
    Posts
    304

    Default

    I often build mdx queries dynamically, but honestly I've never put a complete query in a parameter.
    I build instead parts of a query and I store them in parameters.
    So, in the datasource query I write for example

    SELECT NON EMPTY {...} ON COLUMNS,
    NON EMPTY {...} ON ROWS
    from [Vendite]
    ${whereMdx}


    Try in this way, because probably the query is not well rendered if its string contains only "${myMdx}".
    For example you could just write

    SELECT ${myMdx}

    where you have removed "SELECT" from "myMdx" value.

    About the problem with Table Component, you could initialize the table with the max number of columns you could need
    and then hide (with jQuery in the draw function) the columns that you don't use.

    Sorry for my English
    Have a nice day, you too

    Luca

  5. #5

    Default

    Dear Luca,

    Your name seems a hungarian name too.
    Thank you for your answer!
    You are the only one who helped me.

    As I wrote, I have (not need) to learn mdx, jQuery and so on.... but with your help I could present something dynamic to my next leader.
    I could use the multi-column mdx with CCC Stacked Area Chart and added a simple Export button to the UI.
    So my leader could export different XLS tables with real connection to the chart representation. It was enough for him to say --> We'll start a Pentaho projekt.

    I've changed MatleSetting.properties (with other gwt errors) and we could open TAB-s with company pages.
    And I'm going to use CST to give exact "work-areas" for our partners based on ROLE-s.

    What I have to say. I can read english with using right-click translators and my ability to write paragraph not so good, but I think we can understand each other.

    Thanks!

    Bye,
    László

  6. #6
    Join Date
    Dec 2010
    Posts
    304

    Default

    It's a Italian name.
    Glad to help and good luck with your project!

    Bye
    Luca

  7. #7

    Default

    Dear Luca,
    Unfortunetly or not I've to join again to an other projekt (HISCOM) and therefore my actual leader wants me to programming in caché ObjectScript and Zen (FrameWork) (InterSystems).

    My local (healtchcare regional leader) wants to continue on this way also, so I hope I'll use fresh knowledge in Pentaho also.

    ----Other ---
    My daughter loves Itali and italians, because she is a roller/speed skater and the European Competition will be here in Szeged in July.
    She always says after any international competition what lovely italians she could met.
    (plus: in the highscool she learns english and italian).

    Have a cold (not so hot) week!

  8. #8
    Join Date
    Jun 2012
    Posts
    24

    Default

    Quote Originally Posted by lukolap View Post

    - "myMdx" is your simple parameter
    - MultiSelect Component update it on postChange
    - in Datasource "mdx over mondrinaJndi":
    Parameters -> myMdx (name), String (type)
    Query -> ${myMdx}
    - in Table Component:
    Listeners -> myMdx
    Parameters -> myMdx (Arg), myMdx (Value)
    Such a great description. Even though in 5.0.1 things have changed a little I had no problem following your instructions. Had a working dash in 5 minutes, thank you, Luca.

Tags for this Thread

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.