Archive installation of Pentaho

If you are installing Pentaho in a production environment, use this method to install the Pentaho Server on a server machine while installing design tools on separate client workstations.

You must also choose a Pentaho Repository database, then install it yourself.

By default, the archive installation includes:

• BA plugins (such as Analyzer and Interactive Reports).

• DI plugins (such as Big Data and Marketplace).

The archive installation includes a preconfigured Tomcat web application server.

Use this method when you do not already have an application server.

For a deployment on an existing application server, see Manual installation.

An archive installation is also useful if you already have Pentaho data or repositories from a previous version.

Prerequisites

This guide assumes that you:

• Reviewed installation options in Pentaho installation.

• Checked supported platforms in Components Reference.

• Uninstalled any evaluation version of Pentaho.

• Disabled antivirus software during installation.

Audience

IT administrators who know the data environment and can run Windows or Linux commands.

Tools

You need a text editor and a ZIP tool.

You must provide:

• A supported operating system and Java runtime.

• A Pentaho Repository database (PostgreSQL, MySQL, MariaDB, MS SQL Server, or Oracle).

• JDBC drivers for the database you choose.

Login credentials

Use an account with administrative privileges.

Linux users might need root for some steps.

Requirements for archive installation

An archive installation requires these items and expertise:

Requirements	Description
You Supply	Each of the following items must meet or exceed the requirements in the Components Reference:- Computer with a supported operating system and hardware configuration. Oracle Java Runtime Environment (JRE) or Oracle Java Development Kit (JDK). Pentaho Repository database (PostgreSQL, MySQL, MariaDB, MS SQL Server, or Oracle). You can also use a preexisting Pentaho repository platform. Pentaho Repository database JDBC drivers (PostgreSQL, MySQL, MariaDB, MS SQL Server, or Oracle).
We Supply	Installation package. The web application server (Tomcat).
Technologies Used	Tomcat web application server (provided by Pentaho). A PostgreSQL, MySQL, MariaDB, MS SQL Server, or Oracle database.
Expertise	Knowledge of your networking environment, including database port numbers if they differ from the default and IP address. Permission to access installation directories. Root or administrative access.
Approximate Installation Time	60 to 90 minutes.

Download Pentaho from the Support Portal.

Archive installation process

Use this guide to install Pentaho Server using the archive (ZIP) distribution.

Archive installation includes a bundled Tomcat web application server.

If you need to deploy on an existing application server, see Manual installation.

1

Step 1: Prepare your environment

Set up prerequisites on Windows or Linux.

2

Step 2: Set up the Pentaho Repository

Initialize and configure the repository database (PostgreSQL, MySQL/MariaDB, Oracle, or MS SQL Server).

3

Step 3: Start Pentaho Server and install licenses

Start the Pentaho-provided Tomcat instance, then sign in to the Pentaho User Console.

Step 1: Prepare your environment

Windows

This procedure helps you prepare a Windows environment for an archive installation.

Process overview

1. Create Windows directory structure

2. Install Java

3. Install the Pentaho Repository host database

4. Download and unpack installation files

5. Set environment variables

6. Prepare the Pentaho Repository

Create Windows directory structure

Choose an installation path for installing Pentaho on a Windows system.

Pentaho recommends this directory structure:

1. Sign in to the machine where you will run the Pentaho Server.

2. Create the pentaho\\server directory (for example, C:\\pentaho\\server).

3. Verify you have read, write, and execute permissions for the directories you created.

4. Verify users can write to the directory where you install Pentaho.

Install Java

Pentaho requires a supported Java version.

1. Check Components Reference for supported Java versions.

2. Download and install the supported JRE or JDK from the Oracle website.

3. Set JAVA_HOME to your Java installation directory.

Example:

set JAVA_HOME="C:\\Program Files\\Java\\<JDK_FOLDER>"

Install the Pentaho Repository host database

The Pentaho Repository stores data used for scheduling and security.

It also stores metadata and models for reports.

1. Choose and install a database to host the Pentaho Repository.

Pentaho supports:

• PostgreSQL

• MySQL

• MariaDB

• Oracle

• MS SQL Server

1. Check Components Reference for supported database versions.

2. Install the database you selected.

3. Verify the database is installed correctly.

Download and unpack installation files

Pentaho Server and Operations Mart are delivered as compressed files.

Download them, unpack them, then move their contents to the correct directories.

Download files

1. Sign in to the Support Portal using the credentials from your Pentaho Welcome Packet.

2. In the Pentaho card, select Download.

3. In the 11.x list, select Pentaho 11.0 GA Release.

You might need to select See all <number> articles to see the full list of 11.x downloads.

1. Scroll to the bottom of the Pentaho 11.0 GA Release page.

2. In the file component section, go to Pentaho Server\\Archive Build (Suggested Installation Method).

3. Download pentaho-server-ee-11.0.0.0-<build number>.zip.

4. In the file component section, go back to 11.0.0.0-GA.

5. Open the Operations Mart folder.

6. Download pentaho-operations-mart-11.0.0.0-<build number>.zip.

Unpack installation files

1. Locate the two files you downloaded:

   • pentaho-server-ee-11.0.0.0-<build number>.zip

   • pentaho-operations-mart-11.0.0.0-<build number>.zip

2. Unpack pentaho-server-ee-11.0.0.0-<build number>.zip into the \\pentaho\\server directory.

3. Unpack pentaho-operations-mart-11.0.0.0-<build number>.zip into the \\pentaho\\server directory.

4. Move the directory for your database into pentaho\\server\\pentaho-server\\data\\<database name>. Delete the others.

Use these directory names:

• PostgreSQL → postgresql

• MySQL or MariaDB → mysql5

• Oracle → oracle10g

• MS SQL Server → sqlserver

1. Complete the steps for installing Data Integration Operations Mart in the Administer Pentaho Data Integration and Analytics document before installing your database.

Verify directory structure

Verify the files are placed correctly.

Your pentaho directory should include:

• pentaho\\server\\pentaho-server\\

• pentaho\\server\\pentaho-server\\data\\<database name>

Set environment variables

If you do not set the required *_HOME environment variables, Pentaho might not start correctly.

1. Set PENTAHO_JAVA_HOME to your Java installation directory:

set PENTAHO_JAVA_HOME="C:\\Program Files\\Java\\<JDK_FOLDER>"

1. (Optional) If you are using a JRE, also set JRE_HOME.

2. Sign out and sign back in, then verify the variables are set.

Example:

echo %PENTAHO_JAVA_HOME%

Prepare the Pentaho Repository

After you finish preparing your environment, go to Step 2: Set up the Pentaho Repository.

Linux

Step 2: Set up the Pentaho Repository

The Pentaho Repository resides on the database you installed during environment preparation.

It includes these components:

• Jackrabbit Contains the solution repository, examples, security data, and report content.

• Quartz Holds data related to scheduling reports and jobs.

• Hibernate Holds data related to audit logging.

• (Optional) Pentaho Operations Mart Reports on system usage and performance.

Select the database you are using.

PostgreSQL

Use this procedure to install PostgreSQL as the host database for the Pentaho Repository in an archive installation.

• Before you begin

• Initialize the PostgreSQL Pentaho Repository database

• Configure the PostgreSQL Pentaho Repository database

• Perform Tomcat-specific connection tasks

• Start your server

Before you begin

Prerequisite

Before you prepare your Pentaho Repository, complete Step 1: Prepare your environment.

Components

The Pentaho Repository resides on the database that you installed during the environment preparation step.

It consists of these components:

• Jackrabbit

  Contains the solution repository, examples, security data, and content data from reports that you use Pentaho software to create.

• Quartz

  Holds data related to scheduling reports and jobs.

• Hibernate

  Holds data related to audit logging.

• (Optional) Pentaho Operations Mart

  Reports on system usage and performance.

Initialize the PostgreSQL Pentaho Repository database

To initialize PostgreSQL so it serves as the Pentaho Repository, run SQL scripts to create these components:

• Hibernate

• Quartz

• Jackrabbit (JCR)

• (Optional) Pentaho Operations Mart

CAUTION:

Use the ASCII character set when you run these scripts.

Do not use UTF-8.

Text string length limitations might cause the scripts to fail.

CAUTION:

If you use a different password or user, update the examples to match.

CAUTION:

Pentaho 10.2.0.0 and earlier use Quartz 1.x.

Quartz 1.x uses a QRTZ5_ prefix in the database.

Pentaho 10.2.0.1 and later use Quartz 2.x.

Quartz 2.x uses a QRTZ6_ prefix in the database.

When upgrading from an earlier version to Pentaho 10.2.0.1 and later, create the QRTZ6_ tables by repeating the Quartz database creation procedure.

You can optionally migrate existing Quartz schedules using the migration script.

During this upgrade procedure, executing the SQL create script resets the corresponding Quartz database without loss of the original data.

However, the other associated repository databases in 10.2.0.1 will be reset by SQL scripts if run, and data might be deleted.

Always back up your data before proceeding.

Important: If you do not complete the Quartz upgrade, Pentaho Server fails at startup.

You will also see this exception message in catalina.log:

Missing Quartz library database error

Step 1: Change default passwords

For production, follow best practices and change the default passwords in the SQL script files.

Note: If you are evaluating Pentaho, you might want to skip this step.

1. Browse to the <your pentaho directory>/pentaho-server/data/postgresql folder.

2. Use a text editor to modify these create scripts as needed for your user, password, database, and other values:

   • create_jcr_postgresql.sql

   • create_quartz_postgresql.sql

   • create_repository_postgresql.sql

   • pentaho_mart_postgresql.sql

3. Save and close the files.

Step 2: Run SQL scripts

To use PostgreSQL as the database for your Pentaho Repository, you must initialize the Jackrabbit, Quartz, Hibernate, and Operations Mart components.

You can run SQL scripts developed by Pentaho to create and initialize these components.

When upgrading from earlier Pentaho versions, including 10.2.0.0 GA to 10.2.0.1 and later, you must manually initialize a new Quartz database.

This creates a new Quartz library in the repository database.

Back up your data before proceeding.

If you want to keep your existing Quartz library data, you can migrate the current tables to the new tables.

Note: You may need administrator permissions to run these scripts on the host OS server.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 10.3.

2. If Pentaho Server is running, stop it.

   See Stop and start the Pentaho Server and repository.

3. Ensure your PostgreSQL instance is running.

4. Open a PSQL console window and run these SQL creation scripts in this order:

   1. <your pentaho directory>/pentaho-server/data/postgresql/create_quartz_postgresql.sql

   2. <your pentaho directory>/pentaho-server/data/postgresql/create_jcr_postgresql.sql

   3. <your pentaho directory>/pentaho-server/data/postgresql/create_repository_postgresql.sql (initializes Hibernate)

   4. <your pentaho directory>/pentaho-server/data/postgresql/pentaho_mart_postgresql.sql

   The Quartz database, Jackrabbit and PostgreSQL repositories, and Pentaho Operations Mart are created.

   The new Quartz scheduler tables prefixed with QRTZ6_ are created.

   Any existing QRTZ5_ scheduler database is retained.

   Starting the Pentaho Server at this point results in an empty schedule.

5. If you want to retain your existing scheduler database, open migrate_old_quartz_data_postgresql.sql in a text editor.

6. Update the migration script as needed for your user, password, database, and other values.

7. Run the migration script:

   <your pentaho directory>/pentaho-server/data/postgresql/migrate_old_quartz_data_postgresql.sql

The original Quartz scheduler database is migrated to the new Quartz database.

Note: The original QRTZ5_ data is retained.

You can delete it after you confirm the migration completed successfully.

Note: You unpacked the Pentaho Operations Mart SQL file while preparing your environment for archive installation.

Step 3: Verify PostgreSQL initialization

Note: Unless you changed it in Step 1, the default password for each username is password.

After you run the scripts, verify the databases and user roles were created correctly:

1. Open pgAdminIII or a similar client tool.

2. Log in as hibuser.

3. Verify that you can see the tables under hibernate.

4. If you installed Pentaho Operations Mart, verify that you can see the tables under pentaho_operations_mart.

5. Log in as jcr_user.

6. Verify that you can see the tables under jackrabbit.

   The Jackrabbit tables might not appear until you start Pentaho for the first time.

7. Log in as pentaho_user.

8. Verify that you can see the tables under quartz.

9. Exit from the tool.

You have initialized the PostgreSQL Pentaho Repository database.

Configure the PostgreSQL Pentaho Repository database

After you initialize your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for PostgreSQL.

Note: PostgreSQL is configured by default.

If you kept the default passwords and port, you can skip to Perform Tomcat-specific connection tasks.

By default, the examples in this section use a PostgreSQL database running on port 5432 with the default password.

CAUTION:

If you use a different port or password, update the examples to match.

Step 1: Set up Quartz on PostgreSQL

Event information, such as scheduled reports, is stored in the Quartz JobStore.

During installation, specify where the JobStore is located by modifying quartz.properties.

1. Open pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties in a text editor.

2. In the #_replace_jobstore_properties section, set org.quartz.jobStore.driverDelegateClass:

   org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

3. Save and close the file.

Step 2: Set Hibernate settings for PostgreSQL

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file.

The Hibernate configuration file specifies driver and connection information, dialects, and connection close and timeout behavior.

Note: Pentaho Server also stores audit logs in the Hibernate database.

The files in this section are located in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

1. Open hibernate-settings.xml in a text editor.

2. Find the <config-file> tags and confirm this value is set for PostgreSQL:

   <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

3. Save and close the file if you made changes.

Step 3: Modify Jackrabbit repository information for PostgreSQL

Edit repository.xml to use PostgreSQL for the default Jackrabbit repository.

1. Go to pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit.

2. Open repository.xml in a text editor.

3. Ensure the PostgreSQL lines are not commented out.

4. Ensure the MySQL, Oracle, and MS SQL Server lines are commented out.

Item	Code Section
Repository	<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="postgresql"/> <param name="schemaObjectPrefix" value="fs_repos_"/> </FileSystem>
DataStore	<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="databaseType" value="postgresql"/> <param name="minRecordLength" value="1024"/> <param name="maxConnections" value="3"/> <param name="copyWhenReading" value="true"/> <param name="tablePrefix" value=""/> <param name="schemaObjectPrefix" value="ds_repos_"/> </DataStore>
Workspaces	<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="postgresql"/> <param name="schemaObjectPrefix" value="fs_ws_"/> </FileSystem>
PersistenceManager (1st part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="postgresql"/> <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/> </PersistenceManager>
Versioning	<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="postgresql"/> <param name="schemaObjectPrefix" value="fs_ver_"/> </FileSystem>
PersistenceManager (2nd part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="postgresql"/> <param name="schemaObjectPrefix" value="pm_ver_"/> </PersistenceManager>
DatabaseJournal	<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal"> <param name="revision" value="${rep.home}/revision.log" /> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="postgresql"/> <param name="schemaObjectPrefix" value="cl_j_"/> <param name="janitorEnabled" value="true"/> <param name="janitorSleep" value="86400"/> <param name="janitorFirstRunHourOfDay" value="3"/> </Journal>

Perform Tomcat-specific connection tasks

After you configure the repository, configure your web application servers to connect to it.

In this step, you make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Note: By default, Pentaho Server is configured to run on Tomcat.

Connections are already specified.

Modify Tomcat context.xml only if you changed the default ports or passwords.

Step 1: Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, you must download and copy a JDBC driver to the correct locations.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers.

You must download and install the file yourself.

1. Download a JDBC driver JAR from your database vendor or a third-party driver developer.

   See JDBC drivers reference for supported drivers.

2. Copy the JDBC driver JAR to pentaho/server/pentaho-server/tomcat/lib.

3. If you want to keep the Pentaho sample, copy hsqldb-2.3.2.jar to pentaho-server/tomcat/lib.

Step 2: Modify JDBC connection information in the Tomcat XML file

Database connection and network information is stored in context.xml.

Modify context.xml to match your environment.

CAUTION:

If you use a different port, password, user, driver class, or host name, update the examples to match.

1. Consult your database documentation to determine the JDBC class name and connection string.

2. Go to server/pentaho-server/tomcat/webapps/pentaho/META-INF.

3. Open context.xml in a text editor.

4. Add the following code if it does not already exist:

   <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   <Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1" />
   <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="pentaho_user" password="password" testOnBorrow="true"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
           validationQuery="select 1"/>
   <Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1"/>
   <Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1"/>
   <Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="hibuser" password="password" driverClassName="org.postgresql.Driver"
           url="jdbc:postgresql://localhost:5432/hibernate?searchpath=pentaho_dilogs"
           validationQuery="select 1"/>
   <Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="pentaho_user" password="password"
           driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
           validationQuery="select 1"/>
   <Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="pentaho_admin" password="password"
           driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
           validationQuery="select 1"/>
   <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="jcr_user" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jackrabbit"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

5. Update the user name, password, driver class, host name, and port values for your environment.

6. Comment out any resource references that refer to other databases.

7. Set validationQuery to select 1.

8. Save and close context.xml.

Start your server

After you complete the archive installation steps, start the Pentaho Server.

See Step 3: Start Pentaho Server and install licenses.

MySQL or MariaDB

Oracle

MS SQL Server

Step 3: Start Pentaho Server and install licenses

The Pentaho Server is located on the Pentaho-provided Tomcat web application server.

The way you start the Pentaho Server depends on your operating system.

1. Navigate to the <your pentaho directory>/server/pentaho-server folder.

2. Run the startup script for your operating system:

   • Windows: start-pentaho.bat

   • Linux: start-pentaho.sh

   The Tomcat web application server and the Pentaho Server start.

3. From a workstation, open a web browser and enter http://localhost:8080/pentaho in the address bar.

   If your server has a different hostname or port, replace localhost or 8080.

4. Enter your user name and password, then select Login.

   The Pentaho User Console (PUC) opens.

   Upon first login after installation, enter a license server code or individual license keys.

See the Administer Pentaho Data Integration and Analytics document for license setup and optional Operations Mart logging.

If you have problems starting the Pentaho Server, see Troubleshooting possible installation and upgrade issues.

Install client tools

After you install and start the Pentaho Server, install the Pentaho client tools on separate workstations.

• Installation of the Pentaho design tools

• Install PDI tools and plugins

After you install the server and client tools, configure access, licenses, and data sources.

See Pentaho configuration.