WAMOnline Blog

Have you been having issues when you pentaho server is no longer chatting to MySQL when you arrive at work the following morning. I had an annoying issue where my server no longer sent attachments after it had been up for 8 hours because MySQL was closing the idle connections and hibernate wasn’t reopening them when it ran the scheduled task. After days of debugging the solution is thus….

Get a copy of dbcpconnectionprovider.class and copy it into WEB-INF/classes/org/hibernate/connection/ (create the directories if they don’t exist)

Then add the following to hibernate.cfg.xml….

<property name=”hibernate.connection.provider_class”>

org.hibernate.connection.DBCPConnectionProvider

</property>
<property name=”hibernate.dbcp.initialSize”>8</property>
<property name=”hibernate.dbcp.validationQuery”>SELECT 1</property>
<property name=”hibernate.dbcp.testOnBorrow”>true</property>
<property name=”hibernate.dbcp.testOnReturn”>false</property>
<property name=”hibernate.dbcp.testWhileIdle”>true</property>
<property name=”hibernate.dbcp.minIdle”>4</property>
<property name=”hibernate.dbcp.timeBetweenEvictionRunsMillis”>120000</property>
<property name=”hibernate.dbcp.numTestsPerEvictionRun”>3</property>
<property name=”hibernate.dbcp.minEvictableIdleTimeMillis”>120000</property>

And it should keep your connections pooled so you no longer suffer from this issue.

I was playing around trying to troubleshoot a report I was having problems with, when I struck upon another issue that I didn’t know how to solve, updating the JFreeReport Jar’s without reinstalling the whole lot. This is how its done….

Download the latest archive

Extract the pentaho-reporting/libs/ directory and dump its contents in

jboss/server/default/deploy/pentaho.war/WEB-INF/lib/

When you restart the system, if there is a load of error messages, remove the gnujaxp.jar file as it caused me a right load of problems.

This may sound rather obvious but it took some working out on my part. I wanted a report to be emailed out but the query was MDX, and the report designer doesn’t directly support MDX generation, ie: unlike sql you can’t see what your doing.

So publish an analysis cube with the JPivot stuff, load it up and work out how you want your table displayed and setup. It may be an idea to clean up the code a bit but once that’s done copy it out, and paste it into the report wizard/designer and it should all work, just make sure you use the same cube

Another rather obvious tip but certainly useful.

If you want to load the selections for a secure filter/prompt in a xaction dynamically, then in the editor select a Get Data -> relational

Input the query to retrieve the relevant information and then setup the result-set at the bottom of the page and assign the columns.

The in the Secure Filter/Prompt do the usual just selecting the dynamic result-set. As long as the details are correct you will see the query results next time you run the xaction.

Here’s a quick tip, if you generate calculated members in your code but can’t work out how to format them this is for you..

For some reason Members and Calculated Members have different syntax to format calculated members add them in a similar way to the code here.

<CalculatedMember name="Rolling Budget Margin" dimension="Measures">
<formula>
((([Measures].[Current DAY_ID] / [Measures].[Month DAY_ID])) * [Measures].[Budget Margin])
</formula>
<CalculatedMemberProperty name=”FORMAT_STRING” value=”#,#00;;\Z\e\r\o”/>
</CalculatedMember>