Running Pentaho CE 3.5 With Access to MS SQL 2005

For those that do not know, Pentaho is a commercial open-source Business Intelligence (here-after referred to as BI) software solution.  It produces a Community Edition (CE) which is available free of charge, and an Enterprise Edition (EE) which is around $30,000 for the whole enchilada.  EE has some additional features that the CE does not, such as Single Sign On (SSO), some additional Admin Console features, and other whizbang goodies.  Also thoroughly QA's code.  However, the vast majority of the functionality is the same in both editions.

My goal here is to get the latest 3.5 CE release talking to my Microsoft Dynamics GP 10 Fabrikam company database, running on MS SQL 2005.  Why?  Because I spend most of my time working with GP (and CRM).

First, I downloaded Pentaho CE 3.5 and started reading the Getting Started Guide. I first created a directory to hold my extracted files, and made the two directories recommended under the main - server and design tools.  My path is now:

c:\pentaho\server

c:\pentaho\design tools

I extracted the biserver-ce-3.5.0.stable.zip file to the server directory.  That made two new directories - biserver-ce and administration console.  I then was able to get the Pentaho User Console up by simply running start-pentaho.bat from within the biserver-ce directory. That worked like a charm. All the sample information is there.

Now I want to make a new report using my GP data.  According the the Guide, a new data source can be created using the Pentaho User Console when you click Add in the Select Data Source step when creating a new Ad Hoc Report.  Let's try it.

After clicking on the New Report icon, and then clicking on the Add button to add a new datasource, the New Data Source "window" pops up and already has an entry for the SampleData.

I hit the little green + icon to add a new connection.  When I select the MS SQL Server option and enter my criteria and test, it fails.

Why does it fail? Well, it turns out it is looking for the JTDS JDBC driver, which is not in my classpath, apparently.

So, next step is to download the JTDS driver, which works with SQL Server and Sybase.  Download here, and then extract and copy the jtds-1.2.4.jar file to the biserver-ce/tomcat/common/lib directory.  That was my guess as a path that would work.  And it did!  (One other note - TCP/IP must be enabled for the SQL Server Network Protocol, or you need to pass the JTDS driver a parameter telling it to use Named Pipes.  Details here.)

After stopping Tomcat (Ctrl-C in the windows or stop-pentaho.bat) and restarting Tomcat/Pentaho, I go back to add a new report and new datasource.  This time it works!

So now I can continue and attempt to make a new Ad Hoc Report using this new connection.  I am going to do a simple query to get the Customer List by Customer Class.

select custnmbr, custname, custclas from rm00101

 After entering this query into the New Data Source SQL Query field, I hit Preview to verify it works, and it does.  Then I hit Apply and am presented with a Metadata specification:

I know that all three fields are strings, so I mark them as such.  Now I move along to the next step.

Next is to identify the fields, groupings, and filters for the report.

Next.  Everything looks good.

Next.  I specify a Report Header.

Now I click on the little blue floppy disk icon to Save my report.  I saved it in the BI-Developer/Reporting folder, as TWO Customer by Class.  Now in the User Console, I can open that location in the tree and see my new report.

But when I double-click the report to view it, I get this error message:

Since I do not know what that means, I'm kinda stuck, and will attempt to watch some demos and see what I can find on the net.