Queued Drillthrough

Table of contents [showhide]

1 Why queued drillthrough?

2 Provider

2.1 why ActiveMQ

3 Configuration

4 Initialization

5 Implementation

6 QueuedDrillthrough process

7 How to configure JPivot to use CustomDrillthroughUI?

Why queued drillthrough?

Migrate from internal documentation

Provider

OpenI uses spring JMS features - see : [1] (http://static.springframework.org/spring/docs/2.0.x/reference/index.html), also good tutorial is available here : [2] (http://www.onjava.com/pub/a/onjava/2006/02/22/asynchronous-messaging-with-spring-jms.html)
OpenI uses ActiveMQ as an embeded JMS broker : [3] (http://www.activemq.org)

why ActiveMQ

Probably the best open source out of the box JMS implementaion.
Apache license
Now part of apache as 'Apache Incubator'
Compatible with spring
Can be embeded
Support VM transport mode. No tcp port issue
Zero config required in VM transoport mode

Configuration

Configuration for JMS listener and JMS Broker(server) are done in springapp_servlet.xml. ActiveMQ is used as embededed message broker in VM transport mode which allows clients to connect to each other inside the VM without the overhead of the network communication.

jndi :

 <bean id="jndiTemplate"       class="org.springframework.jndi.JndiTemplate">

     <property name="environment">

        <props>

               <prop key="java.naming.factory.initial">org.apache.activemq.jndi.ActiveMQInitialContextFactory</prop>

               <prop key="java.naming.provider.url">vm://localhost</prop>

        </props>

     </property>

  </bean>

JMS MessageFactory :

 <bean id="jmsFactory"  class="org.apache.activemq.pool.PooledConnectionFactory">

       <property name="connectionFactory">

        <bean class="org.apache.activemq.ActiveMQConnectionFactory">

               <property name="brokerURL"><value>vm://localhost</value></property>

        </bean>

       </property>

  </bean>

Spring JMS Template used to send the JMS messeges to the configured destination :

 <bean id="drillthroughQueueJmsTemplate" class="org.springframework.jms.core.JmsTemplate">

     <property name="connectionFactory">

         <ref local="jmsFactory" />

     </property>

     <property name="defaultDestination">

         <ref bean="drillthroughQueueDestination" />

     </property><property name="receiveTimeout">

          <value>30000</value>

     </property>

  </bean>

  <bean id="listPullQueueJmsTemplate" class="org.springframework.jms.core.JmsTemplate">

     <property name="connectionFactory">

         <ref local="jmsFactory" />

     </property>

     <property name="defaultDestination">

         <ref bean="listPullQueueDestination" />

     </property><property name="receiveTimeout">

          <value>30000</value>

     </property>

  </bean>

destinations for Drillthrough request and list pull request :

 <bean id="drillthroughQueueDestination" class="org.springframework.jndi.JndiObjectFactoryBean">

     <property name="jndiTemplate">

         <ref bean="jndiTemplate" />

     </property>

     <property name="jndiName">

         <value>dynamicQueues/drillthroughQueue</value>

     </property>

 </bean>

 <bean id="listPullQueueDestination" class="org.springframework.jndi.JndiObjectFactoryBean">

     <property name="jndiTemplate">

         <ref bean="jndiTemplate" />

     </property>

     <property name="jndiName">

         <value>dynamicQueues/listPullQueue</value>

     </property>

 </bean>

JMS listeners bean:

 <bean id="drillthroughMessageListener" class="org.openi.olap.drillthrough.DrillthroughMessageListener" >

      <property name="jmsTemplate" ref="listPullQueueJmsTemplate" />

 </bean>

<bean id="listPullMessageListener" class="org.openi.olap.drillthrough.ListPullMessageListener" />

Both listeners registration for destination drillthroughQueueDestination and listPullQueueDestination:

 <bean id="drillthroughMessageListenerContainer" class="org.springframework.jms.listener.DefaultMessageListenerContainer">

        <property name="concurrentConsumers" value="5" />

        <property name="connectionFactory" ref="jmsFactory" />

        <property name="destination" ref="drillthroughQueueDestination" />

        <property name="messageListener" ref="drillthroughMessageListener" />

 </bean>

 <bean id="listPullMessageListenerContainer" class="org.springframework.jms.listener.DefaultMessageListenerContainer">

        <property name="concurrentConsumers" value="5" />

        <property name="connectionFactory" ref="jmsFactory" />

        <property name="destination" ref="listPullQueueDestination" />

        <property name="messageListener" ref="listPullMessageListener" />

 </bean>

Initialization

JMS Broker-ActiveMQ, is automatically booted up, when JMS client tries to make connection with it – spring makes connection with the broker while initializing the listener container for message listener. Similarily, ActiveMQ will automatically shut down when all connected clients close their connection. This is the feature of ActiveMQ if using in VM transport mode which allows clients to connect to each other inside the VM without the overhead of the network communication. For TCP or other transport mode, ActiveMQ server requires to be started explicitly.

Destination Queues are automatically created by ActiveMQ. We do not need to configure it explictly, if we use prefix 'dynamicQueues/' for queue name. This is the feature of ActiveMQ.

Both listeners are configured in spring IoC, so both are managed by spring. Both listeners are automatically bound to the configured destination by spring.

Implementation

Drillthrough Handler : 'org.openi.olap.drillthrough.CustomDrillthroughUI' handles the drillthrough request. This class is responsibile for putting 'DrillthroughMessage' object in RequestContext, which is than processed by the AnalysisController if 'queuedDrillthrough' is enabled. This class does this when user clicks on drill icon in cell of the table. By default jpivot uses 'com.tonbeller.jpivot.table.DrillthorughUI' class. You must set 'org.openi.olap.drillthrough.CustomDrillthroughUI' in jpivot to use queued drillthrough. howto is here (http://wiki.openi.org/index.php/Queued_Drillthrough#How_to_configire_JPivot_to_use_CustomDrillthroughUI.3F).

JMS Message wrapper class:

'org.openi.olap.drillthrough.DrillthroughMessage': encapsulates drillthrough message which is send to the listener 'org.openi.olap.drillthrough.DrillthroughMessageListener'. It contains following properties :

drillthroughMdx : drillthrough MDX query
catalog : catalog name
uri : XMLA uri
user : user name required for basic authentication
password : user password required for basic authentication
outFile : output file name path to save drillthrough result
sqlQuery : RDBMS query to be executed to get the final result
dataSourceName : XMLA datasource name e.g 'Local Analysis Server'
jdbcDataSource : JDBC connection properties to execute RDBMS query

'org.openi.olap.drillthrough.ListPullMessage': encapsulates list pull message which is send to the listener 'org.openi.olap.drillthrough.ListPullMessageListener'. It contains following properties :

drillResultPath : drillthrow raw result file path
outputFilePath : output file name path to save drillthrough result
sqlQuery : RDBMS query to be executed to get the final result
userName : user name for tracing
drillthroughMdx : drillthrough MDX query for for tracing
jdbcDataSource : JDBC connection properties to execute RDBMS query

JMS Listeners :

'org.openi.olap.drillthrough.DrillthroughMessageListener': This class does following :

executes drillthrough MDX with the help of 'org.openi.olap.drillthrough.DrillthroughHelper' and saves raw result to the temp location.

contructs 'org.openi.olap.drillthrough.ListPullMessage' and sends to the 'org.openi.olap.drillthrough.ListPullMessageListener'
'org.openi.olap.drillthrough.ListPullMessageListener': This class does following :

parses raw soap result file generated by 'org.openi.olap.drillthrough.DrillthroughMessageListener'
if no sqlQuery is available, it saves parsed result into user's personal folder in tab delimited text format
if sqlQuery is available, it generates SQL query using parsed keys and executes to the database in chunk of 10K rows per request and saves list pull results into user's personal folder in tab delimited text format .

QueuedDrillthrough process

Queued Drillthrough feature can be enabled by setting boolean property 'queuedDrillthrough' of Application to true.

If queuedDrillthrough is enabled, custom drillthrough handler class 'org.openi.olap.drillthrough.CustomDrillthroughUI' creates the DrillthroughMessage instance, which encapsulates all the required parameters for drillthrough operation, including drillthrough MDX, and then, it sets the instance in requestcontext as an attribute 'drillthroughRequestMessage' for org.openi.web.controller.analysis.AnalysisController

org.openi.web.controller.analysis.AnalysisController sends the 'drillthroughRequestMessage' object, retrieved from request attribute, to the JMS queue with the help of Spring JMS template 'drillthroughQueueJmsTemplate'. The message goes to the JMS listener class 'org.openi.olap.drillthrough.DrillthroughMessageListener'. 'drillthroughQueueJmsTemplate' and 'org.openi.olap.drillthrough.DrillthroughMessageListener', both are configured for destination 'drillthroughQueueDestination' with queue name 'dynamicQueues/drillthroughQueue'.

'org.openi.olap.drillthrough.DrillthroughMessageListener' executes the drillthrough MDX with the help of DrillthroughHelper class and persists the raw result into the temp location 'TEMP_ROOT/openi/USER_ANALYSISNAME_YYYYMMDD_TIMEELAPSED'. It then creates 'org.openi.olap.drillthrough.ListPullMessage' instance and sends it to the JMS queue with the help of Spring JMS template 'listPullQueueJmsTemplate'. The message goes to the JMS listener class 'org.openi.olap.drillthrough.ListPullMessageListener'.'listPullQueueJmsTemplate' and 'org.openi.olap.drillthrough.ListPullMessageListener', both are configured for destination 'listPullQueueDestination' with queue name 'dynamicQueues/listPullQueue'.

'org.openi.olap.drillthrough.ListPullMessageListener' parses the raw drilltthrogh result into plain tab delimited text file, if no RDBMS query is available. If rdbms query is available, it contructs the SQL query with the help of retrived drillthough result keys and executes constructed SQL to the the database in chunk of 10000 rows per request(if the rows are more than 10K). Finally it saves the output to the users personal folder as a file 'ANALYSISNAME_YYYY_MM_DD_TIMEELAPSED.drillthrough'.

How to configure JPivot to use CustomDrillthroughUI?

checkout jpivot(jpivot, jpivot_repository and wcf)
build wcf jar
edit 'jpivot_root\src\java\com\tonbeller\jpivot\table\config.xml' and change the entry '<extension .... class="com.tonbeller.jpivot.table.navi.DrillThroughUI"/>' to '<extension... class="org.openi.olap.drillthrough.CustomDrillthroughUI'/>.
build jpivot
copy jpivot.war into openi's ext folder
build openi