title: The Good way to create pentaho cde dashboard
body: <p><strong>Pentaho version : bi server CE 6.1</strong></p>
  
  <p>I'm new to pentaho universe and I found myself stuck in finding documentation to create a cde dashboard. Just to be clear, I have no idea of what is the good way to create cde dashboard, but i tried many things based on tutorials found pretty much everywhere</p>
  
  <p><strong>What i have done so far</strong></p>
  
  <p><a href="http://i.stack.imgur.com/CWo3y.png" rel="nofollow">From this data model</a></p>
  
  <p>I already created a dynamic chart with "sql over sqljdbc" datasource.
  Here is my query (and the result behind in picture)</p>
  
  <pre><code>    SELECT (select survey_type from survey where id = pr.form_type) as "form type",
      pr.date as "Date",
      count(pr.id) as "Form number" 
      FROM result pr
      inner join district pd on pr.district_id=pd.id
      inner join departement pdep on pd.departement_id=pdep.id
      inner join region pre on pdep.region_id=pre.id
      WHERE pre.region_text = ${region}
      GROUP by date,form_type
      ORDER by date;
  </code></pre>
  
  <p><a href="http://i.stack.imgur.com/As7ql.png" rel="nofollow">Dashboard generated by the query - Form number by date, type and region (set dynamically)</a></p>
  
  <p><strong>What I want to achieve</strong></p>
  
  <ol>
  <li><p>I want to do this kind of chart : community.pentaho.com/ctools/ccc/#type=bar&amp;anchor=small-multiple-bars or community.pentaho.com/ctools/ccc/#type=bar&amp;anchor=stacked-bar (sorry i don't have enough reputation to post more than 2 links) with a "sql over jdbc" datasource</p></li>
  <li><p>Can anyone give me an example of sql request to achieve that ? (preferably with the sql request given up on this post with some modification.I tried this but it does not work as expected:</p>
  
  <pre><code>SELECT (select survey_type from survey where id = pr.form_type) as "form type",
      pr.date as "Date",
      pre.region_text as region,
      count(pr.id) as "Form number" 
      FROM result pr
      inner join district pd on pr.district_id=pd.id
      inner join departement pdep on pd.departement_id=pdep.id
      inner join region pre on pdep.region_id=pre.id
      GROUP by date,form_type,pre.id
      ORDER by date;
  </code></pre>
  
  <p>)</p></li>
  <li><p>And where can i put the code given behind this example to previsualize it in my own instance of pentaho ? I need to know how to reproduce it</p></li>
  </ol>
  
  <p><strong>What i want to know</strong></p>
  
  <p>The good way to do cde chart on pentaho : </p>
  
  <ul>
  <li>how the query need to be formatted ? (how fields are organised on dashboard, number max of fields...)</li>
  <li>what is the difference between mdx queries and sql queries and purpose ? </li>
  <li>what is the best way to do chart between those two types (mdx and sql) ?</li>
  <li>how can i transform my relational database in mondrian cube if i want to use mdx queries (or what i should do is to redesign the database in datawarehouse using kettle ?)</li>
  </ul>
  
  <p>Thank you for your answers.</p>