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
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
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.
Find the <bean id="shiroClient"> element in the <install_dir>/pentaho-solutions/system/apllicationContext-pentaho-securityshiro.xml file and make sure that the bold "name" property 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="securityServiceJndi" value="java:global/ qSystem/qp-central-core-ejb/SecurityServiceBean"/>
</bean>
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 <install_dir>/pentaho-solutions/system/quartz/quartz.properties file:
org.quartz.scheduler.instanceId = AUTO
org.quartz.jobStore.isClustered = true
Add below property key and value just after the org.quartz.jobStore.isClustered = true line:
org.quartz.jobStore.clusterCheckinInterval = 20000
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:8080/businessintelligence route=node1
# Node2
BalancerMember http://node2-ip:8080/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, restart 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.
