This wiki note describes setting up Pentaho Reporting with Oracle 10g with focus on Pentaho Analysis Views (Mondrian JPivot reports).
Operating System: Windows XP
Application Server: JBoss Portal 2.6.2 + JBoss Application Server 4.2.1 - You can download it from here
Install jboss under D:\Pentaho\appserver. As an example, for me, JBOSS is installed as D:\Pentaho\appserver\jboss-portal-2.6.2.GA.
Database: Oracle 10g - You can download it from here
Build tool: Apache Ant 1.7.0 - You can download it from here (Install Ant and make sure that %ANT_HOME% environment variable is set. Also, append %ANT_HOME%\bin to %PATH% environment variable.)
Java SE 1.5.0_14 - You can download it from here(Install JDK and make sure that environment variable %JAVA_HOME% is set. Also, append %JAVA_HOME%\bin to %PATH% environment variable.)
Instructions below assume you've downloaded and installed above mentioned softwares.
1. Create a folder structure as follows:
2. Download and UnZip 'Pentaho Sample Data' zip file to 'D:\Pentaho\pentaho-data' folder. You can download it from here
3. Download and Unzip 'Pentaho Solutions' zip file to 'D:\Pentaho\pentaho-solutions' folder. You can download it from here
4. Download and Unzip 'Pentaho J2EE Deployment Distribution' zip file to 'D:\Pentaho\work' folder. You can download it from here
5. Using 'Oracle Database Configuration Assistant' create a database with SID 'pentaho'.
6. Open 'D:\Pentaho\pentaho-data\oracle\sampledata-ora.sql' file. Replace 'Alex' user with 'SYS' or any other user with DBA privilege. Also change the password accordingly. In addition, add following after every new sqlplus connection within the script (or add it once in glogin.sql).
Modified file is attached on this page.
After modifying the file execute it agaist pentaho db in sqlplus.
7. 'Pentaho Solution' doesn't ship with oracle driver. So, you need to copy ojdbc14.jar from Oracle installation to D:\Pentaho\appserver\jboss-portal-2.6.2.GA\server\default\lib (Assuming you will be running jboss in default configuration).
Note: You can bundle it within the archive (WEB-INF/lib). However, Jboss cannot hot-deploy database drivers. So, its advisable (and Jboss recommends) that you keep the driver jar file under %JBOSS_HOME%\server\<config>\lib.
8. hibernate.cfg.xml present under 'D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863\pentaho-res\hibernate\oracle10g' folder should be modified to use 'pentaho' SID and 'hibernate/password' connection credentials. Modified file is attached on this page.
9. Runtime Repository information is missing for Oracle10g. So copy D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863\pentaho-res\hibernate\hsqldb\org folder to D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863\pentaho-res\hibernate\oracle10g folder. This will copy the necessary
'RuntimeElement.hbm.xml' file. There is no need to modify this file.
10. Modify the Jboss data source files (*-ds.xml) for oracle 10g under D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863\pentaho-res\jboss\datasources\oracle10g. Basically, you need to keep the SID as pentaho and change user-id and password accordingly. Modified files are attached as "oracle_datasource_files.zip"
11. Also, include your database JNDI name to web.xml and jboss-web.xml. Following are the snippets:
12. Navigate to 'pentaho-third-party' folder under pentaho j2ee deployment directory. In example configuration, fully qualified path to the folder is 'D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863\pentaho-third-party'. Under this folder you will see a 'licenses' folder. Copy all the *.license.txt files from the 'pentaho-third-party\licenses' folder to 'pentaho-third-party' folder. This is needed because build.xml inserts these entries into the application.xml while packaging the EAR file. If we don't copy the license files to the parent folder, you will get missing resource exception during Jboss startup.
13. build.xml file has modifications only for hypersonic , and mysql databases. So you need to modify build.xml to include oracle specific targets. I've attached the build.xml and build.properties files I modified on this page.
After modifying build.xml, open command prompt and navigate to folder 'D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863' and run the build as follows
14. This will generate pentaho.ear file under D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863\build\pentaho-ears\jboss\portal\oracle10g.
15. If you are deploying the Pentaho BI Platform to a vanilla JBoss 4.2.1, you will want to follow the tips below.
Make your way into <jboss_home>/bin and modify run.bat as follows:
16. Deploy the ear file in JBoss and access http://localhost:8080/pentaho