This article demonstrates the integration of MySQL Sakila Sample Database with Pentaho Community Dashboard Framework(CDF).
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 to integrate MySQL Sakila Sample Database on Pentaho CDF to fulfill their dashboard development BI needs.
As promised in my previous post this article continues to demonstrate the integration of data source part of the Pentaho CDF. Please review my previous article in the link below to get you in this step.
http://codeissue.com/articles/a04e87158bb8552/pentaho-bi-ctools-cdf-cda-cde-saiku-analytics-etc-using-cygwin
Fig I: Pentaho CDF with CTOOLS
I assume you and download and install MySQL server and ready to create sample database schema. If not please do it from here. I am using 64 bit MSI installer for this article.
http://dev.mysql.com/downloads/mysql/
After you follow instructions in the installer you should get MySQL server 5.x up and running. Now it’s time to build the sample database where we can connect and state querying from the Pentaho CDF.
Please download the MySQL Sakila Sample Database from here.
http://dev.mysql.com/doc/sakila/en/sakila.html
Please run sakila-schema.sql to create a schema first and sakila-data.sql to populate the data in schema. If you get stuck you have the instructions and samples in the URL to help you what is expected and samples SQL.
If you USE Sakila; & SHOW TABLES; should see the schema created like this.
Fig II: MySQL Sakila Sample Database
Now let’s start our main task to configure data source.You can configure new data source in two ways.
File->Data Source
OR
Clicking on the New Data Source Icon in the middle of the default pace after you login as shown in figure one above. Please see the third icon from the left.
Fig III:Pentaho CDF database configuration wizard first step
Please select the highlighted option to add your new database.
Figure IV:Pentaho CDF database configuration wizard second step
Please click the +(plus icon) highlight with yellow.
Figure V:Pentaho CDF database configuration wizard third step
You can see that there are two databases now.I will add drivers for PostgreSQL and Oracle which are the other popular used one.You need to put the jar files viz:
ojdbc14.jar (For Oracle) & postgresql-8.1-407.jdbc3.jar (for PostgreSQL)
in
C:\Users\khanaly\Pentaho\biserver-ce\tomcat\lib
&
C:\Users\khanaly\Pentaho\administration-console\lib
to configure Postgresql and Oracle database with pentaho. After you do these steps and restart the biserver-ce you can see wizard like this:
Figure VI: Pentaho CDF database configuration supporting Oracle and PostgreSQL database
Figure VII:Pentaho CDF database configuration wizard fourth step
Here give connection a data source name. Fill in with all values and hit yellow test button
Figure VIII: Pentaho CDF database configuration wizard fifth step
This shows all the connection with fine and shows you port no data source name and database name.
Ok now it’s time for final action. Let’s fire some queries against our database.
Figure IX: Pentaho CDF database configuration wizard sixth step(Very Important)
This is the sample query that I am planning to fire
SELECT * from STORE;
Here is some important observation you should make. First you need to type the data source you created in first step highlighted below otherwise prview buttons won’t be enabled. Second you don’t need semicolon (;) at the end of the query that is why it’s complaining. I will post need working screen shots below.
Figure X: Pentaho CDF database configuration wizard Voila! (Working)
Please make sure you have the tested working connection name and put it as data source name otherwise data preview will be grayed out.
If you configured everything correctly you should see this screen that data source is created and you can start building dashboard or start reporting using it. You can keep the default model or customize it based on your need.See the screen below for more details:
Figure XI: Saving the data source and keeping the default model.
Figure XII: New data source available to use for report, analysis view Saiku analysis and other purposes.
Figure XIII: Showing data from dabase using CDFwithMySQL data source that we created
Hope this helps in your adventures with Pentaho BI.Will post other cool stuff soon. Stayed tuned in codeissue.com
This article demonstrates how you can integrate MySQL Sakila sample database with Pentaho CDF. Please tune into codeissue.com as my exploration with open source BI continues. Please free to send me your comments, suggestions and questions through this website.
About 
