Image Locations for Pentaho Reports 3.0

My experience with pentaho's report designer is that at design time, images used in the report can be at any arbitrary location. On publishing them to the XML format, each image used in the report design (.report file) is placed in the same directory as the output file but with a new name in the format - Report_staticImage[N].[ext]. N is a number from zero depending on the number of images while ext is the original file extension e.g png.

This behaviour is bound to cause trouble for someone using different locations for the development and production environment. Ideally, the production environment should be setup to reflect the local or vice versa but sometimes this is not possible. To get round this, the production path should be created on the development environment and the desired solution folder linked to the repository location.

For example, let the production path be /path/to/prod/pentaho-solutions/solution1. In this case, the solution1 is the directory containing .xaction files and report templates (.xml). Let the development path be /path/to/dev/pentaho-solutions. A link should be created from the duplicated/replicated production path to the actual repository location on the development environment.

This means that the report designer and pentaho design studio on development will manipulate the files in /path/to/prod/pentaho-solutions/solution1, resulting in paths that will match the production environment on deployment. The local pentaho server, which in my case knows the repository location as /path/to/dev/pentaho-solutions, can access the solution directory as a link - /path/to/dev/pentaho-solutions/soft-link-to-solution1.

Making ProxyPassMatch work with Location in apache2

I have recently created a configuration where apache2 proxies requests to pentaho running on a tomcat backend. The first problem I faced with the ProxyPassMatch directive is to do with the way the url pointing to the backend is written. Specifically, using a url that includes a non standard port like 8080 may result in a ProxyPass Unable to parse URL error. I fortunately found a workaround for that bug on the apache bug list.

When ProxyPassMatch is enclosed in Location - the first argument - which is a regex pattern, is left out. It is used as an argument to the Location directive instead.

The url provided to the enclosed ProxyPassMatch should be combined with the expected result from regex pattern. This is done using $n where n is count of parenthesized matches starting at number 1.
If the parenthesized matche(s) are not provided to the url argument of ProxyPassMatch, the whole matching string will be concatenated with that url and this may result in an unintended path.

Example

<Location ~ ^/(pentaho.*)$>
    Order Allow,Deny
    Allow from All
    ProxyPassMatch ajp://127.0.0.1:8329/$1
</Location>

Restarting Pentaho Automatically - the quick and dirty way

I look after a Pentaho 2 deployment which manifested a pattern of freezing after about a week. That appears to be a weakness in the code where an object grows bigger and bigger and is not garbage collected, leading the JVM to hang while attempting a full GC.

Not having the expertise to profile the memory usage on the pentaho system, I decided to have the application server restart every few days. The first issue I faced is that the provided stop script - stop-pentaho.sh returns immediately after being invoked and does not guarantee that the application has been stopped.

Compiling jsvc (available in tomcat's bin directory as jsvc.tar.gz) enabled me to have a guaranteed way of stopping tomcat. The following script shows the contents of a script I named stop-tc.sh. This script accepts the tomcat HTTP listen port as the sole argument.

#stop tomcat whose port provided at command line
tmp_file=/tmp/tc-stop.pid

#use lsof to get PID, write to temp file and call jsvc
lsof -a -i TCP:$1 -c java | grep LISTEN | awk '{print $2}' > $tmp_file && /usr/local/bin/jsvc -stop -pidfile $tmp_file org.apache.catalina.startup.Bootstrap

I thereafter turned my attention to the hypersonic database that holds pentaho admin information. There should be a command to shutdown the database, but I decided to kill the process and delete the lock files. The complete restart script, which references the one above, is given below.

#! /bin/bash

export JAVA_HOME=/usr/lib/jvm/java-6-sun
#stop tomcat
/storage/kuali-scripts/stop-tc.sh 8286
#ensure tomcat quits - the file involved is created by stop-tc.sh above
kill -9 `cat /tmp/tc-stop.pid`

#kill hypersonic
kill -9 `lsof -a -i TCP:9001 -c java | grep LISTEN | awk '{print $2}'`

#delete hypersonic log files
rm /storage/financials/pentaho2/biserver-ce/data/hsqldb/quartz.lck
rm /storage/financials/pentaho2/biserver-ce/data/hsqldb/hibernate.lck
rm /storage/financials/pentaho2/biserver-ce/data/hsqldb/sampledata.lck

#start pentaho
/storage/financials/pentaho2/biserver-ce/start-pentaho.sh &> /dev/null

Copying a Pentaho Report Repository Manually

Copying a existing report repository is an easy way to create another repository for use with a test or development database. It can also be a way to create a production repository from an erstwhile development version.

These are the steps I used on Linux:

1. Copy the repository
   

   
   cp -r repo-prod repo-dev
   

   
   
2. Edit the index.properties file in repo-dev and change the name property to give it a different name than the original.


3. Change the data source name to match the development one in each xaction file
   

   find . -name '*.xaction' | xargs sed -i 's/data-prod/data-dev/g'



4. Change the repository name in xaction files that have sub-actions
   

   find . -name '*.xaction' | xargs sed -i 's/repo-prod/repo-dev/g'



5. Change the image paths in the report design .xml files. cd into the directory first
   

   find . -name '*.xml' |xargs sed -i 's|/old/prefix/|/new/prefix/|'

   
   
   

Invalid column type in Pentaho

A java.sql.SQLException: Invalid column type error in pentaho had broken a previously functioning report upon the introduction of changes.

On close inspection, I traced the problem to a newly introduced a parameter in an SQL statement. The parameter was supplying data for use in a SQL IN(...) clause. When a variable of type string-list was supplied, the report worked. The exception above was thrown when a variable of type string was supplied.

To resolve the problem, I created a Java ArrayList to hold the single value and hence ensure that the variable supplied was always a list.