Pentaho 3.6 with Microsoft SQL Server

Posted under BI Server, Pentaho, Tutorials

Yep, you read right, I have now updated the guide so that it now includes Microsoft SQL Server 2005+. Big thanks goes out to Roland Bouman who sent me the necessary configurations files and drivers needed for this to work, so head over to his blog and give him a hug or just thank him.

You can grab a copy of the guide in Google Docs format and the SQL Script Pack at the projects page.







As always please let me know if you find any errors and also if you would like to see any further additions to guides and support for other databases.

Prashant.

Posted by Prashant Raju on Aug 30, 2010 — 13 CommentsShare

13 Comments for “Pentaho 3.6 with Microsoft SQL Server”

  1. Posted by Roland Bouman on Aug 31, 2010

    Hi Schone!

    I really want to thank you in the first place – I used your excellent guides for MySQL, Oracle and Postgres to get it up and running on MS SQL and my efforts were very minor compared to yours. So credit where credit’s due – you deserve it :)

    kind regards,

    Roland.

  2. Posted by nerome on Sep 1, 2010

    Hi all

    Pentaho_3.6.0_MSSQL_SQL_Pack.zip is corrupted!
    what method is used gueglfue?

    All other archives are OK. I tested.

    Please correct it, because that scripts are
    important to setup MS SQL Server correctly.

    thanks a lot.

  3. Posted by Prashant Raju on Sep 1, 2010

    nerome,

    I just tested this again and it works fine on my end.

    Can you give me the exact error you are getting?

    Prashant

  4. Posted by nerome on Sep 1, 2010

    Hi Prashant

    I tried extract with archivers
    – Powerarchiver 2006 produces no errors but an emtpy folder
    – Wincommander 5.1 says unsupported method
    – iArchiver 1.7 produces no errors but an emtpy folder
    – tar,unzip,uncompress on OSX 10.5 procuces unsupported method 89
    I see the files but they are not extractable and the folder is empty.

    Thank you for any help. It is possible to publish them as plain textfiles? the biggest is

    Kind regards

  5. Posted by Prashant Raju on Sep 1, 2010

    nerome

    Sorry about that – can you try now?

    Prashant

  6. Posted by nerome on Sep 2, 2010

    Prashant

    never mind, Pentaho_3.6.0_MSSQL_SQL_Pack.zip is now ok. can be properly extracted and files are readable.

    thank you very much.

    nerome

  7. Posted by Federico on Sep 21, 2010

    Hello!. I have a problem with the instalation of Pentaho bi-server.
    I folow point by point the steps in the guide (very good guide and complete; thanks) version 1.3.2 for PostgreSQL and Linux.

    The details of the problem.

    In first instance, run correctly and show de loggin screen.
    When I login like joe and select Steel Wheels for see a report apear:
    An error ocurred while rendering Pivot.jsp. Please see the log for details.

    In the logs /opt/pentaho/bi-server/tomcat/bin/pentaho.log
    2010-09-09 09:40:56,321 WARN [org.pentaho.platform.util.logging.Logger] misc-org.pentaho.platform.engine.core.system.PentahoSys tem: PentahoSystem.WARN_OBJECT_NOT_CONFIGURED – Object with name “Mondrian-UserRoleMapper” has not been configured in the Pentaho object factory.
    2010-09-09 09:40:56,387 WARN [org.pentaho.platform.engine.services.runtime.Templ ateUtil] not found: [Measures].[Quantity]

    The details of my instalation

    Instalador:
    biserver-ce-3.6.0-stable.tar.gz

    uname -a
    Linux cemed26.unl.edu.ar 2.6.26-2-686 #1 SMP Mon Aug 30 07:01:57 UTC 2010 i686 GNU/Linu
    Debian Lenny

    java -version
    java version “1.6.0_20″
    Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
    Java HotSpot(TM) Client VM (build 16.3-b01, mixed mode, sharing)

    PostgreSQL 8.3

    Iceweasel 3.0.6

    Apache-Tomcat embebed in the Pentaho.

    echo $JAVA_HOME
    /usr/lib/jvm/java-6-sun
    echo $JRE_HOME
    /usr/lib/jvm/java-6-sun/jre
    echo $CATALINA_OPTS
    -Xms256m -Xmx768m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000
    echo $CATALINA_HOME
    /opt/pentaho/biserver-ce/tomcat

    Thanks for all.

  8. Posted by Kristi on Nov 9, 2010

    I realize that you don’t support his product but I have followed your guide (a very good easy to follow guide, btw)and still can’t get the Pentaho BI community edition displaying reports with SQL Server 2005 as the backend. I have tried to post to the Pentaho Community Forum but can’t post even though I’ve signed in as a user. I signed up last week and am still not able to post.
    I’ve followed your guide by the letter and double checked everything several times. I am still having issues. I am able to login with the default user “Joe”. Then when I tried to open a report, I get “failed at query”. I look at my Tomcat logs and the first line after I start Tomcat says “14:04:44,114 ERROR [Logger] misc-org.pentaho.platform.engine.services.connection.datasource.dbcp.PooledDatasourceSystemListener: PooledDatasourceSystemListener.ERROR_0003 – Unable to pool datasource object: SampleData caused by com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.
    14:04:45,348 WARN [AxisService] Unable to generate EPR for the transport : http”

    I do not have SQL Server running on the local machine where Pentaho is installed. This error makes me think that something is trying to connect back to port 1433 on the localhost. A few lines later, I get this:

    14:05:02,614 ERROR [SQLLookupRule] Error Start: Pentaho Pentaho Platform Engine Core 3.6.0-stable.41852
    14:05:02,614 ERROR [SQLLookupRule] 0e5d2f56-eb6b-11df-81ed-536a0c0605b7:COMPONENT:context-17961068-1289243087989:global-department-list.xactionConnectFactory.ERROR_0001 – Database connection could not be established to: SampleData
    org.pentaho.platform.api.data.DatasourceServiceException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.
    at org.pentaho.platform.engine.services.connection.datasource.dbcp.PooledDatasourceHelper.setupPooledDataSource(PooledDatasourceHelper.java:147)

    If I have missed something simple or obvious and these logs show that, will you please clue me in? This product is new to me and I am totally lost as to how to troubleshoot it and I’ve searched teh Pentaho Community Forum ad nausium and I am getting nowhere.

    Thanks, Kristi

  9. Posted by Kristi on Nov 9, 2010

    I fixed this error. In the table: Datasource in the Pentaho database, the fields DriverClass and URL need to be changed to fit your own scenerio. I had just run the scripts and not changed these values to fit my setup. Thanks, Kristi

  10. Posted by Kristi on Nov 10, 2010

    I am sure most people who work with this product have already figured all of this out but I am a novice so I wanted to share a few more things for any other novices out there. After I changed the entries for the Datasource table, I was getting a login failed. I noticed that the password listed was Base64 encodeded, I decoded it using: http://www.motobit.com/util/base64-decoder-encoder.asp
    This showed me the password it was using was the one from the scripts and I has changed that when I ran them. So I took my password and coded it to Base64 using the same site then pasted it into the field. Still got the login error. I then went to the SQL Server user and unchecked: Enforce password policy then I retyped my password. Now I am able to login and see the reports. I don’t know if this will help anyone else who is trying to use SQL Server as the backend and trying to get the sampledata setup but I hope it might.

  11. Posted by Tim on Dec 8, 2010

    Prashant,

    I used your guide to get 3.6 GA working on linux with postgres. I have followed to same guide for 3.7 and am not having any success. Have you had an opportunity to look at 3.7 yet?

  12. Posted by Leon on May 10, 2011

    Pentaho_3.6.0_Oracle_SQL_Pack.zip
    Unable to download this file.
    MySQL version downloads ok!

    Please check the upload

    Kind regards,

    Leon

  13. Posted by Ronak Minda on Sep 16, 2011

    Hi Prashant,

    I am using pentaho 3.6 version ,Using C3P0 Connection pooling .Most of the Time I am facing
    5:22:15,050 ERROR [JDBCTransaction] JDBC commit failed
    java.sql.SQLException: Connection handle has been closed and is unusable
    at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:537)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:524)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConnection.java:333)
    at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:115)
    at org.pentaho.repository.HibernateUtil.commitTransaction(HibernateUtil.java:377).

    Could you please help me on this .
    Thanks


Post a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>