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?

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.

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.

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.

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.

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).