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 :-)

Free OLAP cube icon

Okay this is a bit off-topic compared to my normal posts, but here goes.

When I do websites or GUI design I usually look out for free/open source icon packages such as Tango or Crystal. I have been looking for a nice-looking icon to represent an OLAP cube, preferably in a style and coloring similar to the Tango icon set. Sorry to say, I didn't find any, so I went ahead and spent some hours creating a new icon on my own. Here's the result:


And a plain version without the sum/count text elements (good if you need to resize it to very small sizes):


I'm giving this away under a beerware license, so if you want it, it's yours.