Simple Cube with Sakila database

Mondrian‎ > ‎

Contents

1 Introduction
2 Download and Installation
3 Sakila Scheme
4 Create Mondrian Cube's XML file
5 Create a JSP's query file
6 Testing the result
7 Downloadable resources
8 Related Articles

Introduction

Sakila is a movie rental sample database provided by MySQL. This database is very suitable to demonstrate the use of Pentaho's tools in various way.

For this purpose, I'll write a series of articles showing the use of Sakila with Mondrian and Kettle. And to begin with, this first article will show us how to get the sample database, creating a very basic cube definition and query it - all without altering the underlying data.

Download and Installation

Get Sakila sql script from http://dev.mysql.com/doc/#sampledb.
For installation, follow instructions from http://dev.mysql.com/doc/sakila/en/sakila.html#sakila-installation.

Sakila Scheme

This is Sakila scheme taken from MySQL's documentation. Click on the image to see the larger complete view.

Create Mondrian Cube's XML file

For this sample, I will design my Sakila's cube as follows :

1 cube : Rental
1 hierarchical dimension : Customers with All Customers, Active, and Name levels
1 measure : Rental Counts

From this definition, I specify all related elements into XML definition file. Here I name the file Sakila.xml and put it into [MONDRIAN]/WEB-INF/queries folder.

Sakila.xml

<?xml version="1.0"?>
<Schema name="Sakila">
<Cube name="Rental" defaultMeasure="Rental Counts">
<Table name="rental" />

<Dimension name="Customers" foreignKey="customer_id">
    <Hierarchy hasAll="true" allMemberName="All Customers" primaryKey="customer_id">
      <Table name="customer" />
      <Level name="Active" column="active" uniqueMembers="false">
       <NameExpression>
          <SQL dialect="mysql">
(case when `customer`.`active` = 0 then 'Not Active' else 'Active' end)
          </SQL>
       </NameExpression>
      </Level>
      <Level name="Name" column="customer_id" uniqueMembers="true">
       <KeyExpression>
          <SQL dialect="mysql">
CONCAT(`customer`.`first_name`, ' (', `customer`.`email`, ')')
          </SQL>
          <SQL dialect="generic">
email
          </SQL>
       </KeyExpression>
      </Level>
    </Hierarchy>
</Dimension>

<Measure name="Rental Counts" column="rental_id" aggregator="count"
      formatString="#,###"/>
</Cube>
</Schema>

Create a JSP's query file

Now, create a rentalquery.jsp file in the same folder as Sakila.xml.

rentalquery.jsp

<%@ 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/sakila?user=root&password="
   catalogUri="/WEB-INF/queries/Sakila.xml">
select
{[Measures].[Rental Counts]} on columns,
{[Customers].[All Customers]} ON rows
from Rental
</jp:mondrianQuery>

<c:set var="title01" scope="session">Sakila Rental's Cube</c:set>

Replace text in red color with your own database setting.

Testing the result

Make sure your Mondrian and MySQL database server is running - assuming the Mondrian's address url is http://localhost:8080/mondrian
Open your browser
Navigate to http://localhost:8080/mondrian/testpage.jsp?query=rentalquery
If all running fine, you will see the slice and dice of JPivot's view. A view that may look like figure below.