Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Query and data model CDE

  1. #1
    Join Date
    Jul 2017
    Posts
    6

    Default Query and data model CDE

    Hi all,

    I'am new on Pentaho and I have to do a Dashboard with CDE.
    I would like to know if I need to make a different query in the datasources panel for each chart that I have to do. Is possible to create a data model (like qlik) to reuse object? How?

    Making single query for each chart would it take too much time?


    Thanks!

    M.

  2. #2
    Join Date
    Jan 2014
    Posts
    189

    Default

    You can create a single CDE datasource and reuse it as many times as you like within that dashboard. You can have 1000 charts fed by the same CDE datasource. Note however that each chart will trigger a query. Since caching is usually enabled, this will not impact the performance significantly.

  3. #3
    Join Date
    Jul 2017
    Posts
    6

    Default

    Quote Originally Posted by crusso View Post
    You can create a single CDE datasource and reuse it as many times as you like within that dashboard. You can have 1000 charts fed by the same CDE datasource. Note however that each chart will trigger a query. Since caching is usually enabled, this will not impact the performance significantly.
    Thank you, but I'm confused on how to do this in the best way because I have a big database.
    I have to do to a "sql over sqljdbc" for each chart that require a different query or is possible to operate in a different and less expensive way?
    I know only how to use the first approach.

    Thanks!

  4. #4
    Join Date
    Jan 2014
    Posts
    189

    Default

    Perhaps I'm not interpreting your concern exactly, but you can parametrize the queries.

    I suppose your question is not sufficiently specific to provide a truly useful answer.

    In a typical dashboard you will have N charts displaying N different perspectives to a given business. You will most likely also have a bunch of selectors that allow you to vary the values of a set of parameters. These parameters will be passed to the queries to produce the specific outcomes. While you would typically write up to N parametrized queries, the number of possible queries generated by all of the combinations would be very large.
    Last edited by crusso; 07-21-2017 at 12:23 PM. Reason: typo

  5. #5
    Join Date
    Jul 2017
    Posts
    6

    Default

    Quote Originally Posted by crusso View Post
    Perhaps I'm not interpreting your concern exactly, but you can parametrize the queries.

    I suppose your question is not sufficiently specific to provide a truly useful answer.

    In a typical dashboard you will have N charts displaying N different perspectives to a given business. You will most likely also have a bunch of selectors that allow you to vary the values of a set of parameters. These parameters will be passed to the queries to produce the specific outcomes. While you would typically write up to N parametrized queries, the number of possible queries generated by all of the combinations would be very large.
    I try to explain better with an example.
    If I have a table with 4 columns and in the dashboard I want to see a chart (or table) for every combination of columns (ex: a dial chart with column 1,2; a table with 2,3 ecc), what is the best approach to do this if the table contain a large amount of rows?
    I have a database on MySQL and now I use a "sql over sqljdbc" for each component.

    Thanks!

  6. #6
    Join Date
    Jan 2014
    Posts
    189

    Default

    I'm sorry, still struggling to understand your exact use case.

    If you are worried about having a large amount of rows (e.g. some degenerate fact table), it typically means you are interested in showing an aggregated result.
    To avoid doing that with SQL, you could build a mondrian model and do some MDX queries over it (which will unroll the necessary SQL calls under the hood and do the necessary aggreations for you).

    To be able to generate charts like "column X vs column Y", create two parameters, "paramColX" and "paramColY", and assign them names of the columns. Then, write a query that accepts two parameters "colX", "colY" and write some SQL/MDX like " SELECT ${colX}, ${colY} FROM ...". On the chart component, you still need to edit the "parameters" option to assign the dashboard's parameter "paramColX" to the query's parameter "colX" (repeat the process for colY).

    For beginners it might be a good idea to do some training: http://training.pentaho.com/instruct...s-fundamentals
    Alternatively, there are some cheap books out there: https://www.packtpub.com/big-data-an...pentaho-ctools

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.