Troubleshooting Pentaho

The following content was extracted from the Support Manual compiled by Sungard Technology Services in December 2010:

How to install Pentaho and Pentaho Server?

(NOTE: for full detailed instructions on installing the Mifos Business Intelligence Suite containing Pentaho and the Data Warehouse at Installing Mifos BI (Pentaho))

There is no need to install Pentaho the designer and server. Just unzip the pentaho installer files and copy it in your root directory. On clicking the executable files both server and designer starts.

“biserver-ce” is the name of BI- server  folder.

“report-designer” is the name of designer folder.

“administration-console” is the name of administrator folder for users and roles.

How to set up Pentaho to run on different port (if Mifos is running on 8080)?

Mifos will run on your default Tomcat on port 8080. To run Mifos and Mifos BI simultaneously, you will need to change which port Pentaho runs on. To do so you must change the port to 8086 via the web.xml and server.xml files. These tips were compiled by Ramya in this mailing list discussion.

These steps assume the Mifos war is installed in your default tomcat which runs on port 8080 - http://localhost:8080/mifos

http://localhost:8080/mifos1) Navigate to following location:

Edit the base-url section of your web.xml file. Change the port to 8086 from 8080

<context-param>
                                <param-name>base-url</param-name>

                                <param-value>http://localhost:8086/pentaho/</param-value>
</context-param>

2) Navigate to:

Edit the following two sections:

i) Change the shutdown port no to 8007 from 8005

<Server port="8007" shutdown="SHUTDOWN">

ii) Change the port from 8080 to 8086

<!-- Define a non-SSL HTTP/1.1 Connector on port 8080 -->

<Connector URIEncoding="UTF-8" port="8086" maxHttpHeaderSize="8192"

               maxThreads="150" minSpareThreads="25" maxSpareThreads="75"

              enableLookups="false" redirectPort="8443" acceptCount="100"

               connectionTimeout="20000" disableUploadTimeout="true" />

With these three changes, both applications should run simultaneously with Mifos BI at http://localhost:8086/pentaho

How to Start Pentaho Designer, Admin console and BI-server?

BI_SERVER

Click the following

  Default URL to access is:   http://<ip address of the server>:8089/pentaho

  Default username/pwd is : joe/password

Admin console URL

For Admin console, you need to start the following batch file at specified location

http://<ip address of server>:8099

The default username/pwd is admin/password

Pentaho Designer

How to do user management in Pentaho?

From admin console of Pentaho, you can create the users, roles and database connections for the server.

• Open Admin console with admin user. Go to Adminitration tab.

• Creating JNDI and JDBC connections on Pentaho server.

• Setting maximum and minimum active connections on server. Go to Advanced tab for it.

• Creation of roles

• Creation of users

How to deploy the design file on Pentaho server from designer?

a)     Open the designer by clicking the “startdesigner.bat” file.

b)    Open the design file which you want to deploy and then click on publish button

c)     The following screen will open .Enter the credentials and select the location on server to deploy the file.

Note: at the time of deployment, your server must be already started.

How to schedule your reports?

In the admin console, there is an option of scheduling the files or jobs also. You can use it and set the time of generation of the file automatically.

Go to scheduler tab

Click on create schedule and fill accordingly.

How to remove connection pool exhausted error?

Various ways to solve this problem

a)    Increase the max_connections value in my.ini file of MYSQL folder.

b)    Increase the active connections on pentaho admin console as discussed.

c)    Increase the active connections value in:

<?xml version="1.0" encoding="UTF-8"?>

<Context path="/pentaho" docbase="webapps/pentaho/">

            <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"

                        factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="500" maxIdle="5"

                        maxWait="10000" username="hibuser" password="password"

                        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"

                        validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />



            <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"

                        factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="500" maxIdle="5"

                        maxWait="10000" username="pentaho_user" password="password"

                        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"

                        validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>

</Context>

d)    Go to

Increase the max active connection value.

INSERT       INTO     DATASOURCE VALUES('SampleData',500,'org.hsqldb.jdbcDriver',5,'pentaho_user','cGFzc3dvcmQ=','jdbc:hsqldb:hsql://localhost:9001/sampledata','select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES',1000);

e)     If nothing works then the final solution is to add all your subreport queries in the main report connection only, and inherit them in your sub-reports correspondingly.

How to do JNDI settings in Pentaho?

• In designer to use JNDI connections. Go to your

Add entries like below for each JNDI connection.

mifos_kmbi/type=javax.sql.DataSource

mifos_kmbi/driver=com.mysql.jdbc.Driver

mifos_kmbi/user=root

mifos_kmbi/password=mysql

mifos_kmbi/url=jdbc:mysql://localhost:3309/kmbi_mifos_testing_2?holdResultsOpenOverStatementClose=true

mifos_kmbi as shown above, will be the name of your JNDI connection which you will simply mention in your design file.

In designer,

• For JNDI settings on server, I mentioned earlier already.

Log on to

• Go to advanced tab

How to turn off auto-submit button in Pentaho?

By default Pentaho turns on the auto submit button as soon as you select the mandatory parameters of reports while it’s execution. This helps in simultaneously executing the validations of report parameters (especially when are cascading parameters).

There are two ways of turning off it

a)     In the designer, you can set the property “auto-submit” of your report as OFF( there is a minor bug in it. To be on safer side, first check the property and then uncheck to make sure it is OFF)

b)    The other way is to off it at server. Open file

      In this add the following line

Existing command:

<command>content/reporting/reportviewer/report.html?solution={solution}&amp;path={path}&amp;name={name}&amp;layout=flow</command> 

Change it to:

<command>content/reporting/reportviewer/report.html?solution={solution}&amp;path={path}&amp;name={name}&amp;layout=flow;&amp;autoSubmit=false</command>

Tip: Whenever you have cascading parameters in your report, do not turn off the auto submit button

The processing of parameters will not happen then. In such cases try to use list type as your parameters instead of drop downs.

How to call stored procedure/kettle files in Pentaho designer?

There are two ways of running the stored procedures

If the procedure returns a resultset, then you can use the result like any other query in the report-designer by calling it via

"CALL  procedure(${parameter1},${parameter2})"

However, if your procedure just returns a single value and you want to combine that value with results from other procedures or queries, then you have to use Pentaho Data Integration.

In second case, create the kettle file and in your report, select the source as Pentaho data Integration. Then select the kettle file and upload it.

Tip: Remember, if your connections are JNDI type in your kettle file, you have to make the same connections in Reports JNDI file also.

Browser recommendations for Pentaho

Generally Pentaho recommends Firefox to be used. So in case if the page gets blank after clicking view report button, first of all try the following stuff on Firefox.

Headersize:

There is a limit of every browser. IF the data of a MFI is huge, and you are selecting the entire organization parameter values then in that case http headersize for that browser may become small too fit the requirement.

Default headersize for Firefox is 64000

Heap space error:

Whenever you get this error, increase the JAVA_OPTS environment variable value in the server and at below two locations.

Open the batch file in EditPlus and increase the memory value.

@echo off

setlocal

set PENTAHO_JAVA=javaw

call "%~dp0set-pentaho-java.bat"


start "Pentaho Report Designer" "%_PENTAHO_JAVA%" -XX:MaxPermSize=256m -Xmx512M -jar "%~dp0launcher.jar" %*

Similarly on server: