Hitachi Vantara Pentaho Community Wiki
Skip to end of metadata
Go to start of metadata

This wiki note describes setting up Pentaho Reporting with Oracle 10g with focus on Pentaho Analysis Views (Mondrian JPivot reports).

Environment Details:

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. 

Step-by-Step instructions 

1. Create a folder structure as follows:

D:\Pentaho

D:\Pentaho\appserver

D:\Pentaho\pentaho-data 

D:\Pentaho\pentaho-solutions

D:\Pentaho\work

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).

set escape on;
set escape "\\";

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:

web.xml

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

jboss-web.xml

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

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

ant ear-pentaho-jboss-oracle

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:

run.bat

rem JVM memory allocation pool parameters. Modify as appropriate.
set JAVA_OPTS=%JAVA_OPTS% -Xms128m -Xmx512m -XX:MaxPermSize=256m

16. Deploy the ear file in JBoss and access http://localhost:8080/pentaho

--------------------------------------------- 

  • No labels

6 Comments

  1. In the quartz.properties file in the pentaho-solutions/system/quartz directory. Replace all "StdJDBCDelegate" with "OracleDelegate"
    change url value from: jdbc:oracle:oci:localhost:1521:... to jdbc:oracle:thin:localhost:1521:... for Oracle 9i onwards and oracle.jdbc.OracleDriver rather than oracle.jdbc.driver.OracleDriver as driverClassName

    Note: For Oracle 9i onwards you should use oracle.jdbc.OracleDriver rather than oracle.jdbc.driver.OracleDriver as Oracle have stated that oracle.jdbc.driver.OracleDriver is deprecated and support for this driver class will be discontinued in the next major release.
    These changes will occur in all Jboss data source files (-ds.xml) and *hibernate.cfg.xml
    The Pentaho BI Server allows two options for storage - either a file-based repository or an RDBMS-based repository. Obviously, if you are configuring Oracle as your repository, you want to make sure that the server is configured for RDBMS.
    -         Navigate to theD:\Pentaho\pentaho-solutions\systemdirectory
    -         Open thepentaho.xmlfile.
    -         Make sure the following line is commented out:
    o       <ISolutionRepository scope="session">org.pentaho.repository.filebased.solution.SolutionRepository</ISolutionRepository>
    -         Make sure the following line is uncommented:
    o       <ISolutionRepository scope="session">com.pentaho.repository.dbbased.solution.SolutionRepository</ISolutionRepository>
    Alsoinclude the db name parameter in the pentaho.xml :
    <solution-repository>
    <cache-size />
       <db-repository-name>pro_files</db-repository-name>
     </solution-repository>
       

  2. Hi, thanks a lot for this very nice tutorial.

    I tried to install on a win2000 server running Oracle XE, and I have this message when running point 13 :

    D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863>ant ear-pentaho-jboss-orac
    le
    Buildfile: build.xml

    BUILD FAILED
    Target "ear-pentaho-jboss-oracle" does not exist in the project "pentaho-deploym
    ents".

    It seems I missed a folder or a parameter...

    Total time: 1 second
    D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863>path
    PATH=c:\oracle\ora92\bin;C:\pentaho\java\bin;C:\oraclexe\app\oracle\product\10.2
    .0\server\bin;C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;D:\ANT\bin;C:\Pr
    ogram Files\Zend\Core For Oracle\bin

    D:\Pentaho\work\pentaho_j2ee_deployments-1.6.0.GA.863>

     I add my path to help debugging. I m not sure if I used the good files at point 11.

    I tried the installer for win with mysql, It was ok. When I type http://localhost:8080/&nbsp;, I reach oracle xe LICENSE AGREEMENT, so i have Apache using this port, could anyone help me ? I already use Kettle, and I would like to

    use Pentaho reporting 

     Please help me (sad)

     Raph

  3. al

    Thanks for the great article. I tried all the steps and I still get an error message when I start JBOSS and try to access http://localhost:8080/pentaho.

    The build was done successfully. I have Oracle 10g with Pentaho as database with pentaho_data as one table in that db. Can anyone suggest what the jndi name should be (to confirm)? I don't know where else to go from here, as there is not many replies in the forums as well for Oracle and JBOSS.

    Also can anyone advice on which web.xml and jboss-web.xml files need to be edited in step 11 (several of them).

     TIA - Al

  4. CRM

    11. Also, include your database JNDI name to web.xml and jboss-web.xml. Following are the snippets:

    Can you please lets us know the exact path?

  5. Anonymous


    Hi Ralph,

        As you are using Oracle XE and it comes with an application called Oracle Apex, the issue you are having is a port conflict. 

        If you try 127.0.0.1/apex/ you will see the apex welcome screen.  This application is also configured by default to work at the 8080 port.

        So the easiest thing you can do to solve this issue is to change APEX port.

        Log in using any user with dba role and: 

    1.-   To see which ports are being used by Oracle APEX:  (Note that theres actually an http port and a ftp port)  

            select dbms_xdb.gethttpport , dbms_xdb.getftpport  from dual;

    2.-   To change the assigned ports: (If you assign 0 then you will close the connection):

            Begin
                dbms_xdb.sethttpport('8010');
                dbms_xdb.setftpport('0');
            end;

    3.- Once changed, you should restart Oracle  

        And that should solve your port conflict.
        Hope is no too late! 

       Regards,

            Juan 

  6. Anonymous

    Hello guys I have followed all steps in this article because i m want to integreta ORACLE, JBOSS and PENTAHO. I have deployed the .ear file that was generated by ant but i get the following error when JBOSS is starting.

    Excepción enviando evento inicializado de contexto a instancia de escuchador de clase org.pentaho.core.system.SolutionContextListener

     Maybe could be because a didn t do the step 11 because i don t know where i can find these files web.xml and jboss-web.xml.

     Do you know what i am doing wrong. Thanks a lot

    Luis Santos Venezuela