ARTICLE :
 

Pentaho CDF BI CTOOLS: Community Data Access(CDA) Demo with MySQL

by Yogaraj Khanal on October 19th, 2011

Introduction

This article demonstrates the use of CDA,a Pentaho BI CTOOLS for server side data analysis to connect to data sources and view data in UI and use out of the box features available like sorting searching and pagination and many more.

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 CDA out of CTOOLS from Pentaho BI platform.

Detail

I assume you are using CTOOLS installer to reach upto this point and setup sample databases in MySQL like FoodMart and Sakila for our demo purposes so that we are fully open source.If not please follow these articles to reach to this point:
http://codeissue.com/articles/a04e87158bb8552/pentaho-bi-ctools-cdf-cda-cde-saiku-analytics-etc-using-cygwin
http://codeissue.com/articles/a04e979108dea7f/saiku-analytics-datasource-configuration-in-pentaho-cdf-with-mysql-foodmart
http://codeissue.com/articles/a04e8a3b686b0fb/pentaho-cdf-and-mysql-database-integration-using-sakila-sample-database
http://codeissue.com/articles/a04e30b116be00f/integration-of-pentaho-bi-community-dashboard-editor-cde-with-community


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 CDA and what features it has and how it can help your BI needs.Visit this website:
http://cda.webdetails.org/?q=content/about

Let’s go into our task after you have background information with it. First right click in browse section and create new folder as shown in screen shot below.



After you create a folder and give it name you can see in the browse section as shown below in the screen shot.



If you want to see the physical location of the files as you go on they will be physically located here.



After this we click on this new CDE dashboard link as shown in the screen below. Actually CDA and CDE work both together though I will be concentrating on just CDE part today. This will launch the CDE editor as below with defaulted in Layout tab.



Then I want you to navigate you to Data Sources tab and click on the SQL Queries from the menu list to bring up the screen shot as below.



After this step I want you to select sql over sqlJndi sub menu as shown in screen below which will populate additional UI widgets as shown in screen shot below.



The cursor will be moved to Name section with this so let’s go ahead and give some meaningful names what we want to do.For eg like what we did in last article to keep is simple.

selectAllDaysFromFoodMart


This is very important steps so make sure you do it correctly.First can be any name for your convience but second should be the data source model that you have created and saved. If you are not able to follow this please don’t move ahead please go and trouble shoot using this article.
http://codeissue.com/articles/a04e979108dea7f/saiku-analytics-datasource-configuration-in-pentaho-cdf-with-mysql-foodmart

Just concentrate on data source configuration and testing part.You can test this using navigating from top menu like:
File->New data source
See your data source is already there run sample query because we are going to use same query to clear the concept.



Then please click on the query button to bring up the query editor as below.
Write query there you want to use in CDA and click OK button and save.



Now we have to save all the work we did in CDE so please click SAVE button here and give it a name and put it inside the folder we created as shown in screen shots below.



Please do refresh to make the chages you did and load in Pentaho BI



Now you can see the CDA file generated for you by the Pentaho BI platform.You can double click to open it.



At this point you can start using CDA to access the data from server side and use CDA features to sort search paginate and do more stuff out of the box.



You can edit the query you just wrote by right clicking as shown below.



See the yellow highlighted part that you will focus on now others are created by the steps you followed till now. You don’t need to touch unless you want data from other sources too.Please use CDA save and reload button to see the changes.



Query name is same but now you can view the ware house data since it’s another table in same Food Mart data source.



I think from this point you can move forward so I will meet you in next article to show you something new.

Conclusion

This article demonstrates how you can use Pentaho BI CTOOLS CDA part with Pentaho CDF.Keep checking codeissue.com for my next article as I explore more in open source BI. 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://wiki.pentaho.com/display/COM/Community+Wiki+Home
http://type-exit.org/adventures-with-open-source-bi/category/dashboards/community-dashboards/

Author:

Profile Photo
Yogaraj Khanal
Date: October 19th, 2011

Comments:



Profile Photo
Commented by Bagus Prasojo
on November 13th, 2012 at 23:41:31 EST
Good. For now, i know what CDA for


Profile Photo
Commented by Sadakar Pochampalli
on July 16th, 2013 at 03:16:34 EST
nice artical..

Profile Photo
on August 30th, 2014 at 16:46:41 EST
Thank you for this article, it helped me to confirm what I did.