This goal of this post is to walk you through an awesome business intelligent framework named “Pentaho”. I believe the philosophy of “Learn by Practice”. So, I will show you the steps to get pentaho up and run for a fictitious company. Along with this exercise, you should be able to understand how Pentaho works and what features it provides. Lets start.

Installation of Pentaho

1. Download Pentaho Demo (PCI) here
2. Read Pentaho Quick Start and the Creating Pentaho Solutions pdf documents. You can get those documents from the download center above as well.
3. Unzip the download file will result in a pentaho-demo directory. This is the server root, and it is commonly referred to as the PCI root or PCI install directory or something similar. To start the server, windows users run start-pentaho.bat; *nix users run start-pentaho.sh.
4. Open an internet browser, and navigate to:http://localhost:8080/. This may take a little while - the server needs to warm up.
5. Now you should see the pentaho web front. Try this sample out to make sure the setup is correctly done.
   • Getting Started::Hello World
   • Reporting
   • Chart Examples. Shows some of the included charting capabilities
   • Analysis / OLAP Examples. Demonstrates slice and dice
   • Dashboards. There is demo “Flash Dashboard” that actually uses XML-driven free Flash chart. Look pretty good! However, I suggest to use FLEX Charting although it is not free
6. When you’re done with pentaho, locate the stop-pentaho script in the PCI installation directory. Execute the script to stop de server.
7. For more info of how to set up PCI as server and how to configure email service. Take a look at Roland blog.

Create a new sample

1. Start pentaho demo as stated above.
2. Create a folder named “MySQL” under %PCI%/pentaho-solutions/samples/mysql.
3. To make mysql folder to display at the entry page. We need to put index.xml in the mysql folder. You may notice that we are using variables for name and description. The value of the variables are defined using index.properties under the same folder. The reason to do that is to support internationalization because you can define index_cn.properties for Chinese wording. Note: Click “Update Solution Repository” under Admin tab to refresh the change.
4. Download MySQL Sample database - sakila. Here is the schema view.

<index>
     <name>%directory_name</name>
     <description>%directory_description</description>
     <icon>folder.png|dashboard.jpg</icon>
     <visible>true</visible>
     <display-type>list</display-type>
</index>

5. Download mysql v5 database and its jdbc driver.
6. Run sakila_data.sql and sakila_schema.sql against MySql database. Now you have your sample movie database ready.

7. Create a file named mysql-ds.xml in $DEMO_BASE/jboss/server/default/deploy/

<?xml version=”1.0″ encoding=”UTF-8″?>
<datasources>
  <local-tx-datasource>
    <jndi-name>sakila</jndi-name>
    <connection-url>jdbc:mysql://localhost/sakila</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password>honr</password>
  </local-tx-datasource>
</datasources>

8.  Edit the file $DEMO_BASE/jboss/server/default/deploy/pentaho.war/WEB-INF/web.xml.
   Add the following right below solution5 resource-ref entry

    <resource-ref>
   <description>sakila</description>
   <res-ref-name>jdbc/sakila</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
    </resource-ref>

9.  Edit the file $DEMO_BASE/jboss/server/default/deploy/pentaho.war/WEB-INF/jboss-web.xml.
   Add the following right below the solution5 entry.

 <resource-ref>
        <res-ref-name>jdbc/sakila</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <jndi-name>java:/sakila</jndi-name>
  </resource-ref>

10. Copy your mysql jdbc driver library in the following directory:
    $DEMO_BASE/jboss/server/default/lib
11. Create your own myFirst.xaction file.

<?xml version=”1.0″ encoding=”UTF-8″?>
<action-sequence>
 <name>myFirst.xaction</name>
 <title>%title</title>
 <version>1</version>
 <logging-level>debug</logging-level>
 <documentation>
   <author>Raymond Hon</author> 
   <description>%description</description> 
   <help/> 
   <result-type>rule</result-type> 
   <icon>SQL_Datasource.png</icon>
 </documentation>

 <inputs/>
 <outputs>
  <rule-result>
   <type>list</type>
  </rule-result>
 </outputs>
     <resources/>
     <actions>
      <action-definition>
           <action-outputs>
               <rule-result type=”list” />
           </action-outputs>
           <component-name>SQLLookupRule</component-name>
           <action-type>rule</action-type>
           <component-definition>
               <jndi>sakila</jndi>
               <query>
                 <![CDATA[select * from actor where actor_id = 1]]>
              </query>
           </component-definition>
      </action-definition>
     </actions>
</action-sequence>

12. Create your own myFirst.properties with title and description. 
13. Restart pentaho
14. Open up Firefox (cough IE) and hit the following URL:
    http://localhost:8080/pentaho/ViewAction?&solution=samples&path=mysql&action=myFirst.xaction
15. That’s it!