Mondrian‎ > ‎

Simple Cube with Sakila Database

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

  1. 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.
  2. A running MySQL databaes server
  3. 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

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.

Related Articles

ċ
Sakila.xml
(1k)
Feris Thia,
Aug 27, 2008, 5:37 AM
ċ
rentalquery.jsp
(1k)
Feris Thia,
Aug 27, 2008, 5:37 AM
Comments