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.
When Upgrading from Orchestra 5.4
When upgrading from Orchestra 5.4 to a later version, there are a few things that you need to consider:
• 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 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.
• 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
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_tablespace5. 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_tablespace7. 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_tablespace8. 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” .
