Skip to main content
Hitachi VantaraPentaho Documentation
Pentaho Documentation

Initialize Repository

Overview

Indicates how to initialize the DI Repository.

Image of the DI Server installation steps.

Before you prepare the DI Repository complete the tasks in Prepare Environment.

Pentaho stores content about reports that you create, examples we provide, report scheduling data, and audit data in the DI Repository. The DI Repository resides on the database that you installed during the Prepare Environment step. The DI Repository consists of three repositories: Jackrabbit, Quartz, and Hibernate.
  • Jackrabbit contains the solution respository, examples, security data, and content data from reports that you use Pentaho software to create.
  • Quartz holds data that is related to scheduling reports and jobs.
  • Hibernate holds data that is related to audit logging.

This step only consists of one task: Initialize the database. In this task you run DDLs that contain SQL commands that create the Jackrabbit, Quartz, and Hibernate databases, as well as the Operations Mart schema.

Initialize PostgreSQL DI Repository Database

To initialize PostgreSQL so that it serves as the DI Repository, run SQL scripts to create the Hibernate, Quartz and Jackrabbit (also known as the JCR) databases.

Note: Your PostgreSQL configuration must support logins from all users. This is not always the default configuration, so you may have to edit your pg_hba.conf file to support this option. If you do need to make changes to pg_hba.conf, you must restart the PostgreSQL server before proceeding.

  1. To make the databases that you create more secure, Pentaho recommends that you change the default passwords in the SQL script files to ones that you specify. If you are evaluating Pentaho, you might want to skip this step. If you do decide to make the databases more secure, use a text editor to change the passwords in these files:
    • pentaho/server/data-integration-server/data/postgresql/create_jcr_postgresql.sql
    • pentaho/server/data-integration-server/data/postgresql/create_quartz_postgresql.sql
    • pentaho/server/data-integration-server/data/postgresql/create_repository_postgresql.sql

    Here is an example of a password change made in the create_jcr_postgresql.sql file.

    CREATE USER jcr_user PASSWORD 'myNewPassword'
  2. Windows: The commands you use to run the SQL scripts depends on your operating system. For windows, do this.
    1. Open a SQL Shell window. The SQL Shell window is installed with PostgreSQL.
    2. When prompted for the server enter the name of the server if you are not using the default (localhost). If you are using the default, do not type anything and press Enter.
    3. When prompted for the database enter the name of the database if you are not using the default (postgres) If you are using the default, do not type anything and press Enter.
    4. When prompted for the port enter the name of the port if you are not using the default (5432). If you are using the default port, do not type anything and press Enter.
    5. When prompted for the username, accept the default, then press Enter.
    6. When prompted for the password, enter the password that you indicated when you installed PostgreSQL.
    7. Run the script to create the Jackrabbit database by typing this.
      \i /pentaho/server/data-integration-server/data/postgresql/create_jcr_postgresql.sql
      Note:If necessary, change the \pentaho\server\data-integration-server to the place where you unpacked your pentaho files.
    8. Run the script to create the hibernate database by typing this.
      \i /pentaho/server/data-integration-server/data/postgresql/create_repository_postgresql.sql
    9. Run the script to create the Quartz database by typing this.
      \i /pentaho/server/data-integration-server/data/postgresql/create_quartz_postgresql.sql
    10. To switch to the Hibernate database, type this.
      \c postgres
    11. Run the script to create the Operations Mart database by typing this.
      \i /pentaho/server/data-integration-server/data/postgresql/pentaho_mart_postgresql.sql
    12. Exit from the window by pressing the CTRL + C keys.
  3. Linux: To run the SQL scripts on a Linux system, do this.
    1. Open a Terminal window. You should be logged in as the pentaho user.
    2. Sign into PostgreSQL by typing psql -U postgres -h localhost at the prompt.
    3. Run the script to create the Jackrabbit database by typing this.
      
      \i ~/pentaho/server/data-integration-server/data/postgresql/create_jcr_postgresql.sql
      Note:If necessary, change the ~/pentaho/server/data-integration-server directory path to the place where you unpacked your pentaho files.
    4. Run the script to create the hibernate database by typing this.
      \i ~/pentaho/server/data-integration-server/data/postgresql/create_repository_postgresql.sql
      Note:If necessary, change the ~/pentaho/server/data-integration-server to the place where you unpacked your pentaho files.
    5. Run the script to create the Quartz database by typing this.
      \i ~/pentaho/server/data-integration-server/data/postgresql/create_quartz_postgresql.sql
      Note:If necessary, change the ~/pentaho/server/data-integration-server directory path to the place where you unpacked your pentaho files.
    6. To switch to the Hibernate database, type this.
      \c postgres
    7. Run the script to create the Operations Mart database by typing this.
      \i ~/pentaho/server/data-integration-server/data/postgresql/pentaho_mart_postgresql.sql
    8. Exit from the window by pressing the CTRL + C keys.
  4. To verify that databases and user roles have been created, do this.
    1. Open the pgAdminIII tool. pgAdminIII is bundled with both the Windows and Linux versions of PostgreSQL.
    2. To view the contents of PostgreSQL, click the PostgreSQL folder in the Object Browser, then enter the password when prompted.
    3. In the Object Browser, click the Databases folder. The Jackrabbit, Postgres, Hibernate and Quartz databases appear.
    4. In the Object Browser, click the Login Roles folder. The jcr_user, pentaho_user, hibuser, and postgres user accounts appear.
    5. If the databases and login roles do not appear, go to the beginning of these instructions and try running the scripts again.
    6. Select File > Exit to exit from pgAdminIII.

Initialize MySQL DI Repository Database

To initialize MySQL so that it serves as the DI Repository, run SQL scripts to create the Hibernate, Quartz and Jackrabbit (also known as the JCR) databases.

Use the ASCII character set when you run these scripts. Do not use UTF-8 because there are text string length limitations that might cause the scripts to fail.

If you are using Redshift, after you finish this section, move on to the configuring MySQL tasks.

These sections take you through the steps to initialize the MySQL DI repository database.

Change Default Passwords

To make the databases that you create more secure, Pentaho recommends that you change the default passwords in the SQL script files to ones that you specify. If you are evaluating Pentaho, you might want to skip this step. If you do decide to make the databases more secure, use a text editor to change the passwords in these files:

  • pentaho/server/data-integration-server/data/mysql5/create_jcr_mysql.sql
  • pentaho/server/data-integration-server/data/mysql5/create_quartz_mysql.sql
  • pentaho/server/data-integration-server/data/mysql5/create_repository_mysql.sql
  • pentaho/server/data-integration-server/data/mysql5/pentaho_mart_mysql.sql

Run SQL Scripts

Once you change the passwords, you will need to run these SQL scripts. The process for running SQL scripts against MySQL is the same for Windows or Linux machines.

Run these scripts from the MySQL Command Prompt window or from MySQL Workbench.

Action SQL Script
Create Quartz > source <your filepath>\create_quartz_mysql.sql
Create Hibernate repository > source <your filepath>\create_repository_mysql.sql
Create Jackrabbit > source <your filepath>\create_jcr_mysql.sql
Create Pentaho Operations mart > source <your filepath>\pentaho_mart_mysql.sql

Verify MySQL Initialization

After you run the scripts, this list will help you verify that databases and user roles have been created.

  1. Open the MySQL Workbench tool. MySQL Workbench is freely available at the MySQL development site.
  2. Make sure that the Quartz, Jackrabbit (JCR), Hibernate, and Pentaho Operations mart databases are present.
  3. Exit from the MySQL Workbench.

Initialize Oracle DI Repository Database

To initialize Oracle so it serves as the DI Repository, run SQL scripts to create the Hibernate, Quartz and Jackrabbit (also known as the JCR) databases.

  1. To make the databases that you create more secure, Pentaho recommends that you change the default passwords in the SQL script files to ones that you specify. If you are evaluating Pentaho, you might want to skip this step. If you do decide to make the databases more secure, use a text editor to change the passwords in these files. (Also, for each file, edit the datafile path with the path to your Oracle installation.)
    • pentaho/server/data-integration-server/data/oracle10g/create_jcr_ora.sql
    • pentaho/server/data-integration-server/data/oracle10g/create_quartz_ora.sql
    • pentaho/server/data-integration-server/data/oracle10g/create_repository_ora.sql

    Here is an example of a password change made in the create_jcr_ora.sql file.

    --conn admin/myNewPassword@pentaho
    create user di_jcr_user identified by "myNewPassword" default tablespace di_pentaho_tablespace quota unlimited on pentaho_tablespace temporary tablespace temp quota 5M on system;
    
    Note: The user name above (jcr_user) might be different in your create_jcr_ora.sql file.
  2. Although there are several different methods for running SQL scripts, these instructions explain how to run SQL*Plus from a Terminal or Command Prompt window. These instructions are the same for both Windows and Linux. If you prefer to run SQL scripts using another method, modify instructions accordingly.
    1. Open a Terminal or Command Prompt window, start the SQL*Plus and log in.
    2. Run the script to create the Jackrabbit database by typing START create_jcr_ora. If necessary, append the path to the create_jcr_ora.sql path in the command.
    3. Run the script to create the repository database by typing START create_repository_ora. If necessary, append the path to the create_repository_ora.sql path in the command.
    4. Run the script to create the Quartz database and users by typing START create_quartz_ora. If necessary, append the path to the create_quartz_ora.sql path in the command.
    5. Run the script to create the Operations Mart database and users by typing START pentaho_mart_ora. If necessary, append the path to the pentaho_mart_ora.sql path in the command.
  3. To verify user roles have been created, do this.
    1. In the Terminal or Command Prompt window that is running SQL*Plus, type SELECT USERNAME FROM DBA_USERS to see the users that have been created.
    2. If the usernames do not appear, go to the beginning of these instructions and try running the scripts again.
    3. Exit from SQL*Plus.