Getting started with Community Dashboard Editor in Pentaho|Example

Pentaho provides various tools to create custom dashboards to visualize the big data in the form of PI-charts, Bar chart, line charts, graphs and much more. It provides data integration with different data sources and extracts the analysis report from data sets.Getting started with Community Dashboard Editor in Pentaho, this tutorial explains complete steps to create following dashboard

Getting started with Community Dashboard Editor in Pentaho

Steps to create Pentaho Dashboard

  1. Select Create New option from below PUI screen 
    Getting started with Community Dashboard Editor in Pentaho

  2. Click on Create New -> CDE Dashboard, see in below screenshot 
    Getting started with Community Dashboard Editor in Pentaho

  3. Select a template for your dashboard.
  4. In the given Example, we are using “2 Columns Template” according to our requirement. As give in below structure
    pentaho7
    There is one header, footer and two Panels present in the Dashboard Panel_1 -> for the bar chart and dropdownPanel_2 -> for the Line chart

  5. Click on DataSource option from the panel. Click on SQL Queries -> SQL over sqljdbc, fill property values according to your database connection.
  6. In the given example we created the Data Source

Data Sources –

1. Inspection_data

Query –

SELECT
Center AS SERIES,
COUNT(CASE District_Decision WHEN ‘VAI’ THEN 1 ELSE NULL END) AS VAI,
COUNT(CASE District_Decision WHEN ‘OAI’ THEN 1 ELSE NULL END) AS OAI,
COUNT(CASE District_Decision WHEN ‘NAI’ THEN 1 ELSE NULL END) AS NAI
FROM
fda_inspection_data WHERE EXTRACT(YEAR FROM Inspection_End_date)=${year}
GROUP BY
SERIES, ‘Center’;

Set the Parameters for Inspection_data

pentaho8

2. Data Source – get_all_years

Query-

SELECT DISTINCT EXTRACT(YEAR FROM Inspection_End_date) AS YEAR FROM fda_inspection_data;


3. Data Source- F_and_D_query

Query-

SELECT DISTINCT EXTRACT(YEAR FROM Inspection_End_date) AS SERIES,
COUNT(CASE Country WHEN ‘US’ THEN 1 ELSE NULL END) AS Domestic,
COUNT(CASE Country WHEN ‘US’ THEN NULL ELSE 1 END) AS Foreign_Country
FROM fda_inspection_data WHERE EXTRACT(YEAR FROM Inspection_End_date)<=2016 GROUP BY SERIES;

Creating the Component in the Pentaho

  1. In components Tab, there is multiple types of chart and custom components. Here we are using chart -> CCC bar chart and CCC Line Chart.
  2. And for the Select dropdown, we are using Select ->Select Component.
  3. For creating parameter for select dropdown use generic->Simple Parameter
  4. For parameter properties name – year and value – 2008 (default) 
    Getting started with Community Dashboard Editor in Pentaho

  5. Set properties for select component 
    pentaho10

  6. Set the properties for bar chart 
    Getting started with Community Dashboard Editor in PentahoNote: To make create stacked bar chat, set the property stacked=true.

  7. Set the properties for Line chart 
    Getting started with Community Dashboard Editor in Pentaho-Set plot2DotsVisible – true to see dots on the lines.


  8. The HtmlObject name must be the name set in the Layouts names (column name, HTML name ).
  9. Click on view Icon to see Dashboard.

Please connect via comments or emails for more explanation and queries related to BI, we will happy to help you. www.ease2code.com

Speak Your Mind

*