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 am going to assume you’ve at least got Pentaho installed and started, if you haven’t pop over the site and pick up a copy of the installer to get you started. The first thing to make everyone aware of because it will be your life saver, is Mondrian the MDX server. Now I have to admit that when I first installed Pentaho I knew absolutely nothing about MDX, Mondrian or anything of that nature, hell I hadn’t used SQL since I was at uni a few years ago. So my boss gave me the task of bringing the 100’s of reports we create every month together into a more centralised and automated fashion, so I started looking at ways of doing this. I started by scripting a website, creating the tables and code straight in PHP, HTML and an excel generator for some proper output, I got the first few reports done but there had to be a better solution.. and on a budget. I played around with a few CMS systems to see if they fit the bill but they still involved a lot of coding, the eventual output looked better but creating the reports was going to be a chore. Then I stumbled across Pentaho, it look just like what I wanted, an open source(I’ve been using Linux for years), web based, business intelligence suite. So I got it installed and looked at the sample data, bingo I thought, but how does this all work then? I uploaded some of my data to the database and set to work trying to figure out how it all worked. I played for days trying to generate useful data just with SQL and various report types, but never getting close to what I wanted, I had picked one of the harder reports to start with but what the hell. Then I thought what about this MDX stuff I’ve been seeing whist playing around and my quest for the best reporting system began.

MDX history

MDX stands for Multidimensional Expressions it is a query language for Online Analytical Processes(OLAP) Databases and cleverly it allows relatively easy calculations using formula’s similar to spreadsheets. To my surprise it was actually invented by a guy at Microsoft, they’ve come up with a useful specification and actually seemed to keep it pretty quiet. So unsurprisingly it runs on MSSQL, MYSQL, Oracle, DB2, the usual culprits.

How it works

Well MDX with Pentaho works by creating a Cube which references tables in a main database. In this Cube you will define ‘Dimensions’, ‘Hierarchies’, ‘Levels’ and ‘Measures’ these create the levels of information that the MDX server uses to reference. So for example you may have:

<Dimension name="TIME" foreignKey="DATE_ID">
<Hierarchy name="TIME" hasAll="true" allMemberName="All time" primaryKey="DATE_ID">
<Table name="TIME"/> <Level name="YEAR" table="TIME" column="YEAR_ID" uniqueMembers="false"/>
<Level name="QUARTER" table="TIME" column="QUARTER_ID" uniqueMembers="false"/>
<Level name="MONTH" table="TIME" column="MONTH_ID" uniqueMembers="false"/>
</Hierarchy>
</Dimension>

So as you can see first we define the dimension which in this case is called ‘Time’ which references the foreign key ‘Date_ID’.

Next we define a hierarchy which in this case is also called ‘Time’.

Then we define the levels which are reasonably self expanatory these decend in diminsihing size as it quite obvious with this date example.

<Measure name="TOTAL_SALES" column="TURNOVER_EUR" aggregator="sum" datatype="Numeric" formatString="# ##0,###"/>

Lastly we can create Measures which do pretty much do as the name suggests and we use them as the core data in our tables. The name is the name you want to see in the cube, the column if the column name from the database, there are several different aggregators your can set as with datatypes and formatStrings.

But don’t worry this doesn’t all have to be coded by hand Pentaho have developed a Cube Designer which you can download and use to create and upload the cubes to your server. This is much easier than creating by hand, the only problem being at the moment your can’t reopen a saved cube for editing, so once you’ve published your cube check it to make sure its as you expect before you close the designer, because you can always make adjustments and re-upload. Once the cube is published you can always edit the xml file directly, it will be called cubename.mondrian.xml. Take a look and check out its structure.

Anyway, I’ll be back later with the basics of starting query writing.

I dreamt up the idea of this blog whilst sitting around at work this week, I’ve been hammering away at Pentaho for weeks now and not really documented a single thing I’ve done, sure I can look at my code but I have nothing documented. So I recon I’ll start from the ground up. Yes I realise there wont be anything ground breaking in this blog as of yet, but I hope my notes and code will help people who stumble across here with the more obvious problems that I’ve encountered over the last few weeks whilst setting up a Pentaho Server at work.

Also a big thanks to the folks at the forums(especially Taqua) for answering my rather stupid and plain obvious questions, and for bug fixing.