ARTICLE :
 

Saiku Analytics Datasource configuration in Pentaho CDF with MySQL foodmart database for OLAP analysis

by Yogaraj Khanal on October 13th, 2011

Introduction

This article demonstrates the configuration of Saiku Analytics OLAP data source with Sample FoodMart Database with Pentaho Community Dashboard Framework(CDF) as plugin.

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 configure MySQL Foodmart Sample Database data source on Pentaho CDF to fulfill OLAP data analysis and dashboard development BI needs.

Detail

Saiku Analytics is the next generation open source OLAP tool.You can learn more about it in their site and play around with their demo here.
http://www.analytical-labs.com/

Saiku Analytics comes in standalone version and as a plugin project with Pentaho CTOOLS.Please follow my previous posts on installing configuring pentaho in the links below.
http://codeissue.com/articles/a04e8a3b686b0fb/pentaho-cdf-and-mysql-database-integration-using-sakila-sample-database
http://codeissue.com/articles/a04e87158bb8552/pentaho-bi-ctools-cdf-cda-cde-saiku-analytics-etc-using-cygwin

After following the steps in those articles you should see new Pentaho User Console(PUC) something like this:



First step is to download the MySQL Foodmart Database from this site.
https://sites.google.com/a/dlpage.phi-integration.com/pentaho/mondrian/mysql-foodmart-database



Then issue the tar command to unzip the file as shown in highlighted yellow color second line from last.

Before you run the script you need to create database please issue command as in screenshot.



Also make sure you use default database as foodmart to run script and its empty.



Please use SOURCE command to run the script and verify that all tables are created by Show tables command as in screen shot.



You can verify that data are there using select * or count(*) command



At this point database part is done not let’s if we can see this data in Pentaho BI server.
Provide all the information below to connect to the database we just created.



Make sure everything is ok before you proceed further you should be able to see the top most screen.



Now let’s try query with our new data source that we have just created. We should be able to see the same data that we queried to database directly from command line above.



Now after you do finish you will see the screen below conforming the creation of the new database. I am choosing default model for the purpose of this tutorial but you can customize based on your need.



You will see customization screen like this if you want to customize it.



This is the good point to restart our biserver to use our new database.
After than when you click on New Saiku Analytics icon you can see that we can see our newly configured data source for our use.You can drag and drop or double click for row and columns selection.



You can see the real time OLAP dashboard charts in line bar stack bar and pie charts for real time analysis of data as shown in screen shot below.



I feel this gives you full introduction and real time configuration use of Saiku in Pentaho.Saiku Analytics also can be used as standalone tool outside of Pentaho.In Pentaho its works as plugin.It’s a great tool and performance is very fast hope it solves BI and other kind of data analysis need like drill down swapping axis and you can write your own MDX queries of you want more out of it.

Conclusion

This article demonstrates how you can integrate Saiku Analytics MySQL Foodmart sample database for OLAP analysis 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://wiki.pentaho.com/display/COM/Community+Wiki+Home
http://www.analytical-labs.com/
https://sites.google.com/a/dlpage.phi-integration.com/pentaho/mondrian/mysql-foodmart-database

Author:

Profile Photo
Yogaraj Khanal
Date: October 13th, 2011

Comments:



Profile Photo
Commented by ETL Talend
on October 15th, 2011 at 07:46:48 EST
I am getting problem it say's there is error creating/updating the model and when i see the logs it says serialization error.Any clues what I might be doing wrong?


Profile Photo
Commented by Yogaraj Khanal
on October 15th, 2011 at 12:30:44 EST

Profile Photo
Commented by Bak Wim
on May 30th, 2013 at 19:05:32 EST
Hi Khanal,
I tried to build a dashboard with CDE plugin.I have a problem in the connection to the datasource.
My database is oracle 10g. first I have to use Jndi or Jdbc? and what about the configuration?
Pleaaaaase help me it's urgent :'(
Thanks in advance

Profile Photo
Commented by Yogaraj Khanal
on July 11th, 2014 at 10:31:37 EST
In order to save data source you have to first test connection using New->Data Source using PUC if that is ok you have to select datasource name expectly in SQL editor and write query otherwise it doesn't get saved for CDE to use.
Then you can either use JDBC or JNDI.