Package org.openi.olap.drillthrough

See:
          Description

Class Summary
CustomDrillthroughUI Custom drillthrough ui handler class to support queued drillthrough.
DrillthroughHelper executes DT mdx and parses the results in CSV format and writes it to outputstream
DrillthroughMessage  
DrillthroughMessageListener JMS listener class for drillthrough request
ListPullMessage  
ListPullMessageListener  
ListPullTest Drillthrough test class
 

Package org.openi.olap.drillthrough Description

Queued Drillthrough

Table of contents [showhide]

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