ARTICLE :
 

Pentaho CDF BI CTOOLS : Dashboard with clickable bar chart mutli select and data table component

by Yogaraj Khanal on November 14th, 2011

Introduction

This article demonstrates a sample dashboard that implements clickable bar chart and multi select component as selector component for creation of dynamic dashboard and data table as listener component that selects the filter data to show user based on the selection.

Background

Pentaho is the open source business intelligence development platform which has different components integrated with it. You have both open source and commercial version available to support your BI need. This article is scoped to help open source business intelligence developer use CTOOLS to develop a dynamic dashboard.

Detail

The prerequisite for this article is to first install the Pentaho BI CTOOLS which is documented in my previous article below. I have provided the distribution of the freely downloadable source code with this article to help in your exploration and development.

http://codeissue.com/articles/a04e87158bb8552/pentaho-bi-ctools-cdf-cda-cde-saiku-analytics-etc-using-cygwin

Also I have used the simplest query possible from Foodmart database which you can get and up and running using this article.

http://codeissue.com/articles/a04e979108dea7f/saiku-analytics-datasource-configuration-in-pentaho-cdf-with-mysql-foodmart

Though you install whole schema using the script from Foodmart database I am using the simplest table and query to keep simple things simple and hard things possible.


Fig :Pentaho BI with CTOOLS

You just need first article to reach up to this stage but others I just pointed for your references if you are stuck reaching to this stage.

If you want to learn more about what is CTOOLS and what features it has and how it can help your BI needs. Visit this website:
http://ctools.webdetails.org/
Let’s go into our task after you have background information with it. First right click in browse section and create new folder for our new project as shown in screen shot below.



You can see the source code generated from CTOOLS inside this folder which you can download freely from this article
C:\Users\khanaly\Pentaho\biserver-ce\pentaho-solutions\drillDemo



I have made simple Layout using CDE just two rows. In first rows I have two columns with span of 6 for multi select and span of 18 for bar chart component and in second row I have placed my table component. I have just used the minimal required properties to make things simple so that you don’t confuse with the options you have so I am not using any CSS or custom java script for this demo.



For the components part I have used four types of component viz. table to show data multi select and clickable bar chart to pass the parameters for dynamic user interaction and parameter component to define what parameter you want to pass in this case I am passing the days in week for dynamic row selection based on user input.



For making clickable bar chart you have to use java script below.
function(s,c,v){
      Dashboards.fireChange('days',s);
   }

Where S(series), V(Value) and C(categories)
Also notice that you need to make Clickable flag to true.

For the Data Sources part I am using three queries. Two SQLs:one for the selection of distinct days to populate in multi select and one for dynamically passing days parameters in WHERE clause based on user input. I am using MDX generated from Saiku using MDX over mondrainJndi.



The screen shot below shows how you can generate MDX suing Saiku plugin in Pentaho.



Finally you can see the dashboard in action in the screen shot below. Here I have selected three rows using multi select to fetch the three rows from database and display on the screen.



In this screen shot you will see that I am making the selection using clickable green bar chart to select Saturday and populate the row in data table.



I have some issues connecting to oracle database using Saiku standalone instance so wanted to share my finding with you if you are using oracle database.Please use following parameters to connect to Saiku datasource using Oracle database.If you are using Saiku as plugin shouldn’t be any issue.

Please download the source code from here building this dashboard.
type=OLAP
name=my_data_mart
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:oracle:thin:@localhost:1521:yrk;Catalog=/home/bidev/biserver-ce/pentaho-solutions/admin/resources/metadata/Oracle_Datamart.mondrian.xml;JdbcDrivers=oracle.jdbc.OracleDriver;
username=username
password= password

Though it’s nothing fancy or sexy dashboard as you want but this implementation is powerful enough to build sophisticated dashbaord and illustrate the powerful concepts of CTOOLS.I give all credits and thanks to Pentaho Team,Webdetails CTOOLS team and the community forum and IRC ##pentaho team for sharing this tools and technology to the public users for benefits of all.I hope I made some contributions to the users and community where I got a lot by sharing my this sample example for creation of dynamic dashbaord.

I know its difficult to explain everything and make is understandalbe to everyone with different background and understanding.But if you play with working code and review my articles carefully I bet you can make working dashbaorad a reality with CTOOLS in short span of time after you get all the concepts and familierlize with the COOLS.

Conclusion

This article demonstrates how you can use Pentaho BI CTOOLS to create a dynamic dashboard. Hope you have insight into the nuts and bolts of CTOOLS to create dashboard. To make better use of the article please download the source code and save it to see it in action. Please free to send me your comments, suggestions and questions through this website.

References

http://pedroalves-bi.blogspot.com/2011/06/ctools-installer-making-things-fast.html
http://www.cygwin.com/
http://ctools.webdetails.org/
http://wiki.pentaho.com/display/COM/Community+Wiki+Home
http://forums.pentaho.com/forumdisplay.php?80-Community-Tools-CTools
http://webchat.freenode.net/

Download

Click here to download source code

Author:

Profile Photo
Yogaraj Khanal
Date: November 14th, 2011

Comments:



Profile Photo
Commented by ETL Talend
on January 8th, 2012 at 12:32:40 EST
Nice article with good proof of concept easy to understand and refer.

Profile Photo
Commented by Jith Karan
on October 1st, 2012 at 03:37:37 EST
hi,
i created everything except drill through..For me its showing "missing function identifier javascript error ..."
I am showing
" month on rows & revenue on columns & one year parameter in where clause of my mdx query.. "
Can anyone help me..??

Profile Photo
Commented by Jith Karan
on October 1st, 2012 at 04:00:08 EST
hi,
i solved the javascript error,but my drill function is not working till now..i am confused dat how the function parameter is s,c,v is mapping internally with the chart and query..