The Good way to create pentaho cde dashboard

Question

Pentaho version : bi server CE 6.1

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

What i have done so far

From this data model

I already created a dynamic chart with "sql over sqljdbc" datasource. Here is my query (and the result behind in picture)

    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;

Dashboard generated by the query - Form number by date, type and region (set dynamically)

What I want to achieve

I want to do this kind of chart : community.pentaho.com/ctools/ccc/#type=bar&anchor=small-multiple-bars or community.pentaho.com/ctools/ccc/#type=bar&anchor=stacked-bar (sorry i don't have enough reputation to post more than 2 links) with a "sql over jdbc" datasource

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:

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;

)

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

What i want to know

The good way to do cde chart on pentaho :

how the query need to be formatted ? (how fields are organised on dashboard, number max of fields...)
what is the difference between mdx queries and sql queries and purpose ?
what is the best way to do chart between those two types (mdx and sql) ?
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 ?)

Thank you for your answers.

score 0 · Answer 1 · answered Jul 25 '16 at 13:09

First of all you should realize that you're asking alot here. Having said that you've pretty much done what I did when I first started with Pentaho which was experiment. Alot.

Regarding your questions I have some links which should help you (if you haven't checked them already)

The first link is a very good blog on which I have found several answers regarding dashboards. The second link is more of an overal tutorial.

There is no general "best way" (apart from applying general best practices ofcourse) for creating dashboards. I suggest you keep trying (getting to know all of the properties and settings along the way) and find out what method works best for you.

Regarding your questions about MDX and Mondrian, I haven't had much experience in these area's but as I understand it MDX queries are based off of Mondrian cubes which you prepare in the Mondrian Schema Workbench of Pentaho.

http://mondrian.pentaho.com/documentation/olap.php

I believe this should answer (atleast some of) your questions. Trying many different things and experimenting will get you quite far as you'll catch up with plenty of small things one at a time.

Thanks for your answer dooms. The holowczac blog was one of the first I read. the pentaho-bi-suite one also but not this article so i will take a look. I am aware that i'm asking a lot but as a newbie, I don't even know what question to ask first :). I think I got a simple one : do you think that i can do this kind of dashboard with sql over sqljdbc datasource ? If i could have a demo with a sql query it will help a lot. If someone else could answer the others questions, please it will be much appreciate. — Harold TAMO, Jul 26 '16 at 10:20
or how can I make the webdetails examples work on my instance (where to put the code) — Harold TAMO, Jul 26 '16 at 11:03

score 0 · Answer 2 · answered Aug 16 '16 at 14:42

I will elaborate a little bit on this. As dooms stated, you ask a lot of things here but I am glad you are trying to create some great dashboards.

In order to format charts and tune them, I remembered I had to learn some JavaScript/JQuery.
The difference between SQL and MDX. They are completely different, even when sometimes the syntax looks similar. You use SQL to query relational databases whereas MDX is used to query Cubes. If you don’t have cubes in place you need to use SQL of course. If not, you should ask the cube developer to introduce you to this world. Basically cubes are good at aggregating data and allows to easily interact and perform ad-hoc analysis, it is intended for business analyst to let them better explore the data. I am a MDX fan, but I would recommend you to explore new alternatives to multidimensional cubes, like tabular models or other in-memory technologies.
The best way to do a chart has nothing to do with MDX or SQL. It depends where your data is stored. The most important thing is to have a good data model behind.
Again, depending on your architecture, you should have a multidimensional model in your data mart, without snow flake if possible. That allows you either to build easy SQL queries and a straight forward cube design. Designing cubes required some extra skills. I would try to have a clean data model and then start to evaluate if a cube is required.

I hope I give you some lights, it is not easy to answer the broad questions you asked. Important is to define the scope of your project.

Kind Regards,

The Good way to create pentaho cde dashboard

2 Answers2