Configure the Pentaho Server

Basic server tasks include starting and stopping the Pentaho Server, tuning memory, and setting up connections.

These IT administrator tasks prepare the system for more specific Pentaho administrator configuration tasks, like defining connections and managing users and roles.

In this topic

• Stop and start the Pentaho Server and repository

• Create scripts for automatic stop and start of the Pentaho server and repository on Linux

• Increase Pentaho Server memory limit for custom installations on Windows or Linux

• Specify data connections for BA design tools

• Specify data connections for the Pentaho Server

Stop and start the Pentaho Server and repository

To perform maintenance work on the Pentaho Server or components that use the Pentaho Server, such as the Pentaho User Console and Pentaho Data Integration, stop the repository and server, perform the work, and then restart the repository and server.

Start the Pentaho Repository before you start the Pentaho Server.

You must be an IT administrator and know where the data that you are managing is stored, how to access that stored data, details about the computing environment, and how to use the command line for Windows or Linux.

1. Run the following control scripts to stop and start the Pentaho Server:

   Start on Windows	...\pentaho\server\pentaho-server\start-pentaho.bat
   Stop on Windows	...\pentaho\server\pentaho-server\stop-pentaho.bat
   Start on Linux	.../pentaho/server/pentaho-server/start-pentaho.sh
   Stop on Linux	.../pentaho/server/pentaho-server/stop-pentaho.sh

If you installed the Pentaho Repository as PostgreSQL, MySQL, MS SQL Server, or Oracle, consult that database documentation for start and stop instructions.

Script arguments and services (custom start/stop scripts)

Use these standard arguments:

• start

• stop

• restart

• status

• help

Common service names:

• pentahoserver

• postgresql

Create scripts for automatic stop and start of the Pentaho server and repository on Linux

If you used the manual installation to install Pentaho on Linux, create scripts for automatic start on boot and stop at shutdown for the Pentaho Server and Pentaho Repository.

You must have root permissions.

1. Navigate to /etc/init.d/ and create a file named pentaho.

2. Open the pentaho file and enter the following content:

   #!/bin/sh
   ### BEGIN INIT INFO
   # Provides: start-pentaho stop-pentaho
   # Required-Start: networking postgresql
   # Required-Stop: postgresql
   # Default-Start: 2 3 4 5
   # Default-Stop: 0 1 6
   # Description: Pentaho Server
   ### END INIT INFO
   
   case "$1" in
     "start")
       su - pentaho -c "/home/pentaho/pentaho/server/pentaho-server/start-pentaho.sh"
       ;;
     "stop")
       su - pentaho -c "/home/pentaho/pentaho/server/pentaho-server/stop-pentaho.sh"
       ;;
     *)
       echo "Usage: $0 { start | stop }"
       ;;
   esac
   exit 0

3. Optional: Update the script for your environment.

   • If you are not using Red Hat Enterprise Linux, modify the details of the script to work with the OS, shells, and init systems that you are using. The script was tested only on Red Hat Enterprise Linux.

   • If you use an account other than the pentaho local user account to start services, replace pentaho with your account name.

   • If you use a MySQL or Oracle repository instead of PostgreSQL, replace postgresql with mysql or oracle.

   • If the solution repository runs on the same machine as the server, change postgresql to the name of the init script for your database.

   • If the solution repository runs on a remote computer, remove postgresql entirely and adjust the paths to the Pentaho Server scripts.

4. Save and close the pentaho file.

5. Navigate to /home/pentaho/pentaho/server/pentaho-server and open start-pentaho.sh.

6. In start-pentaho.sh, change the last if statement to match the following example:

   if [ "$?" = 0 ]; then
     cd "$DIR/tomcat/bin"
     export CATALINA_OPTS="-Xms4096m -Xmx6144m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000"
     env JAVA_HOME=$_PENTAHO_JAVA_HOME sh ./startup.sh
   fi

7. Save and close start-pentaho.sh.

8. Make the init script executable:

   chmod +x /etc/init.d/pentaho

9. Add the init script to the standard run levels.

   If your distribution supports update-rc.d, run:

   update-rc.d pentaho defaults

Many modern Linux distributions use systemd instead of SysV init scripts. If update-rc.d is not available, consult your distribution documentation for the equivalent.

Increase Pentaho Server memory limit for custom installations on Windows or Linux

If you used a custom method to install PDI on Windows or Linux, consider increasing the PDI memory limit so that the Pentaho Server and the PDI client (also known as Spoon) can perform memory-intensive tasks, like sorting, grouping large datasets, or running complex transformations and jobs.

Increase the memory limit for both the Pentaho Server and the PDI client. If you do not increase the memory limit, PDI uses the default memory settings in the PDI startup scripts.

For instructions on increasing memory limits by editing the Tomcat startup script, see Configure and start the Pentaho Server after manual installation.

For instructions on increasing the memory limit in the PDI client, see Modify the PDI client startup script.

Specify data connections for BA design tools

The BA design tools need a way to connect to where you store data, and to the Pentaho Server where you publish and share what you create in the design tools.

We support connecting to data stored in these ways:

• Pentaho data sources, such as relational Metadata data models or multidimensional Mondrian data models.

• JDBC databases.

JDBC database connections

Your database access protocol affects how you create, test, and publish reports and models. Choose one of these protocols:

• Native (JDBC): Standard JDBC connection details stored in each report or model.

• JNDI: A named connection. Properties are stored outside the report or model.

• ODBC: Not supported. Available only for compatibility.

• OCI: Oracle-only. Use when you must use Oracle Call Interface.

Choose an access protocol

Native (JDBC)

Use Native (JDBC) when you need a quick setup or you are evaluating.

Keep this limitation in mind:

• If connection details change, you must update every report and model that embeds them.

Behavior by tool:

• Report Designer and Metadata Editor

  • Connection details are embedded in each report or Metadata model.

  • Report Designer lists the saved connection name after you define it.

  • Metadata Editor requires you to re-enter the connection name when you use it.

  • Before publishing, ensure the connection name matches a Native (JDBC) connection already defined on the Pentaho Server.

• Schema Workbench and Aggregation Designer

  • Connection details are not stored. Define the connection each time.

  • Before publishing, you need the Pentaho Server URL and the name of a Native (JDBC) connection already defined on that server.

Recommendation:

• Use for trials and evaluation.

• For production, switch to JNDI before publishing.

JNDI

Use JNDI when you need centralized maintenance and a distributed team.

Key characteristics:

• Reports and models store only the JNDI name.

• You update connection details in one place.

Behavior by tool:

• Report Designer and Metadata Editor

  • Connection properties live in a file on each workstation.

  • Before publishing, ensure the JNDI name matches a JNDI connection already defined on the Pentaho Server.

• Schema Workbench and Aggregation Designer

  • JNDI is not available for connecting from the tool.

  • Before publishing, you still need the Pentaho Server URL and a JNDI connection name already defined on the Pentaho Server.

Recommendation:

• Use for production deployments.

ODBC (not supported)

ODBC is not supported. It is available only if you have a hard dependency.

If you must use ODBC, contact Pentaho Support.

OCI (Oracle only)

If you connect to an Oracle database, you can use OCI. Use the steps in Define JDBC or OCI connections for BA design tools.

Define connections for BA design tools

Use these sections to set up connections on workstations running BA design tools.

Define JDBC or OCI connections for BA design tools

1. Choose an access protocol in Choose an access protocol.

2. Install the matching driver.

3. Set the access protocol later in each design tool when you define the data source.

Add drivers

The driver lets the design tools connect to the Pentaho Server. This connection helps you validate the model.

Your database administrator or IT team can provide the driver. If needed, download it from your database vendor.

Copy the driver to these directories on every machine that runs the design tools. Stop the design tools before you copy the file.

• Aggregation Designer: /pentaho/design-tools/agg-designer/drivers/

• Metadata Editor: /pentaho/design-tools/metadata-editor/libext/JDBC/

• Report Designer: /pentaho/design-tools/report-designer/lib/jdbc/

• Schema Workbench: /pentaho/design-tools/schema-workbench/drivers/

Keep one driver version per database in each directory. Remove older versions to avoid conflicts.

If you are unsure, contact Pentaho Support.

Microsoft SQL Server driver notes

If you use Microsoft SQL Server, you might need the jTDS driver. jTDS is not vendor supported. Confirm the correct driver with Pentaho Support.

On Windows, many JDBC drivers support Type 2 integrated authentication using the integratedSecurity connection string property. To use it, copy sqljdbc_auth.dll to every machine and directory where you copied the JDBC driver.

You can find sqljdbc_auth.dll at:

<installation directory>\sqljdbc_<version>\<language>\auth\

• Use the x64 DLL with a 64-bit JVM on an x64 processor.

• Use the IA64 DLL with a 64-bit JVM on an Itanium processor.

Define JNDI connections for Report Designer and Metadata Editor

1. Choose an access protocol in Choose an access protocol.

2. Complete these tasks on each workstation.

Add drivers

The driver lets design tools connect to the Pentaho Server and validate models.

Your database administrator or IT team can provide the driver. You can also download it from your database vendor.

Check Components Reference to confirm your driver is supported.

Copy the driver to these directories on each machine where you installed design tools. Stop the tools first.

• Aggregation Designer: /pentaho/design-tools/agg-designer/drivers/

• Metadata Editor: /pentaho/design-tools/metadata-editor/libext/JDBC/

• Report Designer: /pentaho/design-tools/report-designer/lib/jdbc/

• Schema Workbench: /pentaho/design-tools/schema-workbench/drivers/

Keep only one version of the driver in each directory. Remove older versions to avoid conflicts.

If you are unsure, contact Pentaho Support.

Configure Report Designer

1. Stop Report Designer. Stop the Pentaho Server too, if it runs locally.

   For server steps, see Stop and start the Pentaho Server and repository.

2. Go to the .pentaho directory in the user profile.

   Examples:

   • Windows: C:\Users\username\.pentaho\

   • Linux or Solaris: /home/username/.pentaho/

3. Go to ~/.pentaho/simple-jndi/. Create it if needed.

4. Edit default.properties. Create it if needed.

   Example:

   SampleData/type=javax.sql.DataSource
   SampleData/driver=org.hsqldb.jdbcDriver
   SampleData/user=pentaho_user
   SampleData/password=password
   SampleData/url=jdbc:hsqldb:mem:SampleData

   In this example, SampleData is the JNDI connection name.

5. Save the file.

6. Restart Report Designer. Restart the Pentaho Server too, if it runs locally.

Repeat this process on each workstation where you run Report Designer.

Configure Metadata Editor

1. Stop Metadata Editor. Stop the Pentaho Server too, if it runs locally.

   For server steps, see Stop and start the Pentaho Server and repository.

2. Go to the metadata-editor/simple_jndi directory under your Metadata Editor install.

3. Edit JDBC.properties. Create it if needed.

   Example:

   SampleData/type=javax.sql.DataSource
   SampleData/driver=org.hsqldb.jdbcDriver
   SampleData/user=pentaho_user
   SampleData/password=password
   SampleData/url=jdbc:hsqldb:mem:SampleData

   In this example, SampleData is the JNDI connection name.

4. Save the file.

5. Restart Metadata Editor. Restart the Pentaho Server too, if it runs locally.

Repeat this process on each workstation where you run Metadata Editor.

Specify data connections for the Pentaho Server

For business analytics, you can use the Pentaho User Console to define the connection to where you store data.

We support accessing data stored in the following ways:

• Comma separated values (CSV) files, or any file that can be converted to CSV, such as spreadsheets, XML, or other semi-structured or tabular data files. Use CSV files if you are evaluating Pentaho or you want to get started quickly.

• JDBC drivers to help with database connections.

In Pentaho Data Integration (PDI), you can make connections in each job and transformation through an input step. Although users can create connections themselves, it is best to set up shared connections for your users so that they can select the connection they need from a list.

JDBC database connections

Use JDBC database connections when the Pentaho Server connects to an RDBMS.

You choose one of these access types:

• Native (JDBC): Define the connection in Pentaho User Console (PUC).

• JNDI: Define a data source in your application server (Tomcat).

• OCI: Oracle-only option for using the Oracle Call Interface.

• ODBC: Available but not supported. Contact Pentaho Support if you must use it.

Choose between native (JDBC) and JNDI

Use native (JDBC) when you want the simplest setup.

You manage the full connection definition in PUC.

If connection details change, update each affected connection in PUC.

Use JNDI when you want central management in the application server.

This is common in production.

It can also help keep credentials out of Pentaho admin workflows.

If connection details change, update the application server configuration.

Add JDBC drivers

The Pentaho Server needs the correct JDBC driver for your database.

Get the driver from your database vendor or your IT team.

The Components Reference lists supported drivers.

Copy the driver to these directories:

• Pentaho Server: /pentaho/server/pentaho-server/tomcat/lib/

• PDI client: data-integration/lib

Restart the Pentaho Server and any PDI clients after you add drivers.

Driver conflict guidance

Keep only one driver per database type in each directory.

Remove older versions to avoid conflicts.

This matters when the driver matches your repository database type.

If you are unsure, contact Pentaho Support.

Microsoft SQL Server driver

For Microsoft SQL Server (MSSQL), you might need the non-vendor driver called jTDS.

Contact Pentaho Support to confirm the correct driver.

Most Microsoft JDBC drivers support Type 2 integrated authentication on Windows.

Use the integratedSecurity property in the connection string.

To use integrated authentication, copy sqljdbc_auth.dll to every location where you copied the JDBC driver.

You can find sqljdbc_auth.dll here:

<installation directory>\sqljdbc_<version>\<language>\auth\

Choose the correct DLL:

• 64-bit JVM on an x64 processor: use the x64 folder.

• 64-bit JVM on an Itanium processor: use the IA64 folder.

Set up JNDI connections for the Pentaho Server

If you installed the Pentaho Server using the manual installation method, you set up JNDI data sources in Tomcat.

Use this section to add JNDI data sources and update existing ones.

Before you begin

• You need IT administrator access.

• You must know how to connect to your data sources.

• You must know your OS and database setup.

Defining JNDI connections for PDI clients

If you publish to the Pentaho Server from a PDI client, you can configure the PDI client with the same JNDI settings as the Pentaho Server.

This lets you develop and test transformations without keeping your application server running.

Edit the jdbc.properties file to mirror your application server data sources:

• Location: /pentaho/design-tools/data-integration/simple-jndi/jdbc.properties

Tomcat JNDI connections

Use JNDI connections when you manage data sources in Tomcat.

Install the correct JDBC driver before you create or use a JNDI data source.

Next step: define the connection

After the driver is installed, create the connection definition in Pentaho.

See Define data connections.

See also

The native database connections in the Pentaho Suite are based on Java Database Connectivity (JDBC).

For Pentaho Server-specific connection setup, see:

• Set up native (JDBC) or OCI data connections for the Pentaho Server

• Set up JNDI connections for the Pentaho Server