Installation

Just execute following from your application directory:

grails install-plugin mondrian 0.1

grails install-plugin /path/to/downloaded/plugin/distr

The latest version of the plugin is 0.1.

This plugin works with Grails 0.5.6+

See JIRA for known issues.

What is Mondrian?

Mondrian is an open source Relational OnLine Analytical Processing (OLAP) engine that enables self-service analysis and reporting for users. Data is retrieved from a generic star-schema database so that any variable can be compared by any variable.

Database structure

Databases are generally structured for transactional processing (OLTP) in that there are a number of tables each representing a domain object (aka 3rd Normal Form) so that data integrity is maintained and data is only held in one place (e.g. an address is not repeated if two people live there). Database queries tend to be atomic, e.g. updating a single customer's record.

Analysis and reporting requires the complete opposite as the data is static and queries tend to span large quantities of data, e.g. tell me the average age of customers split by country. The OLAP approach is to flatten the data into a single central 'fact' table linking to multiple 'outrigger' tables containing lookup data like 'description' for a country code and hierarchical grouping information. This database schema is often described as a 'star-schema'.

Running

The plugin doesn't strictly use grails controllers, however we created a controller to simply display a list of available mondrian queries (and their appropriate URLs) but hang on, i'm getting ahead of myself, let me start again.

The plugin installs, amongst other things, 3 jsps into the web-app folder of your grails application, these are testpage.jsp, busy.jsp and error.jsp. The testpage.jsp is the interesting one as it implements a mondrian jpivot table displaying the mdx query of your choice.

In it's freshly installed guise, the testpage.jsp takes the querystring value of 'query' and looks for a jsp page in the /WEB-INF/queries folder, if none is found, the testpage will forward you to the index.gsp of the project.

For example, the URLhttp://localhost:8080/yourgrailsapp/testpage.jsp?query=foodmart

Would result in the testpage.jsp looking for

yourgrailsapp/web-app/WEB-INF/queries/foodmart.jsp

If the queries folder doesn't exist you'll need to create it (for this example at least).

Now assuming you have an appropriate database setup for analysis you can create your query page and accompanying mondrian schema xml. Your query jsp might look a bit like this:

<%@ 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="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/foodmart?user=username&password=password"
catalogUri="/WEB-INF/queries/foodmart.xml">
    select
 {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]}
on columns,
{([Promotion Media].[All Media], [Product].[All Products])}
ON rows
    from Sales
    where ([Time].[1997])
 </jp:mondrianQuery>
 <c:set var="title01" scope="session">FoodMart using Mondrian OLAP</c:set>
\\

Note the jdbc connection to a mysql database and the location of the appropriate mondrian schema file in the mondrianQuery. The schema file would be the same as any normal Mondrian schema file with dimensions, hierarchies etc corresponding to your database, like so:

<?xml version="1.0"?>
 <Schema name="FoodMart">
    <Dimension name="Product">
        <Hierarchy hasAll="true" primaryKey="product_id"
primaryKeyTable="product">
            <Join leftKey="product_class_id" rightKey="product_class_id">
                <Table name="product"/>
                <Table name="product_class"/>
            </Join>
            <Level name="Product Family" table="product_class"
column="product_family" uniqueMembers="true"/>
            <Level name="Product Category" table="product_class"
column="product_category" uniqueMembers="false"/>
            <Level name="Product Name" table="product"
column="product_name" uniqueMembers="true"/>
        </Hierarchy>
    </Dimension>

...etc etc

Once you have your schema and query pages set up you should be able to navigate to the testpage and see the jpivot table, drill down, etc.