Create a custom JPivot crosstab connected to the Mondrian OLAP server

In that lesson we will create a simple JPivot crosstab with custom design and data which will be connected to the Mondrian OLAP server.
A jsp technology with jpivot tags library will be used to create a crosstab accessible via a web browser. In order to complete this lesson, a basic java/jsp knowledge will be essential and it is highly recommended to read about JSP tags (JPivot and WCF).

The tag libraries description can be found here:

  • Jpivot JSP tags
  • WCF JSP tags


    The JPivot crosstab from our tutorial will be displayed and rendered by a jsp page named gg_crosstab1.jsp.

    In order to create and deploy such a page follow the instructions below:
  • Once the MDX connection to the relational database is configured correctly, it should be stored in a jsp file with the query. In our tutorial lesson it is /WEB-INF/queries/etltools.jsp.

      And the etltools.jsp file has the following contents: 

      <%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

<jp:mondrianQuery id="query01" jdbcDriver="oracle.jdbc.driver.OracleDriver"
	jdbcUrl="jdbc:oracle:thin:dwuser/dwpwd1@127.0.0.1:1521:xe" catalogUri="/WEB-INF/queries/EtlTools.xml">
select 	{[Measures].[Total Turnover], [Measures].[Total Weight]} ON columns,
  {[TIME].[All Time]} ON rows from EtlTools
</jp:mondrianQuery>

<c:set var="title01" scope="session">Test Etl-tools.info simple Query that uses Mondrian OLAP</c:set>

      The most important thing in that configuration file is the jp:mondrianQuery tag which defines and configures access to the Mondrian OLAP data source. It contains a simple MDX query which will determine which data will be fetched from Mondrian (in this example we will analyze the time dimension and two measures: turnover and weight).
      Please note that the file is referenced to the EtlTools.xml Mondrian Schema configuration file. It’s the same file which was created in the Pentaho Cube Designer tutorial lesson.


  • gg_crosstab1.jsp file.
      The code of the jsp file which will display the JPivot crosstab is shown below (most significant lines are marked red):
      The code of the jsp file which will display the JPivot crosstab is shown below (most significant lines are marked red)
    • The first lines of the gg_crosstab1.jsp file contain jsp tags for the wcf, jpivot and core java libraries and a standard html header and body definition.

    • The line below contains a link to a jpivot css definition file. The crosstab design can be customized by editing the mdxtable.css file.
      <link rel="stylesheet" type="text/css" href="jpivot/table/mdxtable.css">

    • In the lines attached below we can find a Mondrian Query definition inclusion and a check if the file was included successfully. If not, the browser will be redirected to an index.jsp homepage.

      According to the WCF specification,the include tag acts in the following way:
      Includes a JSP page if a certain HTTP parameter is present. The name of the file is prefix + paremeter + suffix. For example, if prefix="/WEB-INF/mypages/", httpParam="page" and suffix=".jsp", then the URL ?page=page20 will include the file /WEB-INF/mypages/page20.jsp. If the parameter page is not present in the HTTP request, no page will be included. 
      <%-- query definition --%>
<wcf:include id="include01" httpParam="query" prefix="/WEB-INF/queries/" suffix=".jsp"/>

<c:if test="${query01 == null}">
  <jsp:forward page="/index.jsp"/>
</c:if>
    • The table is initialized with the following command (it gets instantiated in the memory, however will not be visible on the screen until it’s rendered): 
      <jp:table id="table01" query="#{query01}"/>
    • And The table is rendered and shown on the screen with the following command: 
      <wcf:render ref="table01" xslUri="/WEB-INF/jpivot/table/mdxtable.xsl" xslCache="true"/>
  • Note that the jsp crosstab file in order to be processed correctly, it must be added to a JPivotController filter in the WEB-INF/web.xml Apache Tomcat configuration file: 
      <filter-mapping>
    <filter-name>JPivotController</filter-name>
    <url-pattern>/gg_crosstab1.jsp</url-pattern>
  </filter-mapping>


  • The crosstab definition is complete and is ready to use and to be explored.
    It should be accessible from the following query (assuming that Tomcat runs on port 8000): http://127.0.0.1:8000/mondrian/gg_crosstab1.jsp?query=etltools


    A JPivot crosstab example with drill down:
    A JPivot crosstab example with drill down

    In the next tutorial lesson we will add more advanced features such as sorting, MDX editing, slice & dice, charts, exporting and many more useful crosstab options.


    <-- 3. Define cube with Pentaho Cube DesignerPentaho Tutorial5. JPivot Navigator -->