Simple Cube with Sakila Database

Mondrian‎ > ‎

Contents

1 Introduction
2 Prerequisites
3 Sakila Scheme
4 Mondrian Cube Definition (XML)
5 JSP query file
6 Result
7 Download
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.

In this article I will show us how to get the sample database, creating a very basic cube definition and query it - all without altering the underlying data.

Prerequisites

Get Sakila database 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.
A running MySQL databaes server
A running Mondrian web application

Sakila Scheme

This is Sakila scheme - taken from MySQL's documentation. Click on it to see a larger view.

Mondrian Cube Definition (XML)

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

Based on the design, I specify all related elements into an XML definition file. I named the file Sakila.xml, put it under [MONDRIAN WEB APP ]/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>

JSP query file

Now, we need to create a Java Server Pages (jps) file to query the cube. Named it rentalquery.jsp and put in under 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.

Result

Make sure your Mondrian and MySQL database server is running - assuming the Mondrian's address url is http://localhost:8080/mondrian
Navigate to http://localhost:8080/mondrian/testpage.jsp?query=rentalquery
Wait for some times.
If all running fine, you will see the slice and dice of JPivot's view (picture).
Done

Download

You can download the XML definition and query file described above from the attachments at the end of the article.

If you encounter any difficulties during this setup, you can ask questions in our user group : http://groups.google.com/group/phi-mondrian-olap.

Sakila.xml

(1k)

Feris Thia,

Aug 27, 2008, 5:37 AM

v.1

rentalquery.jsp

(1k)

Feris Thia,

Aug 27, 2008, 5:37 AM

v.1

Comments