Paul Hernandez playing with Data

Business Intelligence, Data Analysis, Advanced Analytics and more …

Dashboard creation with Pentaho and CDE. Step by step screencast tutorial.

Introduction

This screencast tutorial shows how to create a dashboard using Pentaho and the Community Tools (CTools), especially the Community Dashboard Editor (CDE). A dashboard with two different bar charts is created step by step, including the layout, data sources and components configuration.

It’s very similar to this tutorial: Creating Dashboards with CDE, but it also includes other aspects like the details of the MDX Queries creation or the use of a CCC Bar chart to create a Percentage Stacked Bar Chart with a Line. This type of chart is possible since the latest CDE versions.

To install Pentaho + CTools you can follow this step by step guide from the creators:

http://pedroalves-bi.blogspot.com/2011/12/back-to-basics-step-by-step-pentaho.html

The tutorial is divided in four parts and the source files are available.

Part 1. A Bar Chart with several measures

A new CDE dashboard is created and a CCC Bar Chart is included. The data source of the chart is a MDX query over the SteelWheelsSales cube, which comes with the default Pentaho BI Server.

Part 2. A Stacked Bar Line Chart by percentage

The second part introduces how to create a Stacked Bar Line Chart by percentage. The design of the MDX query is showed including the use of calculated members. The SteelWheelsSales cube is used again.

Part 3. Parameterizing the MDX queries

In this part a parameter is added to let the user select different measures to be displayed in the charts.

Part4. Charts tuning

To finish, some properties of the charts are used to change their appearance like colors, text fonts, string formats, etc.

Get Source Files

Hope you find it useful!

←
→

25 responses to “Dashboard creation with Pentaho and CDE. Step by step screencast tutorial.”

  1. pedro Francisco Avatar
    pedro Francisco

    hello I tried to download your example and after regsitrarme anywhere. tells me that the file you want or not I have access or are removed

    Reply
    1. pauldj54 Avatar
      pauldj54

      Hi Pedro, please try again and let me know if you can do it.

      Hola Pedro, intenta de nuevo y dime si te ha funcionado.

      Kind Regards

      Reply
  2. JohnAlex Avatar
    JohnAlex

    Cordial Greeting.
    First of all thank you very much for the contribution.
    I am working with pentaho with version 3.9 and CDE (CDE-bundle-1.0-RC3).
    I wonder which version you have both platforms at the time of the tutorial, as for example in part 2 of video tuto, modify a property of “CCC Bar Char” called “Bar Line” and I have none in addition to completing and implementing the “source” did not deploy anything in the “preview” and asked whether this property is due or that it should be.
    Thank you very much.

    Cordial Saludo.
    Primero que nada muchas gracias por el aporte.
    Estoy trabajando con pentaho 3.9 y con la versión CDE (CDE-bundle-1.0-RC3).
    Me gustaría saber que versión tiene ustedes de ambas plataformas al momento de realizar el tutorial, ya que por ejemplo en la parte 2 del video tuto, modifican una propiedad del “CCC Bar Char” que se llama “Bar Line” y yo no la tengo, además al terminar y aplicar el “source” no me despliega nada en el “preview” y quisiera saber si es debido a esta propiedad o a que se debería.
    Muchas Gracias.

    Reply
    1. pauldj54 Avatar
      pauldj54

      Hi John,
      There are several CDE releases after the CDE-bundle-1,0-RC3
      For the tutorial I used the Pentaho BI Server 3.7, but you only have to upgrade the CDE version. The barchart component of the newer versions has the “barline property”. You can read this post from the authors that explains how to do the upgrade:

      http://pedroalves-bi.blogspot.com.es/2011/12/back-to-basics-step-by-step-pentaho.html

      Basically you only have to run the ctools-installer.sh
      If you are a Windows user you could open the ctools-installer.sh file with a text editor, see what the script does and then do the same tasks by yourself.

      Kind Regards

      PD: Con un poco de paciencia lo lograrás, si tienes más preguntas no dudes en escribir.

      Reply
  3. Paulo Silva Avatar
    Paulo Silva

    Hello,
    Thank you for your post.
    Can you help me with on thing?
    When I do a bar-line chart bars and set both origins two false bars appears out of chart area and line too.
    I need that the both (bar+line) have a same scale.

    Thanks,

    Reply
    1. pauldj54 Avatar
      pauldj54

      Dear Paulo,

      Right now I’m working in another project with a different technology. I don’t have an enviroment set to test a solution. I think in the Pentaho Forum you will find a possible solution for your needs: http://forums.pentaho.com/forumdisplay.php?80-Community-Tools-CTools. When I have enough time I would like to post a similar tutorial but using the graphic components of the Pentaho Report Designer instead of the CCC. It is possible to embedded in your Dashboards.

      Kind Regards

      Reply
      1. Paulo Silva Avatar
        Paulo Silva

        Hellho,
        Thanks for your quick response.
        I already am part of the pentaho forum and I’m there looking if there is similar problems like my.

        Based on my experience, it is easy to create graphs in pentaho report design. I do not know is how to integrate them in the dashboard. Your future tutorial will be very helpful.
        Keep up the good work.

        King Regards,

        Paulo

  4. Rob Avatar
    Rob

    This is awesome work. Thank you very much.

    I downloaded the samples (thanks again). I added expanded the rar and copied the folder (demo-1) to my solutions folder. I can see the sample solution folder on the browser list. When I click on the folder I see the cda files but not the actual dashboard file. The samples in Steels Wheels > Dashboards are fine.

    Is there some step I am missing. I use the all powerful joe/password login. BI Server 3.8.

    Reply
    1. pauldj54 Avatar
      pauldj54

      Hi Rob, you’re welcome!

      Which version of the C-Tools are you using?
      I think there are several changes in the C-Tolls since I created the demo. I recommend you to create a simple dashboard with your installed version and use my screencast as a guide. Then if it works you can increase the complexity of the dashboards. If not you’ll have to troubleshooting the C-Tools installation.
      By the way, can you open the cde dashboard sample?

      Let me know if you have any trouble.

      Kind Regards,

      Paul

      Reply
  5. pdarscht Avatar
    pdarscht

    Hola Paul!
    he diseñado un dashboard en un bi-server y ahora debo migrarlo a otro. ¿Me puedes orientar en cuanto a los pasos a seguir?
    Para empezar, ¿cómo hago para que al copiar el archivo en pentaho-solutions éste quede visible en el panel de navegación del servidor?
    Muchas gracias desde ya!

    Reply
    1. pauldj54 Avatar
      pauldj54

      Hi pdarscht,

      When you want to move a dashboard from one server to another please don’t forget to copy the 3 files with the following extensions: .cda, .cdfde and .wcdf
      Example: if your dashboard name is sample_dashboard1, you need to move to the desire folder in the new server the files: sample_dashboard1.cda, sample_dashboard1.cdfde and sample_dashboard1.wcdf
      Then, you may need to clear some caches in the BI server to see your dashboard in the Navigation Windows.

      Hope that helps. Kind Regards.

      Reply
  6. Anu Avatar
    Anu

    I am using Pentaho business analytics 4.5 with latest version of ctools installed.

    I created few dashboards and saved them in a folder. Now I want to edit those dashboards, i need to add some more components, change layout or change sql query used. But how the dashboard is edited?
    When i saved the dashboards, i can see .cda and _tmp.cda files of my dashboards. Further when i select them and click on edit it opens into CDA and shows XML code. I want it to open in CDE console,so that i can make the changes I want. Please guide how to do this.
    Is there anything specific to be done while saving the dashboard like saving into .cde format??Please guide

    Reply
    1. pauldj54 Avatar
      pauldj54

      I´m sorry Anu, but I´ve never worked with Pentaho Business Analytics before. I’ve only used the community versions of the Pentaho BI Server. In any case, you should see not only the CDA icons in the windows browser but also the CDE icons. How did you install the CDE. Are you able to see the CDE Sample folder?

      Kind Regards

      Reply
  7. Constanza Avatar
    Constanza

    Hola
    Estos pasos me ayudaron bastante para empezar a hacer dashboard, pero mi actual problema es tratar de usar “Dial chart component”, he revisado varios lugares y no me ha resultado.
    Si tú supieras de algún ejemplo para CDE que exista para basarme en ese o los pasos de cómo se configura el componente lo agradeceré, ya que es para un trabajo de fin de año en mi universidad.

    Muchas gracias por cualquier ayuda.

    Reply
  8. Gaurav Avatar
    Gaurav

    How would I use this Dashboard in an external web app. like a portlet for example?

    Reply
    1. Paul Hernandez Avatar
      Paul Hernandez

      Hi Gaurav,
      I’ve not implemented such a solution but maybe you can take a look here:
      Embedding Pentaho dashboard framework in existing web application

      What I did is embed Pentaho Reports into a Java Web Application. There is a very useful book that I strongly recommend:
      Pentaho Reporting 3.5 for Java Developers

      Kind Regards.

      Reply
  9. rahul tiwari Avatar
    rahul tiwari

    hello Paul,
    your post is amazing. But I am stucked in a serious issue. I have developed a CDE dashboard using JDBC query in my local BI server 4.8 now I want to publish this Dashboard to Production server of pentaho. But when I am copying these files to production BI server then I am unable to get an output. Please help to migrate Dashboard from one BI server to another.

    Reply
    1. Paul Hernandez Avatar
      Paul Hernandez

      Hi Rahul,

      I remember I faced some similar problems the first time that I deploy dashboards in the production enviroment. Are you moving the complete folder where the dashboard is stored or only the specific file(s)? Can you please take a look in the Tomcat log and search any error message when you try to open the dashboard?

      Kind Regards,

      Reply
  10. Daksh Singh Avatar
    Daksh Singh

    Hi Paul,

    I have one query. The files which are created, I want to take backup of it. can you tell me where it is stored in pentaho folder.

    Reply
    1. Paul Hernandez Avatar
      Paul Hernandez

      Hi Daksh,
      unfortunately I posted this tutorial about 4 years ago and several new versions were release since that. I am not working currently with this technologies and I don’t have an environment installed in my computers in order to have a look and answer your question. Please refer to the official documentation or the Pentaho forum. I am sure you will get an answer.

      Reply
      1. Daksh Singh Avatar
        Daksh Singh

        Hi Paul,

        Thank you for the update.

  11. Harold “D” TAMO Avatar
    Harold “D” TAMO

    Many Thanks for this tutorial. Please can you check this SO question ? http://stackoverflow.com/questions/38529259/the-good-way-to-create-pentaho-cde-dashboard
    It will be much appreciate if you can give some answer. Thanks again

    Reply
  12. mahdie Avatar
    mahdie

    Hi Dear Paul
    Thanks for your good tutorial.
    I’m new with pentaho and i need your guidance.
    I want to show the information of the selected year and two years before it as 3 line in one line chart.For example when the user select 2009 the information of 2009,2008 and 2007 show to him.
    I don’t know how to do this.
    would you please guide me?
    Thanks.

    my query is like this:
    select month=case SUBSTRING(ray.SaleDocGnrlAdjctve.ExprDate,5,2)
    when ’01’ then ‘farvardin’
    when ’02’ then ‘ordibehesht’
    when ’03’ then ‘khordad’
    when ’04’ then ‘tir’
    when ’05’ then ‘mordad’
    when ’06’ then ‘shahrivar’
    when ’07’ then ‘mehr’
    when ’08’ then ‘aban’
    when ’09’ then ‘azar’
    when ’10’ then ‘dey’
    when ’11’ then ‘bahman’
    when ’12’ then ‘esfand’
    END
    , Sum (ray.SaleTrnsHdr.TtlPrcEqlRial) AS TotalSale
    From ray.SaleTrnsHdr INNER JOIN
    ray.SaleYear ON ray.SaleTrnsHdr.SaleYearVCode=ray.SaleYear.VCode INNER JOIN
    ray.SaleDocGnrlAdjctve ON ray.SaleTrnsHdr.VCode=ray.SaleDocGnrlAdjctve.SaleTrnsHdrVCode
    INNER JOIN ray.County ON ray.County.County=ray.SaleDocGnrlAdjctve.County INNER JOIN
    ray.Province ON ray.County.Province=ray.Province.Province
    where (ray.SaleTrnsHdr.SaleDocTypVCode=10000006) AND (ray.SaleYear.Year=${selectedYear})
    GROUP BY SaleDocGnrlAdjctve.ExprDate;

    Reply
    1. Paul Hernandez Avatar
      Paul Hernandez

      Hi Mahdie,
      after a quick view I would like to ask you, which rdbms are you using? I think you could rewrite your query using date time functions and also windowing functions to avoid these inner joins.
      Kind regards, Paul

      Reply
      1. mahdie Avatar
        mahdie

        Ok,thanks for your quick answer. i just can show one line for the selected year in line chart.
        .For showing 3 lines in the chart what is the solution?

Leave a reply to pauldj54 Cancel reply