Overview

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 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 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.

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.

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:

If the databases and login roles do not appear, go to the beginning of these instructions and try running the scripts again.

Select File > Exit to exit from pgAdminIII.

Initialize MySQL BA Repository Database

To initialize MySQL 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: 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.

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

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

grant all on jackrabbit.* to 'jcr_user'@'localhost' identified by 'myNewPassword';

The process for running the SQL scripts against MySQL are the same for both Windows and Linux machines.

Run the create_quartz_mysql.sql script in the Terminal or Command Prompt window by typing: mysql -u root -p < create_quartz_mysql.sql.

Run the create_repository_mysql.sql script in the Terminal or Command Prompt window by typing: mysql -u root -p < create_repository_mysql.sql.

Run the create_jcr_mysql.sql script in the Terminal or Command Prompt window by typing: mysql -u root -p < create_jcr_mysql.sql.

Run the pentaho_mart_mysql.sql script in the Terminal or Command Prompt window by typing: mysql -u root -p < pentaho_mart_mysql.sql.

To verify that databases and user roles have been created, do this.

Open the MySQL Workbench tool. MySQL Workbench is freely available at the MySQL development site.

Make sure that the Jackrabbit (JCR), Hibernate, and Quartz databases are present.

Make sure that the jcr_user, hibuser, and pentaho_user user accounts are present.

If the databases and login roles do not appear, go to the beginning of these instructions and try running the scripts again.

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.

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.

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.

Open a Terminal or Command Prompt window, start the SQL*Plus and log in.

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.

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.

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.

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.

To verify that databases and user roles have been created, do this.

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.

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.

To see the users that have been created, type SELECT USERNAME FROM DBA_USERS.

If the databases and login roles do not appear, go to the beginning of these instructions and try running the scripts again.