Some notes on Pentaho solution for BI with CDE/CDA

A few weeks back, I needed to integraed some of test data with the Pentaho business solution. At one hand I am supprised how powerful and flexible it was, but on the other hand, it saddens me that how poorly it is documented. Maybe it’s because of its business model(kind of like freemium mode). You have choice between commercial version, where you pay and get official support, and the “community version”, with no official support. For the latter, all you can do is searching the incomplete, not-so-well organised documentation or cross your fingers for someone else have the same problem and hve found the solution. It’s kind of problematic if the community is not that large.

Here I will try to put some of my tips here in case someone has the same problem.

1. About ctools, including CDE/CDA/CDF/CDV etc. This is probably the first thing you want to install after the standard Pentaho installation. Either from the marketplace or from the commandline.
2. relationship between the three Panel(layout panel, components panel, data sources panel)
1. you “design” your dashboard in the layout panel, using a building-a-wall-with-bricks system. You can reference the components you created in components panel by naming the “column” as the same string as “htmlobject”
2. Every chart-like component has a property called data source that it get it’s data from.
3. About data source: All those charts needs a data source, with results coming from the data source meet the requirement. In most case with a mysql/MSSql server at the backend, you can use three kind of data source:
1. sql over jndi: you need to config a jndi connection in pentaho before using this one
2. sql over jdbc: you need to make sure cde successfully locate the java jdbc library, which I never figured out the location it looks dispite the long hours I spent trying.
3. scriptable over scripting: this is the most powerful and flexable way of doing it. Bascially, you can do what ever you want(query many times database, re-organising the data etc) using a script called beanshell that is close to java.
4. About parameting the charts: you need to do the following:
1. In the components panel, create a Simple parameter componnet with the name STRING1;
2. In your component which generats the parameter value (for example, a select control), you put the STRING1 in the Parameter property.
3. Put the STRING1 in the DataSource that you want to use this parameter
4. In the data source script you can refer to this pamameter like this:
5. 
   

import org.pentaho.reporting.engine.classic.core.DataRow;

param_value = dataRow.get("STRING1");

Overall, pentaho with cde/cda is quite a powerful tool. I wish it would better documented and has a larger community.