HA setup : Setting up Business Intelligence in a clustered environment
  

Setting up Business Intelligence in a clustered environment

In this guide, two instances of Business Intelligence servers will be considered. One Business Intelligence instance will be installed along with Orchestra Central.

Installation

In this setup, DB Server will hold all Orchestra databases, including Stat and the three Business Intelligence databases.

Node 1

Install Orchestra with or without Business Intelligence in a server machine. For this instruction, Orchestra Central, Stat and Business Intelligence are installed in a single server machine. Ideally, Business Intelligence should be installed in a different server.

Node 2

Install a Business Intelligence instance in a server.

Configuration

Note that you need to make sure that all of your application nodes are set up with identical configurations.

Orchestra Central and Queue Agent Database Changes

Update the following table records of qp_central and qp_agent:
UPDATE qp_central.applications SET enabled = 1, url = 'http://loadbalancer:port/businessintelligence/' WHERE id='bi'
 
UPDATE qp_central.application_modules SET enabled = 1 WHERE id='bi'
 
UPDATE qp_agent.applications SET enabled = 1, url = ' http://loadbalancer:port/businessintelligence/' WHERE id='bi'
 
UPDATE qp_agent.application_modules SET enabled = 1 WHERE id='bi'
 

Business Intelligence server configurations

1. On each Business Intelligence instance, update the central.orchestra.url property of the <install_dir>/pentaho-solutions/system/security.properties file with http://loadbalancer:port.
In case there is an internal load balancer and Orchestra needs to be accessed internally behind a public load balancer, you also need to configure the central.orchestra.internal.url property with http://internalloadbalancer:port
2. Find the <bean id="shiroClient"> element in the <install_dir>/pentaho-solutions/system/applicationContext-pentaho-security-shiro.xml file. Make sure that either the ${security.central.orchestra.url} property or the ${security.central.orchestra.internal.url} property (depending on what is set in security.properties) is uncommented.
<bean id="shiroClient"
class="com.qmatic.pentaho.extensions.security.QPShiroClientBean">
<property name="evictTimeMillis" value="7000"/>
<property name="url" value="${security.central.orchestra.url}" />
<!--
<property name="url" value="${security.central.orchestra.internal.url}" />
-->
<property name="securityServiceJndi" value="java:global/ qSystem/qp-central-core-ejb/SecurityServiceBean"/>
</bean>
 
3. Add statDB data source to <install_dir>/app/wildfly-11.0.0.Final/standalone/configuration/standalone-full.xml
<datasource jndi-name="java:/jdbc/statDB" pool-name="stat_datasource"
enabled="true" use-ccm="false">
 
<connection-url>jdbc:postgresql://HOSTNAME:PORT/statdb
</connection-url>
...
...
</datasource>

Initialize and configure repository

There are a few steps to configure each Business Intelligence instance, before you move on to configure the jackrabbit journal.
Initialize your database depending on your preference, PostgreSQL, MS SQL Server, or Oracle. All Business Intelligence instances will point to these databases (hibernate, jackrabbit, and quartz).

Configure the data connections of Business Intelligence Repository

<install_dir>/pentaho-solutions/system/jackrabbit/repository.xml

Update repository database specific "hibernate.cfg.xml" with database server IP

MS SQL Server: <install_dir>/pentaho-solutions/system/hibernate/sqlserver.hibernate.cfg.xml
Oracle: <install_dir>/pentaho-solutions/system/hibernate \oracle10g.hibernate.cfg.xml
PostgreSQL: <install_dir>/pentaho-solutions/system/hibernate /postgresql.hibernate.cfg.xml

Configure JDBC and JNDI connections of PostgreSQL, MS SQL Server, and Oracle with database server IP

<install_dir>/app/wildfly-11.0.0.Final/standalone/configuration/standalone-full.xml
Locate <install_dir>/system/pentaho-solutions/system/jackrabbit/repository directory and remove all files and folders from the final repository folder.
Locate <install_dir>/pentaho-solutions/system/jackrabbit/repository directory and remove all files and folders from the workspaces folder.

Configure Jackrabbit Journal

Make sure that each node has a unique ID.
Locate <!-- Run with a cluster journal --> entry in the <install_dir>/pentaho-solutions/system/jackrabbit/repository.xml file.
Add the following journal configuration corresponding to your repository database.

PostgreSQL

<Cluster id="Unique_ID">
<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
<param name="revision" value="${rep.home}/revision.log"/>
<param name="url" value="jdbc:postgresql://HOSTNAME:PORT/jackrabbit"/>
<param name="driver" value="org.postgresql.Driver"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="databaseType" value="postgresql"/>
<param name="janitorEnabled" value="true"/>
<param name="janitorSleep" value="86400"/>
<param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>
</Cluster>
 

MS SQL Server

<Cluster id="Unique_ID">
<Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
<param name="revision" value="${rep.home}/revision.log"/>
<param name="url" value="jdbc:sqlserver://HOSTNAME:PORT;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="janitorEnabled" value="true"/>
<param name="janitorSleep" value="86400"/>
<param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>
</Cluster>
 

Oracle

<Cluster id="Unique_ID">
<Journal class="org.apache.jackrabbit.core.journal.OracleDatabaseJournal">
<param name="revision" value="${rep.home}/revision.log" />
<param name="url" value="jdbc:oracle:thin://HOSTNAME:PORT/di_jackrabbit"/>
<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="janitorEnabled" value="true"/>
<param name="janitorSleep" value="86400"/>
<param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>
</Cluster>
 

Configure Quartz

Set below property key values in the <bi install directory> \server\pentaho-server\pentaho-solutions\system\quartz\quartz.properties file:
org.quartz.scheduler.instanceId = AUTO
org.quartz.jobStore.isClustered = true
Add below property keys and value after the org.quartz.jobStore.isClustered = true line:
org.quartz.jobStore.clusterCheckinInterval = 20000
and
org.quartz.jobStore.selectWithLockSQL = SELECT * FROM {0}LOCKS WHERE LOCK_NAME=?

Post-install configuration options for session sharing

Update the <install_dir>/conf/shiro.ini file. Locate the section that looks like this:
/qsystem/rest/security/account/** = noSessionCreation, ipFilter[127.0.0.1,0:0:0:0:0:0:0:1]
 
Inside the brackets, add the load balancer server IP address that the Business Intelligence server will use when communicationg with Orchestra. For networks that use both IPv4 and IPv6, you should add both addresses.
Example:
/qsystem/rest/security/account/** = noSessionCreation, ipFilter[127.0.0.1,0:0:0:0:0:0:0:1,loadbalancer-IPv4,loadbalancer-IPv6]
 

Reverse-Proxy configuration (Apache24)

Introduce the following changes to the <install_dir>/Apache24/conf/Httpd.conf file:
LoadModule lbmethod_bybusyness_module modules/mod_lbmethod_bybusyness.so
#LoadModule lbmethod_byrequests_module modules/mod_lbmethod_byrequests.so
#LoadModule lbmethod_bytraffic_module modules/mod_lbmethod_bytraffic.so
#LoadModule lbmethod_heartbeat_module modules/mod_lbmethod_heartbeat.so
 
Uncomment one of the above with preference to load balance method.
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_ajp_module modules/mod_proxy_ajp.so
LoadModule proxy_http_module modules/mod_proxy_http.so
LoadModule proxy_balancer_module modules/mod_proxy_balancer.so
LoadModule headers_module modules/mod_headers.so
 
Uncomment above modules.
Virtualhost configuration:
<VirtualHost *:*>
ProxyRequests off
ServerName localhost
Header add Set-Cookie "ROUTEID=.%{BALANCER_WORKER_ROUTE}e; path=/" env=BALANCER_ROUTE_CHANGED
 
<Proxy balancer://businessintelligence>
# Node1
BalancerMember http://node1-ip:8081/businessintelligence route=node1
# Node2
BalancerMember http://node2-ip:8081/businessintelligence route=node2
ProxySet lbmethod=bybusyness
</Proxy>
 
#monitor loadbalancer
<Location /balancer-manager>
SetHandler balancer-manager
</Location>
 
ProxyPass "/businessintelligence" "balancer://businessintelligence" stickysession=ROUTEID|SSOcookie|JSESSIONID scolonpathdelim=On
ProxyPassReverse "/businessintelligence" "balancer://businessintelligence" stickysession=ROUTEID|SSOcookie|JSESSIONID
 
#All other request URL’s patterns other than “/businessintelligence” will be directed to Orchestra server machine (in this case it is node1)
ProxyPass "/" "http://node1-ip:8080/"
ProxyPassReverse "/" "http://node1-ip:8080/"
</VirtualHost>
 

Summary

After completing the above configuration steps, stop the bi server and delete content in following folders:
<bi install directory> \server\pentaho-server\pentaho-solutions\system\jackrabbit\repository
<bi install directory> \server\pentaho-server\tomcat\temp
<bi install directory> \server\pentaho-server\tomcat\workp
Now start every Business Intelligence instance. Business Intelligence should be able to access seamlessly via the Orchestra URL.
Make sure to add Business Intelligence data sources via the Business Intelligence console: statDB for canned reports; QMATIC and LIVE schemas for Analysis reports.