ARTICLE :
 

Pentaho CDF and MySQL database integration using Sakila Sample Database

by Yogaraj Khanal on October 3rd, 2011

Introduction

This article demonstrates the integration of MySQL Sakila Sample Database with Pentaho Community Dashboard Framework(CDF).

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 to integrate MySQL Sakila Sample Database on Pentaho CDF to fulfill their dashboard development BI needs.

Detail

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

Conclusion

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.

References

http://dev.mysql.com/doc/sakila/en/sakila.html
http://wiki.pentaho.com/display/COM/Community+Wiki+Home

Author:

Profile Photo
Yogaraj Khanal
Date: October 3rd, 2011

Comments:



Profile Photo
Commented by Kamoe -
on January 12th, 2012 at 03:01:27 EST
i try this tutorial with my existing database, but i keep getting a problem
i've created the connection and when i test it, the connection test is ok
but when i use the connection and try to write the query, it always said that
"Query validation failed:null"
what should i do then?

*i'm trying to put query on SampleData connection and it works well


Profile Photo
Commented by Kamoe -
on January 12th, 2012 at 03:04:13 EST
i try this tutorial with my existing database, but i keep getting a problem
i've created the connection and when i test it, the connection test is ok
but when i use the connection and try to write the query, it always said that
"Query validation failed:null"
what should i do then?

*i'm trying to put query on SampleData connection and it works well

Profile Photo
Commented by Yogaraj Khanal
on January 12th, 2012 at 16:32:27 EST
Kamoe,
The common mistakes are putting ; in the end
not selecting created data source name before writing the queries.
Can you test your query using just command line tool or GUI tool for eq Query browser if you are using MySQL or pgAdmin if you are using postgreSQL and sqldeveloper if you are using oracle all these are freeware tool.
All you have to save your data source if you plan to use it later again it will update model based on your query may be you missed this step.
If you still get the issue can u post your query i can check if there is any issues with it?SampleDate is the existing database in Pentaho that you are trying to use?