Pentaho Support at PHI-Integration
 
Search Site

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.

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

  1. Get Sakila sql script from http://dev.mysql.com/doc/#sampledb.
  2. 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

Downloadable resources

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

Related Articles

Attachments (2)

Comments (1)

Feris Thia - Jun 5, 2008 8:11 AM

There are reports on some issues with previous version of our attached files (Sakila.xml & rentalquery.jsp).

For those of you who experience the same problem, please redownload the files or please change "Rental Count" to "Rental Counts" in the content all of those files.

Thanks !

Feris Thia