Pentaho Data Integration 3.2 : Beginner's Guide

Pentaho Data Integration (PDI) a.k.a Kettle is undoubtedly one of the best ETL (Extract, Transform and Load) tools in the market and a favorite application in our organization. 

Kettle has helped us solve many difficult data processing cases involving many varying sources of data. As a data warehouse consultant and trainer, there is virtually no cases that cannot be solved by this great application. 

Despite of intuitive Kettle / PDI graphical environment, there are so many concept misunderstandings and usages I encounter that led to a poor data treatment designs and thus poor performance gain. That's why I've been long waiting for a PDI book that is both comprehensive and has many day-to-day usage samples. And finally the book was published by Packt Publishing under title "Pentaho Data Integration 3.2: Beginner's Guide"

This book is written by Maria Carina Roldan who has contributed PDI tutorial page in Pentaho wiki. Many thanks goes to Packt for giving me the opportunity to review the e-book version recently.

Positive impression instantly alighted on myself when I've read Table of Contents and finished the first chapter. Some basic and commonly asked questions directly presented with clear and concise explanations: 

"What is ETL?"

"Why in data warehouse do we need ETL tool?"

"What role can PDI do ? As an ETL ? And beyond ETL ?" 

That positive impression continued throughout the next chapter : a simple "Hello World" ETL sample. From the example, Maria introduced Spoon - Kettle's GUI designer - and basic important concepts : 

- How to run Spoon 

- Steps /  Hops

- Rows

- Running / Previewing data flow process

- How to read log

Users can immediately and easily understand the introduction. This is possible because her explanation enriched with many intuitive screenshots and graphical concept illustrations. Something that may take a while from the participants of PDI training session I myself conducted.

As I continued on, I notice that the delivery of practical sessions are also very good. Each sample was a step-by-step guide on how to make an ETL flow - with brief introduction of PDI step's used. Full explanation followed when we finished creating the sample. It makes the book easily followed and not making it a boring technical document.

The delivery consistency continues until last chapter. The book stay rich with samples, screenshots and concept illustrations.

Types of data source handled are also being discussed in a gradual "simple-to-sophisticated" fashion. Starting from processing text files, XML, spreadsheets / Excel, relational databases / SQL, and finally to the creation of datamart.

As a conclusion, this book is highly recommended for readers who want to get familiar with Pentaho Data Integration easily and quickly. But even experienced users may also benefited greatly from the book.

Interested ? You can buy the book from here or read a free sample chapter "Developing and Implementing a Simple Datamart" first.

Upcoming Pentaho Book by Roland Bouman

After years, at last there's a commitment for a Pentaho book. As announced in his blog, Roland Bouman - a former MySQL employee - and  Jos van Dongen from Tholis Consulting will write a book for Wiley with the tentative title "Building Pentaho Solutions".

As a Pentaho partner in Indonesia which sometimes struggling to integrate all Pentaho formerly independent projects (Mondrian, Kettle, and  Weka) with BI Platform, of course this is a good news for us. Hope this will provide a comprehensive benefit as a guide for both Pentaho customers and partners.

Good Luck Roland and Jos !

Feris

Business Intelligence Consultant

http://www.phi-integration.com

http://pentaho-en.phi-integration.com

http://pentaho.phi-integration.com

Mondrian 3.0.4 Patch Release

Pentaho has released a bug fix release Mondrian 3.0.4 on August 2nd 2008. For those of you who has implemented Mondrian 3.0.4 are encouraged to upgrade to this release.

You can download the latest release at sourceforge. For release note click here.

MDX in Mondrian

Introduction

MDX (Multidimensional Expression) is a language construct to query OLAP cubes. It is much like SQL is a language construct to query relational database.

MDX was an initiative of Microsoft as part of the OLE DB for OLAP (ODBO) specification in 1997. Mosha Pasumansky is one of the architects of the language. MDX was soon implemented by many OLAP vendors as their standard OLAP query language including Mondrian.

With MDX you can query a selection of Online Analytical Processing (OLAP) data, doing some calculations, and retrieving meta data properties.

Example of simple MDX query is shown below :

select {[Measures].[Unit Sales]} ON COLUMNS,
{[Product].[All Products]} ON ROWS
from [Sales]
where [Time].[1997]

This will query from "Sales" cube a "Unit Sales" measure value from "All Products" dimension that happened in 1997. You can try it against Foodmart sample database.

Mondrian has implemented many of standard MDX expressions supported in MS Analysis Server. You can find a detailed of the expression here.

MDX entry in Mondrian JPivot's Sample

You can type your MDX query in JPivot JSP page in two ways :

• Type it under jp:mondrianQuery tag
• Type it under MDX editor
  

Using MDX Editor

• Start your Mondrian OLAP server
• Browse to one of your sample JSP file, for example "JPivot pivot table"
• Click on MDX button in the above page toolbar, it will show up an MDX Editor
• Type the previous sample MDX query here, and click Apply button
  
  
  
• You will now have a new slice and dice table view of that MDX query
  
  
  

More Resources




Check on the following web resources for more information about MDX :

• Mondrian's MDX Documentation
• Mosha Pasumansky
  
• Microsoft MDX Reference
• MDX Calculation Sample
• Nicholas Goodmans's Blog

Mondrian + MySQL Setup

Introduction

Mondrian is a very popular open source OLAP Server and is also one of the products that are very oftenly asked in several discussion forums. One of those including installation and initial configuration issues.

In this article I will try to guide step by step on how to get Mondrian installed and configured using MySQL community database server and Windows XP as its operating system platform.

Prerequisites

These are the applications that are needed before you can using Mondrian, follow on the link for more information on how to setup each application :

• Java Development Kit 5 or later
• Apache Tomcat
• MySQL 5
• MySQL 5 JDBC driver
• Windows XP Pro SP2 Edition

MySQL Foodmart Database Sample

Mondrian provided Foodmart database sample in MS Access format. But I have provided a full generated MySQL script populate Foodmart so you use it in MySQL Community database server. You can download the tar.gz compressed file from my wiki site (pictured).

Extract the script file using your favorite compression utility - 7zip is the one I would highly recommend.

Create a database and execute the script. Here I show you a sample commands to create a Foodmart database - assuming that your mysql is running and your mysql.exe client is located in C:\mysql\bin with your previously extracted script file in C:\foodmart_mysql.sql.

Open command line prompt (Start | Run | type cmd | click OK)

cd C:\mysql\bin
mysql.exe -uroot -e "create database Foodmart"
mysql.exe -uroot Foodmart < C:\foodmart_mysql.sql
mysql.exe -uroot Foodmart -e "show tables"

You should see result like below if all running well...

+-------------------------------+
| Tables_in_foodmart            |
+-------------------------------+
| account                       |
| agg_c_10_sales_fact_1997      |
| agg_c_14_sales_fact_1997      |
| agg_c_special_sales_fact_1997 |
| agg_g_ms_pcat_sales_fact_1997 |
| agg_l_03_sales_fact_1997      |
| agg_l_04_sales_fact_1997      |
| agg_l_05_sales_fact_1997      |
| agg_lc_06_sales_fact_1997     |
| agg_lc_100_sales_fact_1997    |
| agg_ll_01_sales_fact_1997     |
| agg_pl_01_sales_fact_1997     |
| category                      |
| currency                      |
| customer                      |
| days                          |
| department                    |
| employee                      |
| employee_closure              |
| expense_fact                  |
| inventory_fact_1997           |
| inventory_fact_1998           |
| position                      |
| product                       |
| product_class                 |
| promotion                     |
| region                        |
| reserve_employee              |
| salary                        |
| sales_fact_1997               |
| sales_fact_1998               |
| sales_fact_dec_1998           |
| store                         |
| store_ragged                  |
| time_by_day                   |
| warehouse                     |
| warehouse_class               |
+-------------------------------+

Configuring and Running Tomcat

You will need to copy mysql driver, i.e. mysql-connector-java-5.1.6-bin.jar file to your Tomcat's library folder.

• Extract your MySQL zipped driver file you have downloaded before to get the jar file
  
• Copy the jar driver to C:\apache-tomcat-5.5.17\common\lib folder - which is the location for any jar drivers used in our web application. For version 6 of tomcat you just put it in APACHE_TOMCAT/lib folder
  

Run your server by typing "catalina run" in Tomcat's bin folder.

cd c:\apache-tomcat-5.5.17\bin
catalina run

Check your running server by browsing into http://localhost:8080. If everything works fine, our next step is to deploy Mondrian web application.

Deploying Mondrian

Extract mondrian distribution package and goto lib directory. You will find mondrian.war file there.

Copy the war file into your Tomcat's webapps directory while Tomcat is still running. The war file will be extracted and deployed into Tomcat automatically.

Browse into your http://localhost:8080/mondrian and you shall get a Mondrian welcome page like below.

Configure JSP File

Browse into Mondrian's application queries folder where you can find several sample jsp files. Here I open my C:\apache-tomcat-5.5.17\webapps\mondrian\WEB-INF\queries folder and find the following jsp files :

• fourheir.jsp
• mondrian.jsp
• colors.jsp
• arrows.jsp

Edit those files using your favorite text editor, and change following line :

<jp:mondrianQuery id="query01"
jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUrl="jdbc:odbc:MondrianFoodMart"
catalogUri="/WEB-INF/queries/FoodMart.xml">

..... this is your existing mdx query ....

</jp:mondrianquery>

into your MySQL database configuration. For example ...

<jp:mondrianQuery id="query01"
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/Foodmart?user=root&password="
catalogUri="/WEB-INF/queries/FoodMart.xml">
 
..... this is your existing mdx query ....
 
</jp:mondrianQuery>

Browse Your Multi Dimensional Data

Now you are ready to to play with JPivot. Browse into your local mondrian host address : http://localhost:8080/mondrian, choose the first mondrian examples "JPivot pivot table".

It will take some time as Mondrian will need to populate its initial cache before the view shows up.

You will then see a table which you can drill through in a hierarchical way and a heading toolbar which comprises of configuration icons.

Congratulations, your Mondrian with MySQL database has been successfully setup !

Updated : More on Mondrian Sample's Configuration

To find more about configuring each sample to work well with above installation please refer to our wiki page at http://pentaho-en.phi-integration.com/mondrian/configuring-mondrian-sample.