August 18, 2009

Third Party Reporting with PostgreSQL

Surround SCM
Helix ALM
One of the biggest changes in the 2009 release of Surround SCM was the move to an RDBMS system for its database. There are many benefits to using industry-standard RDBMS systems, but for this post we are going to focus on the ability to generate third party or custom reports. Surround SCM ships with PostgreSQL, which can present a challenge when it comes to generating custom reports. The main hurdle has been finding a low cost or free reporting tool. You are getting PostgreSQL with Surround SCM, and who has budget to purchase another tool? If you are not in a "Windows shop", you will probably stay on PostgreSQL and may not have a reporting tool that works with PostgreSQL. So what to do? In my quest to find a solution for those that leverage PostgreSQL, I came across Pentaho (http://www.pentaho.com). Pentaho offers a business intelligence reporting engine as well as a report designer among other offerings. But what attracted me to them as a solution is:
  • Community Edition: You can download the source code, release builds, or nightly builds. I downloaded the release builds.
  • Works well with PostgreSQL: I had to download the JDBC from the PostgreSQL web site, but once you place the JAR file in the proper folders, it appears as an option when configuring data sources. Documentation and examples for PostgreSQL are available.
  • Easy to use report designer: I was able to get up and running relatively quickly, and I am by no means a report expert.
  • Ability to post reports to a web service (intranet or internet): Once you create the reports, you can share them with other users.
During my evaluation of Pentaho I mainly tested the Business Intelligence server and the report designer. Please note that Pentaho does have a commercial line of products, which include more features, support, and other offerings. Please visit their site for more information. If you find the community edition to have limitations, you may want to explore the commercial edition. Report Designer The Report Designer is a Java application that allows you to design reports. If you have used other reporting tools you will be able to quickly get started as it is an intuitive tool. Here is a screenshot of the report designer: [caption id="attachment_869" align="aligncenter" width="612" caption="Pentaho Report Desginer Interface"]rptdespent[/caption]

Following is a simple report I created with the report designer: [caption id="attachment_851" align="aligncenter" width="601" caption="Sample Report"]pentahoreport[/caption] Here is the query I ran for those of you that would like to try this out:
SELECT COUNT(br.FileName) as FileCount, date_part('day',evt.EventDate) as Date
FROM BRANCHFILE br, EVENT evt
WHERE br.BranchFileID = evt.BranchFileID and evt.EventType = 4
GROUP BY Date ORDER BY Date
Note: The query does not really filter out check ins by June, I simply used a database that only contained check ins for that month. You will need to add another WHERE clause to also filter by the month. One thing I have not been able to figure out is why the days are being displayed as decimals. If you have used Pentaho and know why, please leave a comment, as well as any other experiences or hints you may have. Once you have the report the way you want it, you can then publish it to the Business Intelligence Server. Business Intelligence Server This runs on top of Tomcat, which comes bundled with it. The only issue I found is that the server where I installed this had Apache running, and they were fighting for the same port.  I had to modify the Tomcat configuration to change the port and also to load the PostgreSQL JDBC Jar file.  The documentation helped with these issues and I was able to get the server up and running in no time. Once I published my report to the server, the above chart was available via web access and I could share it with my colleagues. In conclusion Finding a robust reporting tool that is open source, cross-platform, and compatible with open source RDBMS systems can be challenging, but I think I have found a viable solution.  Pentaho supports PostgreSQL (and others) and runs on any platform that supports Java. Also, remember that the TestTrack line of products (TestTrack RM, TestTrack Pro, and TestTrack TCM) supports PostgreSQL, so Pentaho could be the solution for all of your custom reporting needs.