Setting up Mondrian for JNDI DataSources, XML/A and custom CSS styles

The other day I decided that I wanted to set up mondrian as an adhoc analysis package for Lund&Bendsens intranet application, "Yacs". I didn't want to install a large application like Pentaho for just this specific need - rather I wanted to deploy just a simple cube schema, reuse the Java EE datasource definition that the intranet-application was already using and apply some basic styling to comply with the corporate profile. Doing these steps showed a lot more complex than I first imagined, primarily because I think the examples of the standalone mondrian distribution are overly complex and poorly designed, incapsulation-wise. Here is a list of steps I recommend doing to set up Mondrian "the right way":

1. Deploy your Java EE datasource in a container/database-specific way. If you JBoss and MySQL like I do, here's an example datasource descriptor, place it in the deploy-folder with a filename like "mydatasource.xml" (bold parts should be replaced with your specific configuration):
   

   <datasources>
      <local-tx-datasource>
        <jndi-name>MyDataSource</jndi-name>
        <connection-url>jdbc:mysql://localhost/mydatabase</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>username</user-name>
        <password>password</password>
        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <valid-connection-checker-class-name>
          org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker
        </valid-connection-checker-class-name>
        <metadata>
          <type-mapping>mySQL</type-mapping>
        </metadata>
      </local-tx-datasource>
   </datasources>

2. Unzip the mondrian.war archive so you can edit the application.
3. Add a container specific mapping of the DataSource to this application. In JBoss this is done by placing a file called "jboss-web.xml" in the WEB-INF folder with this content:
   

   <jboss-web>
      <resource-ref>
        <res-ref-name>MyDataSource</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <jndi-name>java:/MyDataSource</jndi-name>
      </resource-ref>
   </jboss-web>

4. Now edit the WEB-INF/web.xml file and add the following entry inside the <web-app> element:

   <resource-ref>
      <res-ref-name>MyDataSource</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
   </resource-ref>

   </web-app>
5. Also change the mapping of the JPivot filter so it goes like this:
   

   <filter-mapping>
      <filter-name>JPivotController</filter-name>
      <url-pattern>/*</url-pattern>
   </filter-mapping>

6. Create a schema-file and save it under WEB-INF/mycatalog.xml. I won't give instructions as to writing schemas - Mondrians documentation cover this quite well.
   
7. If you want to enable XML/A support, use this as a template for your WEB-INF/datasources.xml file (notice here that we use the application-local JNDI string here (including java:comp/env/...)):
   

   <datasources>
      <datasource>
        <datasourcename>Provider=Mondrian;DataSource=MyDataSource;</datasourcename>
        <datasourcedescription>My example datasource</datasourcedescription>
        <url>http://localhost:8888/mondrian/xmla</url>
        <datasourceinfo>Provider=mondrian;DataSource=java:comp/env/MyDataSource;
        </datasourceinfo>
        <providername>Mondrian</providername>
        <providertype>MDP</providertype>
        <authenticationmode>Unauthenticated</authenticationmode>
        <catalogs>
          <catalog name="MyCatalog"
            <definition>/WEB-INF/mycatalog.xml</definition>
          </catalog>
        </catalogs>
      </datasource>
   </datasources>

8. The views on the cube that you now want must be created as individual JSP pages. One of the things that are really lacking in the mondrian bundle is reasonable JSP pages with less complexity and reasonable reuse of datasources. Here's how I build mine (you can more or less put this stuff into the testpage.jsp page and then you're not dependent on all the jsp include stuff) - notice now that the datasource-reference entered here is the just the last, ie. the reference in web.xml:
   

   <% if (session.getAttribute("query01") == null) { %>
     <jp:mondrianquery id="query01" datasource="MyDataSource" cataloguri="/WEB-INF/mydatasource.xml">
      <!-- Initial MDX query goes here -->
     </jp:mondrianquery>
   <% } %>

Horray! Now you've got connection pooling, sharing and all the other cool stuff that Java EE DataSources provide. Next step: Add styling. First: Remove all the stylesheets that come with mondrian. You don't need them because it's actually quite a lot easier to add your own than to try and modify the existing ones. Here's the result of 10 minutes of styling:



The important CSS id's and classes are:

• To control styling of the pivot table and its cells it's important that you add <div> around this tag in the JSP:

  <wcf:render ref="query01" xslUri="/WEB-INF/jpivot/table/mdxtable.xsl" xslCache="true" />

  You can then use your div's id-attribute to target cells, headers etc. in your stylesheet.
  


• The class .heading-heading: Used for headings of headings, ie. the top-level blue cells in the screenshot above.


• The classes .column-heading-span, .column-heading-even, .column-heading-odd: Used for column headers, ie. the gray cells above the pivot table content.


• The classes .column-heading-span, .column-heading-even, .column-heading-odd: Used for column headers, ie. the gray cells above the pivot table content.


• The classes .row-heading-span, .row-heading-even, .row-heading-odd: Used for row headers, ie. the gray cells to the left of the pivot table content.


• The classes .cell-even and .cell-odd: Used for the cells on even and odd rows.

I encourage the mondrian crew to clean up the reference application, but I'm guessing they are using the messy configuration to convince people to switch to a full Pentaho deployment :-)

JPA and the N+1 select problem

Warning to readers: This blog entry contains references to articles only available in Danish. So if you keep on reading be prepared to weep if you want to follow my suggestions ;-)

Lately I've been working hard on Lund&Bendsens intranet and processes involved around it. I've been using JBoss Seam for the most part and overall I'm quite thrilled about this choice of web framework. One of the cool parts about Seam is the way it integrates with Java Persistence API (JPA)/Hibernate and handles my persistence context even when I'm rendering the views for the intranet.

At the same time when I have been developing the intranet features in Seam and JPA, my colleague Kenn Sano wrote an excellent article about the N+1 Select Problem in JPA. Here's what it all comes down to (In Danish):

"Man kan [med JPA] "vandre rundt" i en objektgraf og på magisk vis hentes data, som stilles til rådighed i takt med, at vi traverserer - dvs. objekters tilstand indlæses fra databasen alt imens vi bevæger os rundt i objektgrafen. Hvis man ikke er opmærksom på, hvordan JPA fungerer, kan det resultere i mange SQL-kald mod databasen, hvilket kan have stor negativ indvirkning på performance."

I was doing this plentifully in Seam. When presenting a list of courses or a list of students, Seam lets me easily traverse the items in the list by using EL-expressions such as "#{course.location.address}" which involved several N+1 performance penalties. For instance on the list of all planned courses, including their locations, enrolled students etc. I observed a whole of N*4+1 query penalty. So there's no doubt you need to be aware of the impact of your querying strategy.

Note: I'm not blaming JBoss Seam for this behavior ... Seam makes everything a whole lot easier and when everything is easy you just tend to forget to think yourself ;-) Anyways - go read the article if you're interested in JPA and understand Danish.