Using Mondrian

Using mondrian with Jpivot in a Java EE application

Versions used
Mondrian 3.2.1.13885
JPivot 1.8

Required files

• Deploy the mondrian war
• Copy the folders webapps\mondrian\jpivot and webapps\mondrian\wcf to the root of your application e.g. webapps\myapp
• Copy the folders webapps\mondrian\web-inf\jpivot and webapps\mondrian\web-inf\wcf to the web-inf folder of your application e.g. webapps\myapp\web-inf
• Copy the libraries in the mondrian\web-inf\lib folder to your applications web-inf\lib folder
• Copy the file mondrian\web-inf\mondrian.properties to the myapp\web-inf\classes folder
• Edit this mondrian.properties file to have contents like the following

# Allow the use of aggregates
mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true
mondrian.native.topcount.enable=true
mondrian.native.filter.enable=true

# result set limit
mondrian.result.limit=50000

# format sql if logging is configured to output sql or mdx
mondrian.rolap.generate.formatted.sql=true

# don't automatically load any drivers
mondrian.jdbcDrivers=

• Create a file log4j.xml in the myapps\web-inf\classes folder with contents like the following

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">


  
    
    
      
    
  
  

  
        
 

   
        
 

   
        
 

 



 



  
    
    
  
  

• Create a file named userconfig.xml in myapps\web-inf\jpivot\print with the following contents

<!-- empty configuration file to avoid error being logged because file is missing -->

web.xml modifications

• Copy and adapt contents of mondrian\web-inf\web.xml to your application's web.xml file
• Files that will launch pivot tables should be contained in the JpivotController filter mapping e.g.

 
    JPivotController
    /user/showAnalysis.jsp
  

• You can change the location of the error and busy pages e.g.

 
    JPivotController
    com.tonbeller.wcf.controller.RequestFilter
    
      errorJSP      /user/jpivotError.jsp      URI of error page
    
    
      busyJSP      /user/jpivotBusy.jsp      This page is displayed if a the user clicks
        on a query before the previous query has finished
    
      

File displaying pivot table

• Use the mondrian\testpage.jsp file as a template for the file that displays pivot tables. Remove the <wcf:include add="" and="" for="" li="" logic="" mdx="" own="" query<="" section="" the="" your="">

if(request.getParameter("action")==null && request.getParameter("null")==null){
...
 %>

<jp:mondrianquery jdbcUrl="<%=databaseUrl%>" jdbcUser="<%=databaseUser%>" jdbcPassword="<%=databasePassword%>" catalogUri="<%=schemaFile%>">
<%=query%>

<% } %>
...



...

• If you want to have a title for the pivot table, store it as a session attribute and retrieve it when you want to display it.

Alternative for getting Required files

• Download the mondrian zip package from sourceforge. http://sourceforge.net/projects/mondrian/files/
• Extract the mondrian zip file to a temporary folder e.g. C:\temp
• Download the jpivot zip package from the jpivot website, jpivot.sourceforge.net
• Extract the jpivot zip file to a temporary folder e.g. C:\temp
• Copy all the jar files from the mondrian lib directory e.g. C:\temp\mondrian-3.2.0.13661\lib to the application's web-inf\lib directory
• Rename the jpivot war file c:\temp\jpivot-1.8.0\jpivot.war to jpivot.zip and unzip the file
• Copy the wcf folder to the application's root e.g. webapps\myapp
• Copy the jpivot folder to the application's root
• Copy the web-inf\wcf folder to the application's web-inf folder e.g. webapps\myapp\web-inf
• Copy the web-inf\jpivot folder to the application's web-inf folder
• Copy the files in the web-inf\lib folder to the application's web-inf\lib folder
• Add the contents of the jpivot web.xml file to your application's web.xml file

Printing

• To ensure is done correctly regardless of the path of the file displaying the pivot table change the wcf toolbar tags that do the printing to

Modifying pdf output header and footer

• Printing to pdf generates a pdf file with default header and footer text. To modify this, edit the file myapp\web-inf\jpivot\table\fo_mdxtable.xsl

Maintaining scroll position

• When you expand a dimension, the page is refreshed and the scroll position goes back to the top of the page. You then have to manually scroll to the element you just expanded. To maintain scroll position have the page that displays the pivot table to have code like the following

<jp:table id="table01" query="#{query01}">
...

Get the current mdx

• If you want to get the mdx query for the currently displayed pivot table view, add code to the file that displays the pivot table similar to the following

<%@ page import="com.tonbeller.jpivot.table.TableComponent,com.tonbeller.jpivot.olap.model.*,com.tonbeller.jpivot.tags.OlapModelProxy" %>
<%@ page import="com.tonbeller.jpivot.olap.query.MdxOlapModel" %>

<%
//get the current mdx
TableComponent table = (TableComponent) session.getAttribute("table01"); 
//assuming table has id of table01 e.g. <jp:table String mdx="";
if( table != null ) {
    OlapModel olapModel = table.getOlapModel();
    while( olapModel != null ) {
        if( olapModel instanceof OlapModelProxy ) {
            OlapModelProxy proxy = (OlapModelProxy) olapModel;
            olapModel = proxy.getDelegate();
        }
        if( olapModel instanceof OlapModelDecorator) {
            OlapModelDecorator decorator = (OlapModelDecorator) olapModel;
            olapModel = decorator.getDelegate();
        }
        if( olapModel instanceof MdxOlapModel) {
            MdxOlapModel model = (MdxOlapModel) olapModel;
            mdx = model.getCurrentMdx();
            olapModel = null;
        }
    }
}
%>

Clear the mondrian cache

• Mondrian uses a cache held in memory to make analysis faster. Every unique mondrian connection [connection string] has a cache. If the underlying database changes and you need to clear the cache, you can use a jsp page like the following

<%@ page contentType="text/html; charset=UTF-8" %>

<%
//clear all mondrian caches
java.util.Iterator<mondrian.rolap.rolapschema> schemaIterator =  mondrian.rolap.RolapSchema.getRolapSchemas();
while(schemaIterator.hasNext()){
    mondrian.rolap.RolapSchema schema = schemaIterator.next();
    mondrian.olap.CacheControl cacheControl = schema.getInternalConnection().getCacheControl(null);
        
    cacheControl.flushSchemaCache();  
}
  
%>

Errors and possible causes

• 404 - Invalid url
• No metadata for catalog - Misspelt catalog name. Names are case sensitive
• XMLA connection datasource not found - Misspelt datasource name. Names are case sensitive
• XMLA Discover unparse results error - Definition file in catalog section of datasources.xml does not exist. If change made to datasources.xml, app has to be redeployed for the changes to take effect

Sample project
The ART reporting tool, http://art.sourceforge.net, makes use of mondrian + jpivot for OLAP queries (pivot tables). It's source code can be used as a reference. It uses custom jpivot.jar, wcf.jar and tbutils-wcf.jar files.