15 November, 2008

Getting Started With Spoon

Conventions Used

Bold Text : represent new / important concept such as type of step and also representing interface components such as button, menu item, etc.

[Bold in Bracket] : represent step name


What is Spoon ?

Spoon is graphical utility in Kettle suite to do these functionalities :
  • design and run job / transformation
  • job / transformation profiling
  • set up database connection, variable/object sharing, configuration, etc
This article will serve as introductory tutorial for those who are new to Spoon and Kettle.

Running Spoon

  • Spoon in Windows can be executed using these 2 files :
    • Binary : kettle.exe
    • Batch script : spoon.bat




  • Execute one of the two files





  • Upon starting, you will be presented a dialog to choose a repository - a relasional database system repository to store job/transfformation object detail. Clik on No repository button for now to choose our filesystem as our storage.







  • A tips dialog will pop up shortly. Click on close to continue.
  • You will be presented a "Welcome to Pentaho Data Integration: Kettle Project" page in Welcome tag.
  • Done

Spoon Interface

  • Pulldown Menu  : a main navigation for spoon. Comprising of all needed operations on designing, previewing and executing job / transformation.







  • Toolbar : contain icons for basic file operation, printing, repository view and job / transformation operations.






  • Left panel  : contain configuration, steps and favorite steps used splitted in Main Tree, Core Objects dan Favorite Steps sections.







  • Workspace Tabs : act as divider between your workspaces. Picture below show the tag collections of  1 welcome page, 2 transformations and 1 job.





Creating a Transformation File

Here I show you an example how to create a transformation that read a csv (comma separated value)  file containing 33 provinces of Indonesia and dump it to another csv file. The propinsi.csv sample file can be downloaded at the end of this article or directly through this link. Save the file in C:\contoh_kettle folder. 

propinsi.csv

Kode_Propinsi,Deskripsi
P01,Bali
P02,Bengkulu
P03,Banten
P04,Gorontalo
P05,Irian Jaya Barat
P06,Papua
P07,Jambi
P08,Jawa Barat
P09,Jawa Tengah
P10,Jawa Timur
P11,Kalimantan Barat
P12,Kalimantan Tengah
P13,Kalimantan Timur
P14,Kalimantan Selatan
P15,Kepulauan Bangka Belitung
P16,Kepulauan Riau
P17,Lampung
P18,Maluku
P19,Maluku Utara
P20,Nusa Tenggara Barat
P21,Nusa Tenggara Timur
P22,Riau
P23,Sulawesi Barat
P24,Sulawesi Tengah
P25,Sulawesi Tenggara
P26,Sulawesi Selatan
P27,Sulawesi Utara
P28,Sumatra Barat
P29,Sumatra Selatan
P30,Sumatra Utara
P31,DI Yogyakarta
P32,DKI Jakarta
P33,Nanggroe Aceh Darussalam


Now here are the steps to create a transformation handling the csv files :
  • To create a transformation you can do it with several ways :
    • Choose File | New | Transformation from the pulldown menu 







    • Click on  New | Transformation on the toolbar





    • With a CTRL + N shortcut key

  • A new Transformation 1 workspace tab will show up, you rename it by saving our newly created transformation. Press CTRL + S and save file as c:\contoh_kettle\baca_propinsi.ktr
  • Notice that now our tab is changed to baca_propinsi.

Transformation Steps

  • Transformation will consist of several steps. Now we will drop a step visually in our workspace to read the content of ourC:\contoh_kettle\propinsi.csv file.
  • In the left panel open up Core Objects | Input category. Here you will find several steps to read input from several file formats.
  • For our need, we will use CSV file input step.




  • Click on CSV file input icon and drag it into our workspace.






  • Double click on the [CSV file input] step until a dialog shows up.
    • In Filename section fill in c:\contoh_kettle\propinsi.csv. Left the other as they are now.
    • Click on Get Fields to retrieve our known fields. Click OK and Close subsequently for another dialogs that pop up.






    • You will a configuration with values shown like picture below. Click OK to return to our workspace.





  • Save our transfomation file

Data Preview

  • We can preview several records read from our step using preview facility in Spoon.
  • To demonstrate this, click on [CSV file input] and click Preview icon in the toolbar then click on Quick Launch button that shows up.





  • In seconds you will have Examine Preview Data dialog with a number data of records previewing in this window. Close it for now by clicking Close button.







  • Done

Joining Step with a Hop

  • From the left panel open Core Objects | Scripting and drag Modified Java Script Value step into your transformation workspace.
  • Hold CTRL key, click on both [CSV File Input] step and [Modified Java Script Value] then right click and choose New Hop. Click OK on dialog  that show up.






  • We just created a hop that bridging the two steps we created before.






Transform our Data

  • One of  Modified Java Script Value step functionality is to change our data using programmatically using several built in operator and functions. If you know Java very well, you can also embedded Java code in this step. Double click on the step and type following code in the editor that appears.





    var Deskripsi_lengkap = "Propinsi " + Deskripsi;
    var No_urut = getProcessCount("r");

  • Make sure that Compatibility mode ? is unchecked
  • Click on Get variables button.
  • You will have a dialog look as below.






  • Click OK.
  • Evaluate this step by previewing data on it.

Output Step

  • Now we will dump our result from [Modified Java Script Value] to a text file, C:\contoh_kettle\propinsi.txt.
  • Again, from the left panel open Core Objects | Output and drag Text file output type step to workspace.
  • Joining [Modified Java Script Value] and [Text file output] with a hop.





  • Double click on [Text file output]
  • In the pop up Text file output dialog  click on file tab and type  C:\contoh_kettle\propinsi in Filename section.
  • Still on the dialog, click on Fields tab and click Get Fields button to have 4 fields show up (Kode_Propinsi, Deskripsi,Deskripsi_lengkapText file output).
  • Click OK.

Running Transformation

  • Now our transformation already has our goals : read a csv text file, change some value and put it into another 2 fields,  and save the combining fields into a new csv text file.
  • Run the transformation by click Run button on the toolbar.








  • Click Launch on the dialog.
  • You will be redirected to a log workspace with running steps detail information, for example how many rows that are read and written in the step. I will not going further by explaining parts of this workspace but please notice at the bottom panel where there are  detailed logs output there. You see in the last lines that our transformation has been successfully executed.





    Execution logs

    2008/08/24 18:31:22 - baca_propinsi - Dispatching started for transformation [baca_propinsi]
    2008/08/24 18:31:23 - Spoon - The transformation has finished!!
    2008/08/24 18:52:54 - Spoon - Launching transformation [baca_propinsi]...
    2008/08/24 18:52:54 - Spoon - Started the transformation execution.
    2008/08/24 18:52:54 - baca_propinsi - Dispatching started for transformation [baca_propinsi]
    2008/08/24 18:52:55 - Spoon - The transformation has finished!!
  • Now take a look at our C:\contoh_kettle folder, we will have 1 more file there. A newly created propinsi.txt. Open the file with your favorite text editor and see the change from the original file.




    propinsi.txt

    Kode_Propinsi;Deskripsi;Deskripsi_lengkap;No_urut
    P01;Bali                     ;Propinsi Bali;00000000000001.00
    P02;Bengkulu                 ;Propinsi Bengkulu;00000000000002.00
    P03;Banten                   ;Propinsi Banten;00000000000003.00
    P04;Gorontalo                ;Propinsi Gorontalo;00000000000004.00
    P05;Irian Jaya Barat         ;Propinsi Irian Jaya Barat;00000000000005.00
    P06;Papua                    ;Propinsi Papua;00000000000006.00
    P07;Jambi                    ;Propinsi Jambi;00000000000007.00
    P08;Jawa Barat               ;Propinsi Jawa Barat;00000000000008.00
    P09;Jawa Tengah              ;Propinsi Jawa Tengah;00000000000009.00
    P10;Jawa Timur               ;Propinsi Jawa Timur;00000000000010.00
    P11;Kalimantan Barat         ;Propinsi Kalimantan Barat;00000000000011.00
    P12;Kalimantan Tengah        ;Propinsi Kalimantan Tengah;00000000000012.00
    P13;Kalimantan Timur         ;Propinsi Kalimantan Timur;00000000000013.00
    P14;Kalimantan Selatan       ;Propinsi Kalimantan Selatan;00000000000014.00
    P15;Kepulauan Bangka Belitung;Propinsi Kepulauan Bangka Belitung;00000000000015.00
    P16;Kepulauan Riau           ;Propinsi Kepulauan Riau;00000000000016.00
    P17;Lampung                  ;Propinsi Lampung;00000000000017.00
    P18;Maluku                   ;Propinsi Maluku;00000000000018.00
    P19;Maluku Utara             ;Propinsi Maluku Utara;00000000000019.00
    P20;Nusa Tenggara Barat      ;Propinsi Nusa Tenggara Barat;00000000000020.00
    P21;Nusa Tenggara Timur      ;Propinsi Nusa Tenggara Timur;00000000000021.00
    P22;Riau                     ;Propinsi Riau;00000000000022.00
    P23;Sulawesi Barat           ;Propinsi Sulawesi Barat;00000000000023.00
    P24;Sulawesi Tengah          ;Propinsi Sulawesi Tengah;00000000000024.00
    P25;Sulawesi Tenggara        ;Propinsi Sulawesi Tenggara;00000000000025.00
    P26;Sulawesi Selatan         ;Propinsi Sulawesi Selatan;00000000000026.00
    P27;Sulawesi Utara           ;Propinsi Sulawesi Utara;00000000000027.00
    P28;Sumatra Barat            ;Propinsi Sumatra Barat;00000000000028.00
    P29;Sumatra Selatan          ;Propinsi Sumatra Selatan;00000000000029.00
    P30;Sumatra Utara            ;Propinsi Sumatra Utara;00000000000030.00
    P31;DI Yogyakarta            ;Propinsi DI Yogyakarta;00000000000031.00
    P32;DKI Jakarta              ;Propinsi DKI Jakarta;00000000000032.00
    P33;Nanggroe Aceh Darussalam ;Propinsi Nanggroe Aceh Darussalam;00000000000033.00

  • Done

Conclusion

Congratulations, you have just created a simple transfomation designed and executed in Spoon. For more reading on transformation you can check on Pentaho wiki. We will also frequently update our samples in Kettle section. Soalways stay tuned :)

If you have any question at this article, feel free to drop us a note at info@phi-integration.com.

You can download the sample files from http://pentaho.phi-integration.com/kettle/spoon.

27 August, 2008

OpenOffice Recovery Takes Forever ?

OpenOffice.org is a very good alternative productivity application suite to Microsoft Office in terms of features and it has a rapid speed of development from open source community worldwide.

I use Calc - the spreadsheet application in the suite - extensively in my daily activities to analyze data subsets in data warehousing projects and proved to be very helpful. But the drawback of using this application is that you have to use it as it is - no point to have much complaints. But now I'm facing the stability and reliability issue that almost drive me nuts - an unstopable recovery process.

If you have a document that is recognized as a damaged one by OpenOffice you will be shown a dialog to recover it. And if you agree to recover it, almost only few of the times it behaves nicely .


But if you are unlucky enough then this process will take forever without any clue to be stopped in hand. I face it now... and even restarting my system several times didn't bring any good. Then I got this helpful page by googling and it relief me from the problem.

If you have the same issue, try this :
  • If you are on Windows environment, delete Documents and settings/username/Application data/Openoffice.org2/user/registry/data/org/openoffice/Office/Recovery.xcu file.
  • And if you are a Linux user, delete /home/username/.openoffice.org2/user/registry/data/org/openoffice/Office/Recovery.xcu
Done.

Till next article...

Feris

25 August, 2008

MS AS 2000 and MySQL ODBC Problem

Recently, I'm using MySQL as OLAP data staging and development database server for one of my clients which use Microsoft Analysis Server 2000.

I encountered problem with recent MySQL ODBC driver (5.1) from MS AS 2000 trying to get list of tables from MySQL DSN data source.


I then download and install an older version of MySQL driver (3.51) and set new ODBC DSN and finally have it recognized by MS Analysis Server 2000.



Hope this experience may help some of you who need to do the same thing as I did.


Feris Thia

03 August, 2008

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.


02 August, 2008

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 :

04 July, 2008

Kettle's Regex Sample

Kettle (Pentaho Data Integration) is a very popular open source ETL tool. Built on Java platform, it can run in multiple OS such as Windows, Linux, Mac, and other Unix based platform.

Regular expression is a powerful construct to manipulate any text and is supported in Java language. With the capability of Regex evaluation step and Java scripting in Kettle, I give a simple example on how to read an "unstructured" log file and make it tabular using the steps.

Get the sample transformation file from this wiki page.

tomcat_file_log_transformation

01 June, 2008

Mondrian: MySQL Sakila's cube

Sakilla 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 create a series of article showing the use of this database with Mondrian and Kettle. To begin with, here I will show how to get this sample database, creating a very basic cube definition and how to query it - all without altering the underlying data.

See our complete wiki article here.


31 May, 2008

Halogen Wiki Page

Halogen is an olap client viewer based primarily on OLAP4J and Google Web Tookit (GWT).

Halogen has several features :
  • The ability to connect to Mondrian's cube
  • Visually add dimension and measures from a chosen cube
  • Execute MDX query
  • Graphical Chart
To assist anyone who want to have a try on this application, we have created a non official wiki for Halogen. You can visit the site by clicking on the image below or on this link.


12 May, 2008

PDI Sample Screencast

Kettle / Pentaho Data Integration is now one of the most used open source ETL tool in the market.

With increasing inquiries on this product, I make this screencast that shows how to design and execute a very simple Kettle task to migrate one or two tables from SQL Server to MySQL (annotations are in Indonesian).


Have you had any further question or inquiry on this product, you can contact me directly at feris@phi-integration.com.
ke

30 April, 2008

JPivot + MS Analysis 2000

Introduction

JPivot is one of rich features OLAP clients in the market that comes up with web based interface. Its capability to run in multiple platform and compliant with XMLA standard as its data source make JPivot a viable and affordable solution for every organizations that already implemented XMLA OLAP server but needed more robust client solution.

MS SQL Server 2000 and MS 2000 Analysis Server is one of the mostly wide used database and OLAP server in enterprise market. And since Microsoft is also the initiator in this XMLA technology, it is not surprising that MS Analysis Server also support the standard.

But the drawback is that both products don't come out with any OLAP client. Hence, since JPivot is XMLA compliant we can use it nicely to fill the gap.

Setup IIS and MS 2000 Analysis Server

First you need to download Microsoft XMLA sdk and set it up with IIS and MS Analysis Server. I will not go through this step as you can see more detailed instructions in this MSDN article.

For my working sample I use XMLA 1.1 SDK version. And if you have installed, configured and running all the things correctly then try browse to msxisapi.dll from your XMLA virtual host. In my case my virtual directory name is set to xmla and located in my local desktop so I'll browse to http://localhost/xmla/msxisapi.dll and should see result like the picture below


Figure 1: URL Testing for SQL Server 2000 XMLA Implementation

or if you are using Firefox you may get result like in figure 2.


Figure 2: URL Testing for SQL Server 2000 XMLA Implementation (Firefox)


Figure 3 is a screenshot of my IIS config for this xmla virtual directory.



Figure 3: IIS xmla Virtual Directory


And for your complete reference this is the list of prerequisite applications that I use :
  • Windows XP Professional SP2
  • Microsoft Internet Information Services 5.1 that comes with Windows XP
  • Microsoft SQL 2000 SP4 with Microsoft Analysis installed
  • Java Development Kit 1.6
  • Apache Tomcat 5.5.17
  • JPivot 1.8
JPivot Query File

Now you can create a flat jsp file named salesCube.jsp and put it into your $JPIVOT/WEB-INF/queries folder. In my case the folder is located in C:\apache-tomcat-5.5.17\webapps\jpivot\WEB-INF\queries.

Listing 1 : salesCube.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" %>


select
{[Measures].[Unit Sales], [Measures].[Store Cost]} on columns,
{[Customer].[All Customer]} ON rows
from Sales


MS Analysis Services Cube: Sales (via XMLA)

Pay attention to above listing for uri and catalog attribute of jp:xmlaQuery tag. For uri we will use the msixisapi.dll reference url that we already tested before. And for catalog I'm using Tutorial which is Ms Analysis Server catalog for Foodmart database sample. Make sure that in this example you already have setup Sales cube with Unit Sales and Store Cost measures and Customer dimension.

Get the result by executing http://localhost:8080/jpivot/testpage.jsp?query=salesCube in your browser. If everything working fine you will get the result like figure 4 and figure 5 below. Enjoy the great features of JPivot including drill down option and graphichal cart generation.



Figure 4: JPivot with Axis in hierarchical mode


Figure 5: JPivot with Generated Bar Chart


Conclusion

JPivot is an OLAP web based client that can work perfectly with XMLA standard OLAP provider like Microsoft Analysis Server 2000. You will need an additional installation of Microsoft XMLA SDK and a virtual directory IIS configuration to make it work.

Here I have shown how you can consume MS Analysis's OLAP source using XMLA with a standard JPivot installation. Create a jsp file, specify your jp:xmlaQuery tag's attributes and specify an MDX query. You now have all the rich features of JPivot.




Online Resources

Mondrian as XMLA Provider

Mondrian is an open source Java OLAP server under Pentaho. One of its great feature is that it can be act as XML/A (XML for Analysis) server that can be consumed by a number of independent XML/A compliant clients.

From Mondrian's documentation I find it quite simple to set up Mondrian as XMLA provider. But I didn't find any adequate information on how to test it directly from Mondrian's own bundled tool.

For any beginner, it will be hard and time consuming to find out whether the provider is already and rightly set up.

I write this article in order to help those with that problem, complementing Mondrian's own documentation .

Setup

Open your /WEB-INF/datasources.xml file and add a data source :

<?xml version="1.0"?>
<DataSources>
<DataSource>
<DataSourceName>
Provider=Mondrian;DataSource=MySQLTest;</DataSourceName>
<DataSourceDescription>Mondrian FoodMart Data
Warehouse</DataSourceDescription>
<URL>http://localhost:8080/mondrian/xmla</URL>
<DataSourceInfo>
Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=root;JdbcPassword=;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>
<ProviderName>Mondrian</ProviderName>
<ProviderType>MDP</ProviderType>
<AuthenticationMode>Unauthenticated</AuthenticationMode>
<Catalogs>
<Catalog name="FoodMart">
<Definition>/WEB-INF/queries/FoodMart.xml</Definition>
</Catalog>
</Catalogs>
</DataSource>
</DataSources>


DataSourceName tag

Although DataSourceName tag can have entry of any name - which can be detected by Mondrian form test - but we will use the "Provider=[value];DataSource=[value]" format instead.

Connection String

Since I use MySQL as my cube's storage that I setup before, so in this particular line I specified connection detail to the server. I specified in the JDBC's URL my host (localhost), database schema (foodmart_mondrian), user (root) with no password and the the driver class itself (com.mysql.jdbc.Driver).

Validate Data Source

I assume that you already have Mondrian running under Tomcat in your local computer. Try browse to http://localhost:8080/mondrian/xmlaTest.jsp. Select "1.discoverDataSource" option from the combo list then click "show request" button until you see the XMLA construct like below.

After that, you might proceed by clicking "Run" to see if your XMLA provider - specified in your DataSourceName - is recognized.

A screen shot below indicated that Mondrian XMLA provider is recognized and ready to be sent any OLAP/MDX query.

Query Testing

Back to your XMLA test page, select "2. executeHR-tabular option list. Click on "Show Request" button.

Once you see the XMLA generated construct you need to modify one thing: DataSourceInfo entry.


Change the tag's value of "DataSource=MondrianFoodMart;" into "DataSource=MySQLTest;". Continue by clicking on "run" button.



If all going right you will have a returning XML SOAP result like below. This means that Mondrian has successfully executed your query.































<_x005b_measures_x005d_._x005b_org_x0020_salary_x005d_ type="xsd:double">
39431.6712




<_x005b_employees_x005d_._x005b_employee_x0020_id_x005d_._x005b_member_caption_x005d_>
Sheri Nowmer

<_x005b_measures_x005d_._x005b_org_x0020_salary_x005d_ type="xsd:double">
39431.6712









Conclusion

Setup Mondrian as XMLA provider is easy and quite straightforward.

However, in order to test it we sometimes at a lost. Since we don't have any adequate information provided with the bundled documentation. This article will serve as a complement for that.

Hope you find this blog entry helpful. Any comment or suggestion to improve this article is highly appreciated.


29 April, 2008

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 :



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.