Upgrade : Orchestra Business Intelligence
  

Orchestra Business Intelligence

Orchestra Business Intelligence is normally upgraded as part of an Orchestra Central upgrade. However, there are a few things that need to be taken into consideration, before going ahead with a Central upgrade, containing Orchestra Business Intelligence.

General Information

Please note that the Upgrade wizard will run all necessary scripts etc for you. There is no need to run any scripts manually, unless specifically stated!

When Upgrading from Orchestra 5.4 - Checklist

When upgrading from Orchestra 5.4 to a later version, there are a few things that you need to consider:
 
Prerequisite
Check
N/A
Since all of Business Intelligence is completely reinstalled during the Upgrade process, please make a backup of the <Orchestra home>/pentaho-solutions/system/ folder, before upgrade.
 
 
Language packs from the Pentaho market place and Language Packs created using the languagePackInstaller will no longer work, after the upgrade. You need to follow the instructions in “Translation of Orchestra Business Intelligence” , to manually introduce a new language to the Business Intelligence application.
 
 
If you want to move your Business Analytics Repository from another database to MS SQL, see “Business Analytics Repository, MS SQL” .
 
 
If you want to move your Business Analytics Repository from another database to Oracle, see “Business Analytics Repository, Oracle” .
 
 
Follow the steps in “JBoss Related Configuration, MS SQL” , or “JBoss Related Configuration, Oracle” , depending on which database you want to use.
 
 
If you are using a customised mondrian file, or have performed other changes to your data sources, these will not be transferred automatically, during the upgrade. You need to manually import your customised mondrian file and perform these updates again. For more information, please see the Business Intelligence chapter of the Administrator’s Guide.
 
 

Business Analytics Repository, MS SQL

If you have upgraded from Orchestra 5.4 to a later version and want to move your Business Analytics Repository to MS SQL, follow these steps:

1. Configure Quartz on MS SQL BA Repository Database

When you use Orchestra Business Intelligence to schedule an event, such as a report to be run every Sunday at 1:00 a.m. EST, event information is stored in the Quartz JobStore.
Modify the file quartz.properties to indicate where the JobStore for scheduling is located.
1. Open the <Orchestra home>/pentaho-solutions/system/quartz/quartz.properties file in the text editor of your choice.
2. In the #_replace_jobstore_properties section of the file, set the following:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate
 
3. In the # Configure Datasources section, set the following:
org.quartz.datasource.myDS.jndiURL = Quartz
 
4. Save the file and close the text editor.

2. Configure Hibernate settings for SQL Server

Modify the hibernate settings file to specify where Business Intelligence will find the BA Repository’s hibernate configuration file.
The hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.
1. Open <Orchestra home>/pentaho-solutions/system/hibernate/hibernate-settings.xml in a text editor.
2. By default, the system indicates the location of the PostgreSQL hibernate configuration file.
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
Change this to point to the SQL Server configuration file:
<config-file>system/hibernate/sqlserver.hibernate.cfg.xml</config-file>
3. Save and close the file.
4. Open the file <Orchestra home>/pentaho-solutions//system/hibernate/sqlserver.hibernate.cfg.xml in a text editor.
5. Make sure that the password and port number match the ones you specified in your configuration. Make changes, as necessary, then save and close the file.

3. Replace Default Version of Audit Log File with SQL Server Version

The default audit_sql.xml file that is in the pentaho-solutions/system directory is configured for the PostgreSQL database.
Since you are using SQL Server to host the BA Repository, you need to replace the audit_sql.xml file with one that is configured for SQL Server.
To do this, copy the <Orchestra home>/pentaho-solutions/system/dialects/sqlserver/audit_sql.xml file to the <Orchestra home>/pentaho-solutions/system directory. Update the JNDI tag as <JNDI>Audit</JNDI>.

4. Modify Jackrabbit Repository Information for SQL Server

You must indicate which database is used as the BA Repository as well as the port, url, username, and password.
All of the information needed to configure the repository for the PostgreSQL, SQL Server, and Oracle BA Repository databases appear.
By default, the PostgreSQL sections are not commented out, but the SQL Server and Oracle sections are.
Modify this file so that it works for your BA Repository:
1. Use a text editor to open the <Orchestra home>/pentaho-solutions/system/jackrabbit/repository.xml file.
2. In the Repository part of the code, change the code so that the SQL Server lines of code are not commented out, but the PostgreSQL and Oracle lines are.
Make sure that the <param name="url" value="jdbc:sqlserver://localhost:1433:DatabaseName=jackrabbit"/> parameter indicates the correct server and database name.
If the password for the jcr_user was modified during the preparation step, the correct value must be inserted here.
<!--
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>
 
3. In the DataStore section of the code, change the code so that the SQL Server lines of code are not commented out, but the PostgreSQL and Oracle lines are, like this.
Make sure that the <param name="url" value="jdbc:sqlserver://localhost:1433:DatabaseName=jackrabbit"/> parameter indicates the correct database name.
<!--
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
<param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="databaseType" value="mssql"/>
<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>
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
4. In the Workspaces section of the code, change the code so that the SQL Server lines of code are not commented out, but the PostgreSQL and Oracle lines are.
Make sure that the <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/> parameter indicates the correct database name.
<!--
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="fs_ws_"/>
</FileSystem>
 
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
5. In the Persistence Manager section of the code, change the code so that the SQL Server lines of code are not commented out, but the PostgreSQL and Oracle lines are.
Make sure that the <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/> parameter indicates the correct database name.
<!--
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
<param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
6. In the Versioning section of the code, change the code so that the SQL Server lines of code are not commented out, but the PostgreSQL and Oracle lines are, like this.
Make sure that the <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/> parameter indicates the correct database name.
<!--
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>
 
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
7. In the Persistence Manager section of the code that is near the end of the file, change the code so that the SQL Server lines of code are not commented out, but the PostgreSQL and Oracle lines are, like this.
Make sure that the <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/> parameter indicates the correct database name.
<!--
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
<param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="pm_ver_"/>
</PersistenceManager>
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
8. Replace the Journal section, at the end, with:
<Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
<param name="revision" value="${rep.home}/revision.log" />
<param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="cl_j_"/>
</Journal>
 
9. Stop Orchestra.
10. Delete <orchestra_home>/pentaho-solutions/system/jackrabbit/repository/ folder.
11. Start Orchestra.

Business Analytics Repository, Oracle

If you have upgraded from Orchestra 5.4 to a later version and want to move your Business Analytics Repository to Oracle, follow these steps:

1. Configure Quartz on Oracle BA Repository Database

When you use Pentaho to schedule an event, such as a report to be run every Sunday at 1:00 a.m. EST, event information is stored in the Quartz JobStore.
Modify the file quartz.properties to indicate where the JobStore for scheduling is located.
1. Open the <Orchestra home>/pentaho-solutions/system/quartz/quartz.properties file in the text editor of your choice.
2. In the #_replace_jobstore_properties section of the file, set the following:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
 
3. Save the file and close the text editor.

2. Configure Hibernate settings for Oracle

Modify the hibernate settings file to specify where Business Intelligence will find the BA Repository’s hibernate configuration file.
The hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.
1. Open <Orchestra home>/pentaho-solutions/system/hibernate/hibernate-settings.xml in a text editor.
2. By default, the system indicates the location of the PostgreSQL hibernate configuration file.
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
Change this to point to the Oracle configuration file:
<config-file>system/hibernate/oracle10g.hibernate.cfg.xml</config-file>
3. Save and close the file.
4. Open the file <orchestra_home>/pentaho-solutions//system/hibernate/oracle10g.hibernate.cfg.xml in a text editor.
5. Make sure that the password and port number match the ones you specified in your configuration. Make changes, as necessary, then save and close the file.

3. Replace Default Version of Audit Log File with Oracle Version

The default audit_sql.xml file that is in the pentaho-solutions/system directory is configured for the PostgreSQL database.
Since you are using Oracle to host the BA Repository, you need to replace the audit_sql.xml file with one that is configured for Oracle.
To do this, copy the <Orchestra home>/pentaho-solutions/system/dialects/oracle10g/audit_sql.xml file to the <Orchestra home>/pentaho-solutions/system directory.

4. Modify Jackrabbit Repository Information for Oracle

You must indicate which database is used as the BA Repository as well as the port, url, username, and password.
All of the information needed to configure the repository for your BA Repository database appears.
Modify this file so that it works for your BA Repository:
1. Use a text editor to open the <Orchestra home>/pentaho-solutions/system/jackrabbit/repository.xml file.
2. In the Repository part of the code, change the code so that it matches your database installation, as in the example below.
Make sure that the <param value="url" value="jdbc:oracle:thin:@localhost:1521/XE"/> parameter indicates the correct server and database name.
If the password for the jcr_user was modified during the preparation step, the correct value must be inserted here.
The tablespace name must be changed to pentaho_tablespace
<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
<param name="url" value="jdbc:oracle:thin:@localhost:1521/XE"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
<param name="tablespace" value="pentaho_tablespace"/>
</FileSystem>
 
3. In the DataStore section of the code, change the code so that it matches your database installation, as in the example below.
Make sure that the <param value="url" value="jdbc:oracle:thin:@localhost:1521/XE"/> parameter indicates the correct database name. You may need to modify the parameter if your database has a different name than orcl.
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
<param name="url" value="jdbc:oracle:thin:@localhost:1521/XE"/>
<param name="driver" value="oracle.jdbc.OracleDriver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="databaseType" value="oracle"/>
<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>
 
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
4. In the Workspace section of the code, change the code so that it matches your database installation, as in the example below.
Make sure that the <param value="url" value="jdbc:oracle:thin:@localhost:1521:/XE"/> parameter indicates the correct database name.
<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
<param name="url" value="jdbc:oracle:thin:@localhost:1521/XE"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schemaObjectPrefix" value="fs_ws_"/>
<param name="tablespace" value="pentaho_tablespace"/>
</FileSystem>
 
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
The tablespace name must be changed to pentaho_tablespace
5. In the Persistence Manager section of the code, change the code so that it matches your database installation, as in the example below.
Make sure that the <param value="url" value="jdbc:oracle:thin:@localhost:1521/XE"/> parameter indicates the correct database name.
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
<param name="url" value="jdbc:oracle:thin:@localhost:1521/XE"/>
<param name="driver" value="oracle.jdbc.OracleDriver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="oracle"/>
<param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
<param name="tablespace" value="pentaho_tablespace"/>
</PersistenceManager>
 
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
The tablespace name must be changed to pentaho_tablespace.
6. In the Versioning section of the code, change the code so that it matches your database installation, as in the example below.
Make sure that the <param value="url" value="jdbc:oracle:thin:@localhost:1521/XE"/> parameter indicates the correct database name.
<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
<param name="url" value="jdbc:oracle:thin:@localhost:1521/XE"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schemaObjectPrefix" value="fs_ver_"/>
<param name="tablespace" value="pentaho_tablespace"/>
</FileSystem>
 
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
The tablespace name must be changed to pentaho_tablespace
7. In the Persistence Manager section of the code that is near the end of the file, change the code so that it matches your database installation, as in the example below.
Make sure that the <param value="url" value="jdbc:oracle:thin:@localhost:1521/XE"/> parameter indicates the correct database name.
You may need to modify the parameter if your database has a different name than orcl.
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
<param name="url" value="jdbc:oracle:thin:@localhost:1521/XE"/>
<param name="driver" value="oracle.jdbc.OracleDriver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="oracle"/>
<param name="schemaObjectPrefix" value="pm_ver_"/>
<param name="tablespace" value="pentaho_tablespace"/>
</PersistenceManager>
 
If you changed your password when you initialized the database during the Prepare Environment step, or if your database is on a different port, edit the url and password parameters accordingly.
The tablespace name must be changed to pentaho_tablespace
8. Stop Orchestra.
9. Delete <orchestra_home>/pentaho-solutions/system/jackrabbit/repository/workspaces.
10. Start Orchestra.

JBoss Related Configuration, MS SQL

Go to JBoss Admin console (http://localhost:9990, username: admin, password: ulan) and create/edit auditpool, hibpool and quartzpool data sources with mssql configurations with the following parameters.
Remove any pools which are configured for PostgreSQL.
 
Driver:
sqlserverDriver
Driver Class:
com.microsoft.sqlserver.jdbc-SQLServerDriver
• For quartzpool:
 
JNDI
java:/jdbc/Quartz
Connection URL:
jdbc:sqlserver://localhost:1433;databaseName=quartz
Username:
pentaho_user
Password:
password (if you haven’t changed it when executing bi-quartz-mssql.sql)
• For auditpool:
 
JNDI
java:/jdbc/Audit
Connection URL:
jdbc:sqlserver://localhost:1433;databaseName=hibernate
Username:
hibuser
Password:
password (if you haven’t changed it when executing bi-jcr-mssql.sql)
• For hibpool:
 
JNDI
java:/jdbc/Hibernate
Connection URL:
jdbc:sqlserver://localhost:1433;databaseName=hibernate
Username:
hibuser
Password:
password (if you haven’t changed it when executing bi-repository-mssql.sql)
When this has been completed, the QP service needs to be started/restarted.

JBoss Related Configuration, Oracle

Go to JBoss Admin console (http://localhost:9990, username: admin, password: ulan) and create/edit auditpool, hibpool and quartzpool data sources with oracle configurations with the following parameters.
Remove any pools which are configured for PostgreSQL.
 
Driver:
oracleDriver
Driver Class:
oracle.jdbc.driver.OracleDriver
• For quartzpool:
 
JNDI
java:/jdbc/Quartz
Connection URL:
jdbc:oracle:thin:@127.0.0.1:1521:orcl (must of course match the selected Oracle installation).
Username:
pentaho_user
Password:
password (if you haven’t changed it when executing bi-quartz-ora.sql)
• For auditpool:
 
JNDI
java:/jdbc/Audit
Connection URL:
jdbc:oracle:thin:@127.0.0.1:1521:orcl (must of course match the selected Oracle installation)
Username:
hibuser
Password:
password (if you haven’t changed it when executing bi-jcr-ora.sql)
• For hibpool:
 
JNDI
java:/jdbc/Hibernate
Connection URL:
jdbc:oracle:thin:@127.0.0.1:1521:orcl (must of course match the selected Oracle installation)
Username:
hibuser
Password:
password (if you haven’t changed it when executing bi-repository-ora.sql)
When this has been completed, the QP service needs to be started/restarted.

Start the Orchestra Business Intelligence Upgrade

Now, you are ready to upgrade Orchestra Central, according to the instructions in “Orchestra Central” .
For more information, please see “Upgrade Wizard and Properties File” .
After the upgrade, you need to edit the QMATIC data source in the following way:
a) Select the QMATIC data source and click the cog wheel icon, , then Edit. Existing parameters of the QMATIC data source will be shown.
b) Click on the plus icon, , and add the following new parameter:
Name: DynamicSchemaProcessor
Value: mondrian.i18n.LocalizingDynamicSchemaProcessor