Skip to main content
Pentaho Documentation

Initialize Repository

Overview

Explains how to initialize the BA repository.

Before you prepare the BA 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 BA Repository. The BA Repository resides on the database that you installed during the Prepare Environment step. The BA Repository consists of three repositories: Jackrabbit, Quartz, and Hibernate.
  • Jackrabbit contains the solution repository, 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.

If you are using Redshift, you will need to follow the section for initializing MySQL or other supported database, then move onto the configuring MySQL tasks.

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 BA Repository Database

To initialize PostgreSQL so that it serves as the BA 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/biserver-ee/data/postgresql/create_jcr_postgresql.sql
    • pentaho/server/biserver-ee/data/postgresql/create_quartz_postgresql.sql
    • pentaho/server/biserver-ee/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/biserver-ee/data/postgresql/create_jcr_postgresql.sql
      Note:If necessary, change the \pentaho\server\biserver-ee to the place where you unpacked your pentaho files.
    8. Run the script to create the hibernate database by typing this.
      \i /pentaho/server/biserver-ee/data/postgresql/create_repository_postgresql.sql
    9. Run the script to create the Quartz database by typing this.
      \i /pentaho/server/biserver-ee/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/biserver-ee/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/biserver-ee/data/postgresql/create_jcr_postgresql.sql
      Note:If necessary, change the ~/pentaho/server/biserver-ee 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/biserver-ee/data/postgresql/create_repository_postgresql.sql
      Note:If necessary, change the ~/pentaho/server/biserver-ee to the place where you unpacked your pentaho files.
    5. Run the script to create the Quartz database by typing this.
      \i ~/pentaho/server/biserver-ee/data/postgresql/create_quartz_postgresql.sql
      Note:If necessary, change the ~/pentaho/server/biserver-ee 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/biserver-ee/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 should 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 BA Repository Database

To initialize MySQL so that it serves as the BA Repository, you will need to run a few SQL scripts to create the Hibernate, Quartz, Pentaho Operations mart, 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 BA 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/biserver-ee/data/mysql5/create_jcr_mysql.sql
  • pentaho/server/biserver-ee/data/mysql5/create_quartz_mysql.sql
  • pentaho/server/biserver-ee/data/mysql5/create_repository_mysql.sql
  • pentaho/server/biserver-ee/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 BA Repository Database

To initialize Oracle so it serves as the BA 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/biserver-ee/data/oracle10g/create_jcr_ora.sql
    • pentaho/server/biserver-ee/data/oracle10g/create_quartz_ora.sql
    • pentaho/server/biserver-ee/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@pentahocreate user jcr_user identified by "myNewPassword" default tablespace pentaho_tablespace quota unlimited on pentaho_tablespace temporary tablespace temp quota 5M on system;
  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 that databases and user roles have been created, do this.
    1. In the Terminal or Command Prompt window that is running SQL*Plus, make sure that the Jackrabbit database has been created by typing DESCRIBE JACKRABBIT;. The column definitions should appear when you press Enter.
    2. Make sure the Quartz database has been created by typing DESCRIBE QUARTZ;. The column definitions for the Quartz table should appear when you press Enter.
    3. To see the users that have been created, type SELECT USERNAME FROM DBA_USERS.
    4. If the databases and login roles do not appear, go to the beginning of these instructions and try running the scripts again.
    5. Exit from SQL*Plus.